Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Automatic SSRS report output of all report parameter combinations


By:   |   Last Updated: 2016-04-08   |   Comments (3)   |   Related Tips: > Reporting Services Development

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 which you can download for free from CodePlex at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.

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.

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


Last Updated: 2016-04-08


next webcast button


next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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 08, 2016 - 9:19:46 PM - Daniel Farina Back To Top

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 https://www.mssqltips.com/tipimages2/4237_CountryCodes_Parameters.jpg.

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 08, 2016 - 2:59:38 PM - Boris Back To Top

 

 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.

Boris.

 

 


Learn more about SQL Server tools