Automatic SSRS report output of all report parameter combinations

By:   |   Comments (4)   |   Related: > Reporting Services Development


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.


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 which you can download for free from CodePlex at this link

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

    @CountryRegionCode NVARCHAR(6) ,
    @StateProvinceCode NVARCHAR(6)
    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 ,
    ORDER BY EnglishCountryRegionName ,
            StateProvinceName ,

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

CREATE PROCEDURE usp_GetCountryRegionCode
    SELECT  CountryRegionCode ,
    FROM    dbo.DimGeography
    GROUP BY CountryRegionCode ,

USE AdventureWorksDW2012

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

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.

Setting up ChartData Dataset.

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.

Setting up CountryCodes 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.

Setting up StateCodes Dataset.

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.

Parameter Mapping for ChartData Dataset.

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.

Configure Sub Report CountryRegionCode Parameter to be obtained from CountryCodes Dataset.

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

Configure Sub Report tateProvinceCode Parameter to be obtained from StateCodes Dataset.

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.

Creating a Column Chart.

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

Screen Capture of Chart Definition Window.

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.

This is How the Report Should Look

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.

Insert a Sub Teport Component in a Table Cell

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

Map the Sub Report Parameters to the Dataset.

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.

Screen Capture of Main Report Execution.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, September 6, 2021 - 1:38:22 AM - Sven Texas Back To Top (89211)
Thank you Daniel for this awesome article - it worked as a charm. The move to paginated reports has stopped my process working. So, as an alternative (for anyone else reading), I removed the main parameter, and just included it as a field in the dataset. And then added a "Rectangle" around my chart, and then added both the rectangle and the chart to a cell inside a Matrix, AND then finally, grouped the Matrix (by the value that used to be the parameter), and then RUN - perfect. You helped me enormously - hopefully this helps someone else.

Friday, April 22, 2016 - 7:03:17 AM - Charley Hearn Back To Top (41307)

Is there a reason why you didn't just put the chart in a list box grouped by country and state?  That would prevent multiple round trips to the database for each country and state pair

Friday, April 8, 2016 - 9:19:46 PM - Daniel Farina Back To Top (41175)

Hi Boris!

Thank you for reading!

I am mapping the report parameter [@CountryRegionCode] to the dataset “StateCodes” @CountryRegionCode parameter. You have to right click the dataset named “StateCodes” and hit properties on the dropdown menu. Then on the parameters page do the mapping like on the next image

You can try to download the project’s source code (I used SQL Server Data Tools for Visual Studio 2013). If that is not your version, you can create a new Reporting Services project with the version you have and import the .rdl files (those doesn't vary amongst versions).

Please let me know if I answered your question.


Friday, April 8, 2016 - 2:59:38 PM - Boris Back To Top (41171)


 Hello Daniel,

In step mapping the @CountryRegionCode stored procedure parameter to get its value from the [@CountryRegionCode] report parameter are you using ChartData dataset? I'm using Data tools for SQL Server 2014, and I don't know how to get Parametrs Property?

Thank you.




get free sql tips
agree to terms