Note: this article is published on 08/08/2024.

This series of articles will discuss SQL Server Reporting Services, including the related Services:

A - Introduction

The services discussed include:

The first article of this series discussesed the Server side Instalation and Configuration. The second discussed the Client side (the Development Invironment) Installation and Configuration, inluding Visual Studio setup by installing Extentions and a Desktop app Report Builder. This article will make a sample of Report from Visual Studio Environment by using both the pre-setup SSIS, SSAS, Server and the SQL Server DAta Tools

This will be the content of this article:

B - Create a report server project

C - Create a report definition file (RDL)

Next, you create a report definition file (RDL). This process involves setting up the report within the project you created.

D - Set up a connection

E - Define a Transact-SQL query for report data

then the Dataset Properties dialog opens with the Query section highlighted.

SELECT
   soh.OrderDate AS [Date],
   soh.SalesOrderNumber AS [Order],
   pps.Name AS [Subcat],
   pp.Name as [Product],
   SUM(sd.OrderQty) AS [Qty],
   SUM(sd.LineTotal) AS [LineTotal]
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh
      ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd
      ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp
      ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps
      ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc
      ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,soh.SalesPersonID  
HAVING ppc.Name = 'Clothing'

We have

Add a table data region and fields to a report layout

Report Designer draws a table data region with three columns in the center of the design surface.

Preview your report

While designing, preview your report frequently. By doing so, you validate the design and data connections and you can correct errors and issues as you go.

F - Format the Report

Formatting.

Date:

Currency

Change text style and column widths

You can add other formatting to your report by highlighting the header, and then adjusting the widths of the data columns.

G - Group data in a report

H - Add totals to a report

Add total:

Line Total:

You can add totals to your report to summarize data. Totals help you quickly understand aggregate values and enhance the report's usability.

  1. Switch to the Design view.

  2. Right-click the data region cell that contains the [LineTotal] expression, and select Add Total. Report Designer adds a row with a sum of the dollar amount for each order.

  3. Right-click the cell that contains the field [Qty], and select Add Total. Report Designer adds a sum of the quantity for each order to the totals row.

  4. In the empty cell to the left of the Sum[Qty] cell, enter "Order Total".

  5. Select the two sum cells and the label cell in the row where you added the total cells.

  6. Select Format > Background Color > Light Gray.

Daily Total

You can add a daily total to your report. This step provides a daily summary at the end of each date grouping and helps you quickly identify daily aggregates within your report.

  1. Right-click the [Order]expression cell, and select Add Total > After. Report Designer adds a new row containing sums of the [Qty] and [Linetotal] values for each day, and the string "Total" to the bottom of the [Order]expression column.

  2. Enter the word "Daily" before the word "Total" in the same cell, so it reads "Daily Total".

  3. Select that cell and the two adjacent total cells to the right side and the empty cell in between them.

  4. Select Format > Background Color > Orange.

Grand Total

You can add a grand total to your report to summarize all the data across the entire report. A grand total provides a comprehensive summary and makes it easier to understand the overall data at a glance.

  1. Right-click the [Date] expression cell, and select Add Total > After. Report Designer adds a new row containing sums of the [Qty] and [LineTotal] values for the entire report, and the string "Total" to the bottom of the [Date] expression column.

  2. Enter the string "Grand" before the word "Total" in the same cell, so it reads "Grand Total".

  3. Select the cell with "Grand Total", the two Sum() expression cells and the empty cells between them.

  4. Select Format > Background Color > Light Blue.

  5. Select OK.

Preview the report

Switch to the Preview tab so you can view the report as it appears when published. Look for any errors or issues you can fix before finalizing the report.

  1. Select the Preview tab.

  2. In the Preview toolbar, choose Last Page, which looks like . The Grand Total values display at the end of the report.

I - Add a report parameter

SELECT soh.OrderDate AS Date, soh.SalesOrderNumber AS [Order], pps.Name AS Subcat, pp.Name AS Product, SUM(sd.OrderQty) AS Qty, SUM(sd.LineTotal)  AS LineTotal  
FROM Sales.SalesPerson AS sp INNER JOIN  
  Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID INNER JOIN  
   Sales.SalesOrderDetail AS sd ON sd.SalesOrderID = soh.SalesOrderID INNER JOIN  
   Production.Product AS pp ON sd.ProductID = pp.ProductID  
INNER JOIN  
   Production.ProductSubcategory AS pps ON pp.ProductSubcategoryID = pps.ProductSubcategoryID   
INNER JOIN  
    Production.ProductCategory AS ppc ON ppc.ProductCategoryID = pps.ProductCategoryID  

WHERE (UPPER(SalesOrderNumber) =UPPER(@OrderNumber) or  @OrderNumber IS NULL)  

GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name, soh.SalesPersonID  
HAVING (ppc.Name = 'Clothing')

J - Publish the report to the Report Server

If you see a message similar the following Output window, it indicates a successful deployment.

------ Build started: Project: tutorial, Configuration: Debug ------  
Skipping 'Sales Orders.rdl'. Item is up to date.  
Build complete -- 0 errors, 0 warnings  
------ Deploy started: Project: tutorial, Configuration: Debug ------  
Deploying to `https://[server name]/reportserver`  
Deploying report '/tutorial/Sales Orders'.  
Deploy complete -- 0 errors, 0 warnings  
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========  
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

K - Export to Excel or others

In both development environment or server deployment, there is a button that we can use to export the report to MS Excel or other files:

References: