Cascading parameters, powerful technique how make your report navigation smarter and comfortable for final users. I will demonstrate cascading parameters implementation on one version of the Sales report I created in previous posts, for example Reporting Services Report Tablix altering color.
Goal
Add two Parameters, one for Region data and second one for Province data. Create cascading relationship between these parameters, when you choose Region item, list of Province items is filtered base on the Region selection.
In short
- Prepare database and report datasets for parameters
- Create parameters and mapping parameters to datasets
- Modify dataset returning data and implement Splitting function
- Link parameters with dataset
In one of my post I describe basic types for implementation of parameters. In our case we would like to have report with parameters, where user can choose predefined values form select box. There is a need to prepare available values to have them in a select box. Parameters offer possibility to set available values manually, to have them embedded in report. But in our case, we will prepare available values taken directly from database. There reason I would prefer this approach are:
- It is better from maintenance point of view, they can change on database level and we don’t have to change the report
- In case that there are lots of values, so it would be hard task to set them manually
Create report and database datasets
Let’s start with parameter for Country Region.
To fill the report dataset, we will create SQL stored procedure. We transfer the main report dataset to SQL stored procedure too. Datasets mapped to stored procedures have some benefits I will describe in another post. You can download whole script_sales_report to run on your database, I will work with Adventure Works objects I used in previous posts, for example Create report in two minutes.
Firstly, we will create SQL stored procedure for RegionCode parameter.
CREATE PROCEDURE dbo.GetCountryRegion AS SELECT NULL CountryRegionCode, 'All' EnglishCountryRegionName -- ALL MEMBER UNION ALL SELECT DISTINCT CountryRegionCode,EnglishCountryRegionName FROM dbo.DimGeography
Query select distinct Country Region data and add “All” member for case we would like to get all country regions. I will show you how you can work with parameters in case you would like to filter data based on all items selected on your parameters. There are few ways how to do that, I will show you 2 basic approaches. Country Region will not be set as type of multivalued parameter, that’s the reason why I use “All” member in the query, it will actually disable filters in case you choose “All“ member. You will see later in this post.
After the SQL stored procedure is created go to Report Data pane and add new dataset, lets name it GetCountryRegion. Map it to your Data source you use to connect to your database, select Query type as Stored Procedure, then go to Select or enter stored procedure name select box and you should see stored procedure you created in previous step.
By clicking on Refresh Fields button, you should see attributes returned from stored procedure. You should see new dataset added as you can see on picture bellow.
Do the same steps described above for the Province dataset. By running script bellow you will create the second one stored procedure querying province data.
CREATE PROCEDURE dbo.GetProvince @RegionCode VARCHAR(2) AS SELECT DISTINCT StateProvinceCode,StateProvinceName FROM dbo.DimGeography WHERE ISNULL( @RegionCode,CountryRegionCode ) = CountryRegionCode
You can see that this stored procedure does not return “All” member and it has one parameter RegionCode. Since Province parameter will be multivalued parameter type, “All” member is not needed. With multivalued parametre type, user get possibility to select multiple values from predefined set.
Parameter used in stored procedure will be used to filter dataset based on data selected in RegionCode Parameter. And here we have cascading relationship of the parameters. The RegionCode parameter sets selected values to SQL stored procedure for GetProvince dataset parameter to filter queried GetProvince data. Finally, you should have 2 new datasets in Report Data pane as shown on picture bellow. You can see that there was created RegionCode parameter automatically. The cause is that Province dataset expects such a parameter.
You can check that the RegionCode parameter is already mapped to the GetProvince dataset. Right click on GetProvince dataset and Parameters section, see picture bellow.
Create parameters and mapping them to datasets
For RegionCode there is Parameter added already, as mentioned above. Let’s go to Report Parameter Properties, right click on the parameter and Parameter Properties from popup menu. In the General section, change Prompt text box area to text you would lie to display next to parameter selection. Select the parameter Data type, in our case we select Text type, we will use Country Region code as key value. It will be set to GetProvince dataset and main dataset getting data. Because we would like to have this parameter visible in the Report we leave Parameter visibility settings as they are.
In next step is set Available Values. In our case the values will be taken from prepared dataset, set checkbox Get values from a query and choose dataset GetCountryRegion from Dataset select box.
When dataset is selected set Value field with keys that will be passed to other datasets, obviously key values and Label field, names for these key values, will be displayed in the Report.
As the next step we set Default Values. It is not necessary to set this option. But if we would like to run the Report by default every parameter should have set its default values. In case that the default value is not set, the Report does not render by default and user displaying the report should set the parameter values as first step. For our report set (Null) as default value. Null is key value for “All” member item in the GetRegion dataset. That means that we would like to run the Report querying data for all regions by default.
Do the same above-mentioned steps for the GetProvince parameter, with these two differences mentioned bellow.
- The GetProvince parameter type will be multivalued
- Default values will be taken from its dataset for available values
Go to GetProvince parameter, Report Parameter Properties, General tab and check Allow multiple values option. Now you cannot set the Allow null value option, since SSRS expects to get some values by default.
Because NULL value is not allowed now, we will use set of values taken from the GetProvince dataset. Go to DefaultValues tab and set Get values form a query and select GetProvince dataset. For the value field set StatetProvinceCode as key value.
Mapping parameters to datasets
In final phase we change current dataset returning fact data that the data will be taken from SQL Stored Procedure. Run script bellow to create the Stored Procedure and Split function used in the SQL Stored Procedure. I will describe in more detail bellow.
You can download Split function from many SQL web sites or use STRING_SPLIT function implemented since SQL Server 2016.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE FUNCTION [dbo].[SplitString]
( @vcDelimitedString NVARCHAR(MAX),
@vcDelimiter NVARCHAR(10))
RETURNS @tblArray TABLE
(
ElementID INT IDENTITY(1,1)
Element VARCHAR(MAX)
) AS BEGIN
DECLARE @siIndex SMALLINT, @siStart SMALLINT, @siDelSize SMALLINT
SET @siDelSize = LEN(@vcDelimiter)
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN END
Main dataset changed to SQL server stored procedure with Parameters for Region and Province filter.
CREATE PROCEDURE dbo.GETData
@OrderDateKey INT , @OrderDateKey2 INT,
@CountryRegion VARCHAR(2),
@Province VARCHAR(MAX)
AS
SELECT
DimCustomer.FirstName
,DimCustomer.LastName
,DimCustomer.MiddleName
,DimCustomer.Gender
,DimCustomer.AddressLine1
,SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount
,SUM(FactInternetSales.OrderQuantity) AS Sum_OrderQuantity
,DimGeography.City
,DimGeography.StateProvinceName
,DimGeography.EnglishCountryRegionName
FROM
DimCustomer
INNER JOIN DimGeography
ON DimCustomer.GeographyKey = DimGeography.GeographyKey
INNER JOIN FactInternetSales
ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey
JOIN dbo.SplitString(@Province,';') tmp ON tmp.Element =DimGeography.StateProvinceCode
WHERE
FactInternetSales.OrderDateKey >= @OrderDateKey
AND FactInternetSales.OrderDateKey <= @OrderDateKey2
AND ISNULL(@CountryRegion ,DimGeography.CountryRegionCode )= DimGeography.CountryRegionCode
GROUP BY
DimCustomer.FirstName
,DimCustomer.LastName
,DimCustomer.MiddleName
,DimCustomer.Gender
,DimCustomer.AddressLine1
,DimGeography.City
,DimGeography.StateProvinceName
,DimGeography.EnglishCountryRegionName
Map dataset the DataSet1 to the Stored Procedure. Click on Refresh Fields to get the dataset fields and parameters to actual state.
Because the SQL Stored Procedure parameter, which takes Region code is named as CountryRegion, new Report parameter with that name is created. If we would choose RegionCode as parameter name, report would use the RegionCode parameter which already exists in the Report. For GetProvince parameter there is the same situation, since in stored procedure is named as Province.
Delete the automatically created parameters, since we will not use them anymore.
Link parameters with dataset
Map the correct parameters to the dataset DataSet1. Right click on DataSet1, Dataset Properties, Parameters Section and map Report RegionCode Parameter to dataset parameter CountryRegion and the report GetProvince parameter to the dataset parameter Province. Since the GetProvince parameter is of multivalued type there has to be done one more step to make it functional.
Because the multivalued parameter type is actually type of System.Array, we have to use JOIN function to convert array members to text separated by “;” char to be possible to send the data to SQL Stored Procedure Parameter.
On the picture bellow you can see final Dataset Properties – Parameters tab.
Take look inside SQL Stored Procedure GetData. Pay attention to usage of 2 parameters filtering data.
For RegionCode parameter.
AND ISNULL(@CountryRegion ,DimGeography.CountryRegionCode )= DimGeography.CountryRegionCode
For region parameter, there is used bellow mentioned predicate saying if @CountryRegion parameter is NULL, which means that user chose All member, ignore this filter because DimGeography.CountryRegionCode = DimGeography.CountryRegionCode. Be aware!, that this condition cannot be used in case that CountryRegionCode column could have NULL values, because NULL=NULL will not return data row. In such a case the condition has to be rewritten. Other posts will be concentrated on this problematic.
In case of @Province parameter we chose different approach. We put values to @Province parametre as text delimited by “;” char, we created in dataset using JOIN function. Using SplitString function we get dataset from this table valued function and we can join values. The problem of this solution could be in case that @Province parameter obtains many members. In such a case performance could get wrong. I will describe such scenarios in other posts.
JOIN dbo.SplitString(@Province,';') tmp ON tmp.Element =DimGeography.StateProvinceCode
Finally, we got it. You can try the report.
Select Country Region parameter and set Canada for example, GetProvince parameter province values are taken from Canada region only.
GetProvince parameter members are filtered based on Country Region parameters selection. You can download report here ReportSalesCascadingParameters.
