ASP.NET, DB

SSRS(SQL Server Reporting Services) Report Implementation With Grouping

What is SSRS (Sql Server Reporting Services) Report ?

SSRS stands for SQL Server Reporting services – a server based report generation software system from Microsoft.

SSRS is a reporting platform where the reports are stored in single centralized web server.

We can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. Our reports can draw their data from SQL Server databases, but also from other relational database such as Oracle, and other types of multi-dimensional or XML-based data sources such as SQL Server Analysis Services, Teradata, or Parallel Data Warehouse. 

Why SSRS Reports?

  • SSRS Reports are more flexible to implement and deploy in to the server.
  • User quickly access essential information with the ability to drill down within the reports.
  • Users can filter report data using dynamic parameters.
  • Users can create sub reports with a main report and also the main report to one or more sub reports through a set of parameters.
  • This report layout option quickly presents the data in a table format for better viewing and report distribution across different units of a business.
  • Easy export for further analysis with multiple file formats
  • Ability to develop a Reporting Portal
  • Make a report run based on your timing via Report Subscription
  • Intuitive display options
  • SSRS implements a flexible and a role-based security model to protect reporting resources. This ensures that every employee is allowed access to data which the user role is entitled to.

 

Architecture of SQL Server Reporting Services

The following diagram shows the architecture of SQL Server Reporting Services.

This architecture consists mainly of the following types of components and tools.

  • Report Builder
  • Report Designer
  • Report Manager
  • Report Server
  • Report server database
  • Data sources

Report Builder

  • This component can be used to drag and drop and for easy to use report design functionality. Report Builder is an ad-hoc end-user report publishing tool that is executed on a client computer.

Report Designer

  • The Report designer tool can be used to develop simple and complex reports. Report Designer is a publishing tool,  that is hosted in Business Intelligence Development Studio (BIDS) or Visual Studio.

Report Manager

  • This tool provides easy access to web-based reports. The default URL of the Report Manager is http://<server>/reports.

Report Server

  • It is a server that uses the SQL Server database engine to store metadata.

Report server database

  • It stores metadata, resources, report definitions, security settings, delivery data and so on.

Data sources

  • Reporting services retrieves data from data sources like relational and multidimensional data sources.

 

Processing of SQL Server Reporting Services

This diagram shows the processing of SQL Server Reporting Services.

 

On a report server, or a different server we choose, we will have access to the Report Manager website. The Report Manager website can be used to deploy and manage reports. End users run reports by the Report Manager and publish their reports if they have permission. An SQL Server Reporting Services is associated with a SQL Server instance. The SQL Server instance has two databases, the first is a Reportserver and the second one is ReportServerTempdb. The end-user sends a HTTP request for a report. The report server finds the metadata of the report and sends a request for data to the data source then after the data is returned by the data source and merged with the report definition into a report. When the report is generated then it is sent to the client. 

SSRS Grouping :

Please SSRS grouping is mainly used to display different kinds of the same data, which are repeated in single reports. There are two type of creating grouping, which are shown below.

  1. Parent Grouping It means just need to create a group with all rows outside the grouping.
  2. Child Grouping– It means just need to create a group with all rows inside the grouping.

 

Implementation of SSRS Report with Grouping:

Please find the below steps for creation of a sample SSRS Report with Grouping using Visual studio 2017.

  • Open Visual Studio 2017, then go to File->New->Project->Business Intelligence
  • Then click on Report Server Project and name a project “Sample SSRS Report Project” then click on ok.

                                                    Figure 1

  • Once project is opened then we have to create a data source. Go to Shared DataSources and click on Add New Data Source. as per below figure 2.

Figure 2

  • After Datasource wizard opens then create the connection with required database as per below.

Figure 3

  • Now Dataset should be created for required report data. Here in the below figure we have created a “EmployeeDataset” and wrote the query. (If you have stored procedure can use that too.)

                                                                                                                 Figure 4

  • Till now we have created datasource and dataset ,Now it is the time to create a SSRS Report. Go to Solution Explorer right click on Report and Click on “Add New Report” then below report wizard will open. Click on Next.

Figure 5

  • Select the Datasource which we have created earlier. Then click on Next.

Figure 6

  • Build a sql Query for required information then click on Next.
  • Select the format how we have to show the data in the report, here we have to select any of format and click on Next.
  • Click on Finish in the below wizard.

 Figure 7

  • Now SSRS Report is created and able to see the details of the employee.

               Figure 8

  • If we want to formatting as per the below figure 9.  Now we can implement the Grouping on the Tablix. In the below example row level. Here we have inserted a Table (In SSRS we call it is a Tablix) then we need to set the dataset. Here we have selected the dataset which we have created earlier “EmployeeDataset”. This is called as Shared Dataset.
  • Right click on the grid => Add Group => Row Group =>Click on Parent Group. 

Figure 9

  • Then a popup will open, then we need to select the column. Here we have selected “DepartmentName” as Group column as per the below figure 10.

                                                                                                                          Figure 10

  • Now we have to select the Child group i.e. Employee Name. Then report will show the employee names against the departments.

                          

            Figure 11

  • Select the Child group as “Employee Name” as per below diagram.

Figure 12

  • Now we have completed the steps to implement the report with Grouping the Department Names for Employees.

Figure 13

  • Now if we can preview the report able to see the employee details as per below diagram.
  • We can export the report in to multiple formats like HTML (MHTML), Excel, Acrobat, Tiff (image), XML, CSV as per the below figure 14.

Figure 14

 

This way we can implement Row Level Grouping in SSRS Report. Similarly, we can do Column, Recursive hierarchy and Details grouping also.

We have different types of Report Items like Textbox Report Item, Line Report, Rectangle Report Item, Image Report Item, Sub Report Item, Chart Report Item. We can use these report items on need basis in our reports.

 

References:

 

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/understanding-groups-report-builder-and-ssrs?view=sql-server-2017

https://www.c-sharpcorner.com/article/how-to-use-grouping-in-ssrs/

https://docs.microsoft.com/en-us/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports?view=sql-server-2017

https://www.codeproject.com/Articles/621332/Building-Your-First-SSRS-Report

https://www.tutorialspoint.com/ms_sql_server/ms_sql_server_reporting_services.htm

 

 

 

About The Author

Leave a Reply

*