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

Rajnilari2015
Posted by in MSBI (SSIS, SSRS, SSAS) category on for Beginner level | Points: 250 | Views : 885 red flag

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


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

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 multiselect 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

USE [ExperimentalDB]
GO
/****** Object:  Table [dbo].[tblDemo]    Script Date: 2/24/2017 8:30:13 AM ******/
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/24/2017 8:30:14 AM ******/
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)
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', 2000000221, CAST(N'2017-01-03 09:21:00.000' AS DateTime), 9, 1)
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 Three', 2000000223, CAST(N'2017-01-03 09:23:00.000' AS DateTime), 9, 1)
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 Three', 2000000211, CAST(N'2017-01-03 11:01:00.000' AS DateTime), 11, 1000)
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 Three', 2000000225, CAST(N'2017-01-04 12:25:00.000' AS DateTime), 12, 108)
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 Three', 2000000228, CAST(N'2017-01-04 14:38:00.000' AS DateTime), 14, 1000)
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', 2000000212, CAST(N'2017-01-05 12:18:00.000' AS DateTime), 12, 100)
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 Three', 2000000229, CAST(N'2017-01-06 22:01:00.000' AS DateTime), 22, 1)
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'ganesh agent', 2000000241, CAST(N'2017-01-12 12:06:00.000' AS DateTime), 12, 1)
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'Test Save as Draft', 2000000247, CAST(N'2017-01-12 13:21:00.000' AS DateTime), 13, 2000)
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', 2000000222, CAST(N'2017-01-12 14:59:00.000' AS DateTime), 14, 1)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
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)
GO
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (1, N'EAST')
GO
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (2, N'NORTH')
GO
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (3, N'SOUTH')
GO
INSERT [dbo].[tblRegionMaster] ([RegionId], [Region]) VALUES (4, N'WEST')
GO
/****** Object:  StoredProcedure [dbo].[usp_GetDemoRecords]    Script Date: 2/24/2017 8:30:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetDemoRecords] (@RegionName VARCHAR(100))
	-- 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
	DECLARE @sql NVARCHAR(1000)
	DECLARE @Region VARCHAR(100) = '''' + Replace(@RegionName,',',''',''') +''''
		
	SET @sql = 'SELECT 
			td.*	
		FROM [dbo].[tblDemo] td
		WHERE td.Region IN(' + @Region + ')'
	
	EXECUTE sp_executesql @sql  
END
GO
/****** Object:  StoredProcedure [dbo].[usp_GetRegions]    Script Date: 2/24/2017 8:30:14 AM ******/
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

Observe the way the the stored procedure usp_GetDemoRecords has been written

CREATE PROCEDURE [dbo].[usp_GetDemoRecords] (@RegionName VARCHAR(100))
	-- 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
	DECLARE @sql NVARCHAR(1000)
	DECLARE @Region VARCHAR(100) = '''' + Replace(@RegionName,',',''',''') +''''
		
	SET @sql = 'SELECT 
					td.*	
				FROM [dbo].[tblDemo] td
				WHERE td.Region IN(' + @Region + ')'
	
	EXECUTE sp_executesql @sql  
END

In accepts the @RegionName parameter which as values as 'EAST,NORTH' etc. and then the dynamic query is formed which executes and returns the resultset.

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

Please note that, we have selected Allow Multiple Values for selecting multiple values at runtime.

In the "Available Values Section", do the below

In the "Default Values Section", do the below

The "Advanced Section" will be as under

Step 6: Modify the DemoDataSet

Now let's go to the DemoDataSet properties and click "Refresh Fields" and click "Query Designer".

In the "Query Designer" window, pass the value 'EAST,WEST' in the Parameter Value ection and click execute

The result

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

Step 7: Run the report

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

Change the region multiselect drop down value to "EAST,WEST"

and click on the "View Report" to get

Conclusion

In this article, we have seen how to add MultiSelect 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
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


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)