Automatic SSRS report output of all report parameter combinations
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 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.
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.
- 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.
About the author
View all my tips