By: Nai Biao Zhou | Updated: 2020-09-03 | Comments (2) | Related: > Reporting Services Development
Data analysts use cross-tabulations, which are like Pivot Tables, to examine
relationships within data. When using cross-tabulation, we arrange data in row groups
and column groups. The row groups represent the different levels of one variable;
the column groups represent the other variable that may also have multiple levels
. The intersections of rows and columns contain summarized information. For example,
sales managers may want to view a breakdown of the company sales by product category
and time. In this type of report, the rows are different product categories; the
columns are different times. A cell, the intersection of a row and column, represents
the total sales amount for the corresponding product category in a certain period.
How can we build this kind of report in SSRS?
Matrices in SSRS provide functionalities like those features in the cross-tabulation
and pivot tables . We use a matrix to group data in row and column groups. This
tip provides a step-by-step procedure to create a ready-to-use, cross-tabulation
report shown in Figure 1. The report is interactive, i.e., the report allows users
to expand or collapse a group. The demonstration report loads data from the AdventureWorks
sample database “AdventureWorks2017.bak” .
Figure 1 Adventure Works
Sales Summary Report
This exercise gathers business requirements from the “Company Sales” report in
the SQL Server Reporting Services Product Samples :
Sales managers at AdventureWorks want to have a report to summarize the sales
amount in a certain period. When report users view the report, they want to see
a breakdown of company sales by product category and time. The product category
variable has two levels: product category and product subcategory; the time group
has two levels: year and quarter. Report users can interactively expand groups at
higher levels and collapse rows associated with lower levels.
Figure 2 demonstrates the expansion
or contraction of groups.
Figure 2 Interactively
Expand a Row Group and a Column Group
I organize this procedure into five sections.
- In Section 1, we create a data source for a report.
- We cover a step-by-step process to design a ready-to-use, cross-tabulation
report using Visual Studio 2019 in Section 2.
- Section 3 introduces techniques to build configurable reports.
- Next, in Section 4, we explore methods to add images to the report.
- Section 5 shows how to access reports through the SSRS web portal.
I created this report with Microsoft Visual Studio Community 2019 and Azure Data
Studio 1.19.0 on Windows 10 Home 10.0 <X64>; I tested the report on SQL Server
2017 Reporting Services (Version 14.0.600.906). The DBMS is Microsoft SQL Server
2017 Enterprise Edition (64-bit).
1 – Preparing Data Sources for the Report
We create data sources for SSRS reports from which the SSRS loads report data.
As a best practice, reports should share data sources . This exercise uses the
SQL Server relational database as the data source. The SSRS also supports other
data source types, such as Microsoft SQL Server Analysis Services, Microsoft Azure
SQL Database, Oracle, OLE DB, and XML .
1.1 Create a Stored Procedure to Extract Data
We use stored procedures to load report data from the SQL Server database. We
recommend this method. DBAs only need to grant EXECUTE permission on these stored
procedures without having a risk of exposing underlying tables . Besides, we
put all business logic in stored procedures; therefore the report, like the view
component in the MVC pattern, solely displays data to report users.
According to the business requirements, we should load the following data into
the report: sales amount, product subcategory, product category, quarter, and year.
We extract unique identifiers associated with these names as well. Inspired by the
SQL query in , we create a stored procedure “[dbo].[uspGetCompanySalesSummary]”
to retrieve these data from the sample database:
CREATE PROCEDURE [dbo].[uspGetCompanySalesSummary] @StartDate datetime ,@EndDate datetime AS BEGIN BEGIN TRY SELECT PC.[ProductCategoryID] ,PC.[Name] AS Category ,PS.ProductSubcategoryID ,PS.[Name] AS Subcategory ,DATEPART(yy, SOH.OrderDate) AS [Year] ,'Q' + DATENAME(qq, SOH.OrderDate) AS Qtr ,SUM(DET.UnitPrice * DET.OrderQty) AS Sales FROM Sales.SalesOrderDetail DET INNER JOIN Sales.SalesOrderHeader SOH ON DET.SalesOrderID = SOH.SalesOrderID INNER JOIN Production.Product P ON P.ProductID = DET.ProductID INNER JOIN Production.ProductSubcategory PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID] INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID WHERE(SOH.OrderDate BETWEEN(@StartDate) AND(@EndDate)) GROUP BY DATEPART(yy, SOH.OrderDate) ,'Q' + DATENAME(qq, SOH.OrderDate) ,PC.[ProductCategoryID] ,PC.[Name] ,PS.ProductSubcategoryID ,PS.[Name] END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState ); RETURN 1 END CATCH RETURN 0 END