Adding DropDown Filters To Matrix Report with Microsoft SQL Server 2016 Report Builder (Part 3)

Posted by Niladri Biswas (RNA Team) in MSBI (SSIS, SSRS, SSAS) category on 2/20/2017 for Beginner level | Points: 250 | Views : 2657

Post Article |

Search Articles |

Articles Home

In this article, we will enhance the reporting capability by adding dropdown filters to the same in a step by step way.

Download source code for Adding DropDown Filters To Matrix Report with Microsoft SQL Server 2016 Report Builder (Part 3)

Introduction

Microsoft SQL Server 2016 Report Builder is a stand-alone report service environment. We can create Tabluar/Matrix, paginated reports and publish them to Reporting Services installed in native or SharePoint integrated mode. In the previous article, we have learnt Step By Step approach of creation of a Matrix Report using Microsoft SQL Server 2016 Report Builder. In this article, we will enhance the reporting capability by adding dropdown filters to the same in a step by step way.

Step 1: Table creation and population of data

As a first step, let us run the below script which will create the master tables with the data as under

/****** Object:  Table [dbo].[tblBranchMaster]    Script Date: 2/20/2017 4:19:32 PM ******/
SET ANSI_NULLS ON
GO
/****** Object:  Table [dbo].[tblDemo]    Script Date: 2/20/2017 4:19:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDemo](
	[Region] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[BranchName] [nvarchar](255) NULL,
	[BOMBucket] [float] NULL,
	[PRODUCT] [nvarchar](255) NULL,
	[AgencyName] [nvarchar](255) NULL,
	[AgentName] [nvarchar](255) NULL,
	[ReceiptNo] [float] NULL,
	[ReceiptDate_time] [datetime] NULL,
	[ReceiptCollectedHour] [int] NULL,
	[TotalReceiptAmount] [float] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblRegionMaster]    Script Date: 2/20/2017 4:19:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblRegionMaster](
	[RegionId] [bigint] NULL,
	[Region] [nvarchar](255) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User One', 2000000216, CAST(N'2017-01-02 18:09:00.000' AS DateTime), 18, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User One', 2000000221, CAST(N'2017-01-03 09:21:00.000' AS DateTime), 9, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User Three', 2000000223, CAST(N'2017-01-03 09:23:00.000' AS DateTime), 9, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User Three', 2000000211, CAST(N'2017-01-03 11:01:00.000' AS DateTime), 11, 1000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User Three', 2000000225, CAST(N'2017-01-04 12:25:00.000' AS DateTime), 12, 108)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User Three', 2000000228, CAST(N'2017-01-04 14:38:00.000' AS DateTime), 14, 1000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User One', 2000000212, CAST(N'2017-01-05 12:18:00.000' AS DateTime), 12, 100)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User Three', 2000000229, CAST(N'2017-01-06 22:01:00.000' AS DateTime), 22, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'ganesh agent', 2000000241, CAST(N'2017-01-12 12:06:00.000' AS DateTime), 12, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'Test Save as Draft', 2000000247, CAST(N'2017-01-12 13:21:00.000' AS DateTime), 13, 2000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'BANGALORE', 0, N'TW', N'BANGALORE Agency', N'User One', 2000000222, CAST(N'2017-01-12 14:59:00.000' AS DateTime), 14, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'User Three', 2000000219, CAST(N'2017-01-03 11:02:00.000' AS DateTime), 11, 5000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'User Three', 2000000226, CAST(N'2017-01-04 14:01:00.000' AS DateTime), 14, 1300)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'User Three', 2000000227, CAST(N'2017-01-04 14:01:00.000' AS DateTime), 14, 2500)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'User One', 2000000217, CAST(N'2017-01-05 12:30:00.000' AS DateTime), 12, 100)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'ganesh agent', 2000000244, CAST(N'2017-01-12 12:09:00.000' AS DateTime), 12, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'ganesh agent', 2000000253, CAST(N'2017-01-12 13:40:00.000' AS DateTime), 13, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'ganesh agent', 2000000245, CAST(N'2017-01-12 14:47:00.000' AS DateTime), 14, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'DELHI', N'HYDERABAD', 0, N'CLE', N'HYDERABAD Agency', N'ganesh agent', 2000000260, CAST(N'2017-01-16 16:46:00.000' AS DateTime), 16, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'NORTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'User Three', 2000000218, CAST(N'2017-01-02 18:59:00.000' AS DateTime), 18, 1000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'User Three', 2000000224, CAST(N'2017-01-03 11:03:00.000' AS DateTime), 11, 10000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'ganesh agent', 2000000243, CAST(N'2017-01-12 12:12:00.000' AS DateTime), 12, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'Test Save as Draft', 2000000249, CAST(N'2017-01-12 13:27:00.000' AS DateTime), 13, 5000)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'ganesh agent', 2000000251, CAST(N'2017-01-12 13:36:00.000' AS DateTime), 13, 2)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'LUDHIANA', 0, N'CLE', N'LUDHIANA Agency', N'Test Save as Draft', 2000000258, CAST(N'2017-01-12 17:28:00.000' AS DateTime), 17, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'DELHI', N'DELHI', 16, N'CLE', N'DELHI Agency', N'User One', 2000000284, CAST(N'2017-01-27 17:07:00.000' AS DateTime), 17, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'MUMBAI', 21, N'CLE', N'MUMBAI Agency', N'User One', 2000000273, CAST(N'2017-01-27 16:55:00.000' AS DateTime), 16, 20)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'RAJASTHAN', N'JAIPUR', 4, N'TW', N'JAIPUR Agency', N'User One', 2000000271, CAST(N'2017-01-27 16:51:00.000' AS DateTime), 16, 10)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'DELHI', N'DELHI', 1, N'PLCL', N'DELHI Agency', N'User One', 2000000266, CAST(N'2017-01-27 16:48:00.000' AS DateTime), 16, 20)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'ANDHRA PRADESH', N'VISAKAPATNAM', 2, N'TW', N'VISAKAPATNAM Agency', N'User One', 2000000279, CAST(N'2017-01-27 17:03:00.000' AS DateTime), 17, 20)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'NAGPUR', 19, N'CLE', N'NAGPUR Agency', N'User One', 2000000286, CAST(N'2017-01-27 17:09:00.000' AS DateTime), 17, 4)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MADHYA PRADESH', N'INDORE', 0, N'CLE', N'INDORE Agency', N'User One', 2000000270, CAST(N'2017-01-27 16:50:00.000' AS DateTime), 16, 20)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'ORISSA', N'BHUBANESHWAR', 2, N'TW', N'BHUBANESHWAR Agency', N'User One', 2000000285, CAST(N'2017-01-27 17:08:00.000' AS DateTime), 17, 2)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'ANDHRA PRADESH', N'HYDERABAD', 11, N'PLCL', N'HYDERABAD Agency', N'User One', 2000000283, CAST(N'2017-01-27 17:06:00.000' AS DateTime), 17, 3)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'SOUTH', N'MAHARASHTRA', N'NAGPUR', 18, N'CLE', N'NAGPUR Agency', N'User One', 2000000232, CAST(N'2017-01-27 16:41:00.000' AS DateTime), 16, 2)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'PUNJAB', N'LUDHIANA', 2, N'TW', N'LUDHIANA Agency', N'User One', 2000000280, CAST(N'2017-01-27 17:04:00.000' AS DateTime), 17, 1)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'KERALA', N'TRIVANDRUM', 22, N'CLE', N'TRIVANDRUM Agency', N'User One', 2000000281, CAST(N'2017-01-27 17:05:00.000' AS DateTime), 17, 2)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'ASSAM', N'GUWAHATI', 1, N'TW', N'GUWAHATI Agency', N'User One', 2000000264, CAST(N'2017-01-27 16:46:00.000' AS DateTime), 16, 4)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'MADHYA PRADESH', N'BHOPAL', 0, N'TW', N'BHOPAL Agency', N'User One', 2000000287, CAST(N'2017-01-27 17:10:00.000' AS DateTime), 17, 5)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'WEST BENGAL', N'KOLKATA', 18, N'CLE', N'KOLKATA Agency', N'User One', 2000000276, CAST(N'2017-01-27 17:00:00.000' AS DateTime), 17, 20)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'ANDHRA PRADESH', N'HYDERABAD', 8, N'PLCL', N'HYDERABAD Agency', N'User One', 2000000257, CAST(N'2017-01-27 16:43:00.000' AS DateTime), 16, 3)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'ORISSA', N'BHUBANESHWAR', 3, N'TW', N'BHUBANESHWAR Agency', N'User One', 2000000278, CAST(N'2017-01-27 17:02:00.000' AS DateTime), 17, 50)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'TAMIL NADU', N'CHENNAI', 1, N'TW', N'CHENNAI Agency', N'User One', 2000000274, CAST(N'2017-01-27 16:57:00.000' AS DateTime), 16, 40)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'ANDHRA PRADESH', N'VISAKAPATNAM', 16, N'CLE', N'VISAKAPATNAM Agency', N'User One', 2000000265, CAST(N'2017-01-27 16:47:00.000' AS DateTime), 16, 10)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'DELHI', N'DELHI', 16, N'CLE', N'DELHI Agency', N'User One', 2000000272, CAST(N'2017-01-27 16:54:00.000' AS DateTime), 16, 50)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'MADHYA PRADESH', N'BHOPAL', 20, N'TW', N'BHOPAL Agency', N'User One', 2000000277, CAST(N'2017-01-27 17:01:00.000' AS DateTime), 17, 10)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'MAHARASHTRA', N'NASIK', 18, N'PLTW', N'NASIK Agency', N'User One', 2000000262, CAST(N'2017-01-27 16:44:00.000' AS DateTime), 16, 8)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'EAST', N'MADHYA PRADESH', N'BHOPAL', 1, N'PLCL', N'BHOPAL Agency', N'User One', 2000000269, CAST(N'2017-01-27 16:50:00.000' AS DateTime), 16, 55)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'KARNATAKA', N'MYSORE', 1, N'TW', N'MYSORE Agency', N'User One', 2000000263, CAST(N'2017-01-27 16:45:00.000' AS DateTime), 16, 6)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'MAHARASHTRA', N'NAGPUR', 0, N'CLE', N'NAGPUR Agency', N'User One', 2000000282, CAST(N'2017-01-27 17:05:00.000' AS DateTime), 17, 5)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'DELHI', N'DELHI', 20, N'CLE', N'DELHI Agency', N'User One', 2000000267, CAST(N'2017-01-27 16:48:00.000' AS DateTime), 16, 30)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'MAHARASHTRA', N'MUMBAI', 13, N'CLE', N'MUMBAI Agency', N'User One', 2000000288, CAST(N'2017-01-27 17:10:00.000' AS DateTime), 17, 10)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'WEST BENGAL', N'SILIGURI', 4, N'TW', N'SILIGURI Agency', N'User One', 2000000268, CAST(N'2017-01-27 16:46:00.000' AS DateTime), 16, 3)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'DELHI', N'DELHI', 17, N'PLCL', N'DELHI Agency', N'User One', 2000000233, CAST(N'2017-01-27 16:42:00.000' AS DateTime), 16, 4)
INSERT [dbo].[tblDemo] ([Region], [State], [BranchName], [BOMBucket], [PRODUCT], [AgencyName], [AgentName], [ReceiptNo], [ReceiptDate_time], [ReceiptCollectedHour], [TotalReceiptAmount]) VALUES (N'WEST', N'GUJARAT', N'ANAND', 17, N'CLE', N'ANAND Agency', N'User One', 2000000275, CAST(N'2017-01-27 16:58:00.000' AS DateTime), 16, 2)
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (1, N'EAST')
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (2, N'NORTH')
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (3, N'SOUTH')
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (4, N'WEST')

/****** Object:  StoredProcedure [dbo].[usp_GetDemoRecords]    Script Date: 2/20/2017 4:19:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetDemoRecords] 
	-- Add the parameters for the stored procedure here	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Fetch the records
	SELECT *
	FROM [dbo].[tblDemo]
END
GO
/****** Object:  StoredProcedure [dbo].[usp_GetRegions]    Script Date: 2/20/2017 4:19:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[usp_GetRegions] 
	-- Add the parameters for the stored procedure here	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [RegionId]
      ,[Region]
  FROM [ExperimentalDB].[dbo].[tblRegionMaster]
END
GO

Step 2: Open Report Builder and Choose DemoMatrixReport.rdl

Step 3: Add Data Sources

Right Click on the Data Sources > Add DataSource...

Click "OK". The "RegionDS" dataset will appear.

Step 4: Add Data Sets

Right Click on the Data Sets > Add DataSet...

In the DataSet property window, configure the following and click "OK"

The "RegionDataSet" will appear.

Step 5: Add Parameters for Region Dropdown

Right click on the Parameters folder > Add Parameter...

- OR- , Right click on the Parameters in the layout screen > Add Parameter...

In the "Report Parameters General Section", do the below

In the "Available Values Section", do the below

In the "Default Values Section", do the below

The "Advanced Section" will be as under

If we run the report at this step we will get

Step 6: Modify the usp_GetDemoRecords stored procedure

Let us now modify the usp_GetDemoRecords which will accept parameter as under

ALTER PROCEDURE [dbo].[usp_GetDemoRecords] (@RegionName VARCHAR(10))
	-- Add the parameters for the stored procedure here	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Fetch the records
	SELECT td.*
	FROM [dbo].[tblDemo] td
	WHERE td.Region = @RegionName
END

Step 7: Modify the DataSet1

Right click on the DataSet1 and make the below changes.

Click on "Query Designer", pass value(WEST) to the RegionName parameter and click Run Query

One more important step. In the "Parameter" section, change the "Parameter Value"

Step 8: Run the report

The last step is to run the report. Press F5 to get

Change the region drop down value to "EAST" and click on the "View Report" to get

Conclusion

In this article, we have seen how to add a dropdown to a Matrix Report using Microsoft SQL Server 2016 Report Builder for filtering records in a step by step way. Hope this will be helpful. Thanks for reading. Zipped file attached.

About the Author

Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team

Bookmark It

Login to vote for this post.

Latest Articles

Comments or Responses

Comment using

(Author doesn't get notification)