SQL Server Reporting Services Tips and Tricks to Improve the End User Experience

Problem

In this tip, we will look at a few SQL Server Reporting Services (SSRS) Tips and Tricks to improve the end-user experience.

Solution

Let's take a look at the following Tips and Tricks:

• Display Total Number of Pages while Navigating
• Display Everything in a Single Page
• Display Report Parameter Selection
• Display No Rows Error Message
• Page Settings for Optimal Printing

Note: This tip assumes you have experience in building simple to medium complexity SSRS Reports.

For the purpose of this demonstration, let's build a simple report.

Create a new project, add a new report, and create a data source pointing to AdventureWorksDW database. Then create a dataset with the following query.

Report Main Dataset - "dstSSRSTipsPart2"

SELECT
    DPC.EnglishProductCategoryName AS Category
    , DPS.EnglishProductSubcategoryName AS Subcategory
    , DP.EnglishProductName AS Product
    , FIS.OrderDate
    , FIS.SalesAmount
    , FIS.Freight AS FreightAmount
    , FIS.TaxAmt AS TaxAmount
FROM
    dbo.FactInternetSales AS FIS
    INNER JOIN dbo.DimProduct AS DP
        ON FIS.ProductKey = DP.ProductKey
    INNER JOIN dbo.DimProductSubcategory AS DPS
        ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
    INNER JOIN dbo.DimProductCategory AS DPC
        ON DPS.ProductCategoryKey = DPC.ProductCategoryKey
    INNER JOIN dbo.DimDate AS DD
        ON FIS.OrderDateKey = DD.DateKey
WHERE DD.CalendarYear = 2005

Next, add a Table report item to the designer with five columns as Category, Subcategory, Product, OrderDate, and SalesAmount in the same sequence. The completed report, with some basic formatting, should look as shown below.

Display Total Number of Pages while Navigating a SSRS Report

With the release of SQL Server 2008, many new features were introduced in SSRS. One of those features was "On-Demand" report processing, which is a very nice performance improvement especially for large reports. With this feature, when the report is viewed in Report Manager, SSRS displays estimated number of pages with a question mark ("?") at the end, to indicate that there are more pages to be displayed, as shown below.

Often times users mistake this to be a bug, but this is actually the result of a feature. And hence, there are requests to display the total number of pages and remove the question mark. Let's see how we can achieve this.

To address this issue, we need to use the "TotalPages" Built-in Field either in the report header or footer. Add a textbox either in the header or footer with the following expression and hide the textbox, so that it's not visible to the users.

=Globals!TotalPages

This will fix the issue and the total number of pages are displayed in the navigation bar and the question mark ("?") disappears as shown below.

Display Everything in a Single Page for a SSRS Report

In certain scenarios, especially in cases like dashboards, users want all the contents to be displayed in a single page. However, when the contents of the page are more than what can actually fit on one page, the report spans across two pages.

To display all the contents of a report in one page when the report is rendered in Report Manager, set the Interactive Height property of the report to zero as shown below.

For the purpose of this demonstration, set this property for our report and notice that, all the contents are displayed in a single page as shown below. Note: Keeping all the contents on a single page for a long report can adversely impact the report rendering performance. Test, measure, and evaluate whether it's suitable for your scenario or not, before the report is released to users.

Display Report Parameter Selection for a SSRS Report

Parameters/Filters are a great way for the users to interact with the report. It is a good practice to display the parameter selection in the report as it offers various benefits including the following:

• At a quick glance, it shows the list of values selected in each of the parameters.
• When the report is exported and is shared with others, they get a picture of what selection of parameters resulted in the output report.

For the purpose of this demonstration, add two parameters to the report - Category & Subcategory. Configure the parameters using the below steps.

Create two datasets using the below two queries, one for each parameter. The first dataset with the first query is for the Category parameter and the second dataset with the second query is for the Subcategory parameter.

Dataset for Category Parameter - "dstCategory"

SELECT
    ProductCategoryKey AS CategoryKey
    , EnglishProductCategoryName AS CategoryName
FROM dbo.DimProductCategory

Dataset for Subcategory Parameter - "dstSubcategory"

SELECT
    ProductSubcategoryKey AS SubcategoryKey
    , EnglishProductSubcategoryName AS SubcategoryName
FROM dbo.DimProductSubcategory
ORDER BY ProductCategoryKey, EnglishProductSubcategoryName

Create and configure the two parameters using the following settings.

Parameter Property	Category Parameter	Subcategory Parameter
Name	pCategory	pSubcategory
Prompt	Category	Subcategory
Data Type	Integer	Integer
Selection	Multi-select (Allow Multiple Values)	Multi-select (Allow Multiple Values)
Available Values
Dataset	dstCategory	dstSubcategory
Value Field	CategoryKey	SubcategoryKey
Label Field	CategoryName	SubcategoryName
Default Values
Dataset	dstCategory	dstSubcategory
Value Field	CategoryKey	SubcategoryKey

Note that, for simplicity, we are not cascading the parameters even though these two parameters are good candidates for cascading parameters.

Next update the main report dataset "dstSSRSTipsPart2" with the following query. This is to link the above two parameters with the report's main dataset.

SELECT
    DPC.EnglishProductCategoryName AS Category
    , DPS.EnglishProductSubcategoryName AS Subcategory
    , DP.EnglishProductName AS Product
    , FIS.OrderDate
    , FIS.SalesAmount
    , FIS.Freight AS FreightAmount
    , FIS.TaxAmt AS TaxAmount
FROM
    dbo.FactInternetSales AS FIS
    INNER JOIN dbo.DimProduct AS DP
        ON FIS.ProductKey = DP.ProductKey
    INNER JOIN dbo.DimProductSubcategory AS DPS
        ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
    INNER JOIN dbo.DimProductCategory AS DPC
        ON DPS.ProductCategoryKey = DPC.ProductCategoryKey
    INNER JOIN dbo.DimDate AS DD
        ON FIS.OrderDateKey = DD.DateKey
WHERE 
    DD.CalendarYear = 2005
    AND DPC.ProductCategoryKey IN (@pCategory)
    AND DPS.ProductSubcategoryKey IN (@pSubcategory)

Map the Parameters in the above dataset with the corresponding Report Parameters. Parameter Mapping should look as shown below.

Next add two textboxes to the report above the Table with the following expressions.

Expression for 1st Textbox for Category Parameter

="Selected Categories: " & Join(Parameters!pCategory.Label, ", ")

Expression for 2nd Textbox for Subcategory Parameter

="Selected Subcategories: " & Join(Parameters!pSubcategory.Label, ", ")

Your report design surface should look as shown below.

Now render the report with default values selected in both Category and Subcategory parameters and the rendered report looks as shown below.

Next select "Bikes" under the Category Parameter and "Mountain Bikes, Road Bikes, Touring Bikes" under the Subcategory parameter and render the report and the rendered report looks as shown below.

Notice that, based on the values selected in the parameters, the corresponding values/labels are displayed in the report. This can be really useful information specifically for users viewing the report offline.

Display No Rows Error Message for a SSRS Report

Sometimes when there is no data to display in the report, the rendered report is displayed as blank, which is not very intuitive to the end users. SSRS has a feature, which allows displaying a custom message when there are no rows to display in the table.

Select the Table and go Properties window and notice that there is a property called "NoRowsMessage" under "No Rows" category. Enter the following text as a value for that property. Notice that, you can either enter static text or it can be dynamic text based on an expression.

No data to display for the selected input parameter values!

Also, notice that, there are additional formatting options under "No Rows" category. Choose the font style, weight, color etc. and the properties windows should look as shown below.

"NoRowsMessage" property is applicable to Table, Matrix, and List data regions. The corresponding property for charts is "NoDataMessage" and for the color scale for a map is "NoDataText".

Now render the report by selecting "Components" in the Category filter and leave the default selection in the Subcategory filter. The output report should look as shown below.

Page Settings for Optimal Printing for a SSRS Report

Often when the reports are exported or printed, we see different issues, which can be fixed by making appropriate page settings. Following are some of the commonly encountered issues:

• Report items getting clipped across multiple pages. For instance, few columns appear on one page and next set of columns appear on a different page.
• Blank pages appear in the PDF or Printed Versions.

To address this issue, we need to configure the page settings accordingly. Use the following rule of thumb while setting the width of a page.

Page Width >= Body Width + Left Margin + Right Margin

Make a note of the Width of the Report Body in the properties window. Make sure to select "Body" from the drop down while checking the size.

Now configure the page width as shown below. Go to "Report" --> "Report Properties..." --> "Page Setup".

As we can see in this tip, SSRS is a very powerful reporting tool, and we just need to explore it to cater to most of the end user requirements.