Automatic SSRS report output of all report parameter combinations

Problem

You already have a SQL Server Reporting Services (SSRS) sales report that takes parameters for Country and State. Each time the report is run a user selects the specific Country and State for the report. A request has been made to deliver one report via email that includes all of the combinations for each country and state. In this tip I will show you how we can achieve this without modifying the report.

Solution

At first it seems like something impossible to accomplish. When I was asked to do a subscription like this the first thing that came to my mind was to create a report with all of the respective sub-reports. But in my case the input parameters weren’t limited to a range of values that never change, so the number of sub-reports cannot be predefined in the master report. Then I realize that the way to do this was with a table of sub-reports. Keep reading and I will show you how it works.

For the purposes of this tip I am using the AdventureWorksDW2012 database.

Stored Procedures for Report Data in SSRS

Let’s start by creating the base report. The code below is a stored procedure that we will used for the report data source.

USE AdventureWorksDW2012
GO
CREATE PROCEDURE usp_DailySales
    @CountryRegionCode NVARCHAR(6) ,
    @StateProvinceCode NVARCHAR(6)
AS
    SELECT  EnglishCountryRegionName ,
            StateProvinceName ,
            OrderDate ,
            SUM(SalesAmount) AS DaySales
    FROM    dbo.FactInternetSales S
            INNER JOIN DimGeography G ON G.SalesTerritoryKey = S.SalesTerritoryKey
    WHERE   g.CountryRegionCode = @CountryRegionCode
            AND g.StateProvinceCode = @StateProvinceCode
    GROUP BY EnglishCountryRegionName ,
            StateProvinceName ,
            OrderDate
    ORDER BY EnglishCountryRegionName ,
            StateProvinceName ,
            OrderDate
GO

As you may notice, the previous stored procedure receives two parameters, @CountryRegionCode and @StateProvinceCode. For that reason, we must create two stored procedures to feed the list box of available parameter values.

USE AdventureWorksDW2012
GO
CREATE PROCEDURE usp_GetCountryRegionCode
AS
    SELECT  CountryRegionCode ,
            EnglishCountryRegionName
    FROM    dbo.DimGeography
    GROUP BY CountryRegionCode ,
            EnglishCountryRegionName 
GO

USE AdventureWorksDW2012
GO
CREATE PROCEDURE usp_GetProvinceCode
    @CountryRegionCode NVARCHAR(6)
AS
    SELECT  StateProvinceCode ,
            StateProvinceName
    FROM    dbo.DimGeography
    WHERE   CountryRegionCode = @CountryRegionCode
    GROUP BY StateProvinceCode ,
            StateProvinceName 
GO

Creating the Reporting Services Data Sources

Now in a new report project, add a new data source pointing at your test database. Then add a dataset, name it ChartData and proceed to configure the ChartData dataset to obtain its data from the usp_DailySales stored procedure we created earlier. This will add two parameters to the report.

To get the information for the input parameters setup another dataset named CountryCodes to execute the usp_GetCountryRegionCode stored procedure. We will use the return value from this dataset as an input parameter for the ChartData dataset.

We have to create one last dataset to obtain a value for the @StateProvinceCode parameter. Name this dataset StateCodes and configure it to run the usp_GetProvinceCode stored procedure.

As an additional step in the parameters view, map the @CountryRegionCode stored procedure parameter to get its value from the [@CountryRegionCode] report parameter as shown below.

Configuring SSRS Report Parameters

Now we are going to configure the available values for the CountryRegionCode and StateProvinceCode. To do so, on each parameter go to the Available Values page and select the radio button “Get values from a query”. For the CountryRegionCode parameter, select CountryCodes as the dataset, CountryRegionCode as the value field and EnglishCountryRegionName for its label.

Then do the same with StateProvinceCode, this time select StateCodes as the dataset, StateProvinceCode as the value field and EnglishStateProvinceName for its label.

Creating the SSRS Subreport

The report we are creating is going to show a chart, so drag a Chart control to the report and configure it as a column chart.

On to the Chart Data window and select DaySales for Values and OrderDate for Category Groups.

Then for the Report Title drag a textbox into the report and open the Expression Editor and paste the following code:

="Daily Sales on " + First(Fields!StateProvinceName.Value, "ChartData") + ", " +First(Fields!EnglishCountryRegionName.Value, "ChartData")

This is how the report should look after you select a value for the parameters.

Creating the SSRS Master Report

Now we will create the main report that will be used to create the mail subscription. Let’s start by adding a Shared Data Source to the project and a shared dataset with the following query.

SELECT CountryRegionCode, StateProvinceCode FROM dbo.DimGeography
GROUP BY CountryRegionCode, StateProvinceCode
ORDER BY CountryRegionCode, StateProvinceCode

The next step is to add a table component to this master report and in one cell of the table insert a Subreport component. It will look like the following image. Of course, we have to remove the Table Header as well as the other columns and leave a single cell then make this the appropriate size and bind the table to the shared dataset.

The final step is to map the subreport parameters to the values provided by the shared dataset in the table.

This is how the master report will look when we execute it. You can see that it will generate a chart for each country and state combination and display these as subreports inside the master report.

Next Steps

• You can download the Visual Studio Project and the SQL code used in this example from this link.
• Are you new to Reporting Services? This tutorial will be the best place to start: SQL Server Reporting Services Tutorial.
• You can find more SSRS tips here.

Daniel Farina

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.

• MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019