Creating a Simple Matrix Report with Microsoft SQL Server 2016 Report Builder (Part2)

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

Post Article |

Search Articles |

Articles Home

In this article, we will build a simple Matrix Report with Microsoft SQL Server 2016 Report Builder in a step by step way.

Download source code for Creating a Simple Matrix Report with Microsoft SQL Server 2016 Report Builder (Part2)

Recommendation
Read Working with Microsoft SQL Server 2016 Report Builder (Part 1) - Step by Step Installation and SetUp before this article.

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 the Step By Step installation and SetUp of the Microsoft SQL Server 2016 Report Builder. In this article, we will build a simple Matrix Report with Microsoft SQL Server 2016 Report Builder in a step by step way.

Step 1: Table creation and population of data

As a first step, let us create a table (say tblDemo) with the data as under

USE [tempdb]
GO
/****** Object:  Table [dbo].[tblDemo]    Script Date: 2/20/2017 12:18:25 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
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

Step 2: Stored Procedure creation for fetching the record from the above table

Next let us create a Stored Procedure(say usp_GetDemoRecords) for fetching the record from tblDemo

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

Step 3: Open Report Builder and Choose Matrix Report Wizard

Step 4: Create a Data Set

From the next screen "Choose a Dataset", choose Create a dataset and click "Next"

Step 5: Choose a connection to a data source

Click "New"

Choose "Microsoft SQL Server" as Connection Type. Click "Build".

After filling all the details choose "Test Connection"

Now the Choose a connection to a data source will appear as

Step 6: Design a query

Click "Next" for "Design a query" screen to appear. In that screen expand "Stored Procedures", choose usp_GetDemoRecords and click "Run Query"

Step 7: Arrange Fields

Click "Next" and "Arrange Fields" as under -

In the Row groups, drag and drop Region,State,BranchName,BOMBucket,AgencyName,AgentName,ReceiptCollectedHour

. In the Columns Group, drag and drop PRODUCT and in the Values drag and drop TotalReceiptAmount, ReceiptNo. Perform a Sum on TotalReceiptAmount and Count on ReceiptNo.

Click "Next" and in the "Choose the layout" screen

Select Show subtotals and grand totals.Ensure that Blocked, subtotal below is selected.
Select the option Expand/collapse groups.

Click "Next" to find the "Preview" screen.

Step 8: Make the degin layout

Click "Finish" to find the "Design" screen. Let's change the layout as under

Step 9: Run the report

Finally Run the report by pressing F5 or Run icon

Expand the Region,State,BranchName,BOMBucket,AgencyName,AgentName,ReceiptCollectedHour to get a detail view.

We can export the report from teh "Export" menu as

Step 10: Save the report

Click on the "Save" button or Ctrl+S to save the report

Conclusion

In this article, we have seen how to build a simple Matrix Report using Microsoft SQL Server 2016 Report Builder 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)