In this article, we will enhance the reporting capability by adding dropdown filters to the same in a step by step way.
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.