SSRS – Cascading Parameters

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.


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

  1. Prepare  database and report datasets for parameters
  2. Create parameters and mapping parameters to datasets
  3. Modify dataset returning data and implement Splitting function
  4. 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
 SELECT NULL CountryRegionCode, 'All'  EnglishCountryRegionName -- ALL MEMBER
 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.

Dataset Properties
Picture 1 – Dataset Properties

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.

Report data - datasets
Picture 2 – Report data – datasets

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.

 @RegionCode VARCHAR(2)
 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.

Report data - datasets
Picture 3 – Report data – datasets

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.

Dataset Properties - parameters
Picture 4 – Dataset Properties – parameters

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.

Report parameter properties
Picture 5 – Report parameter properties

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.

Report parameter properties
Picture 6 – Report Parameter Properties

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.

Dataset properties - set parameters
Picture 7 – Report Parameter Properties – Avaliable Values

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.

Parameter Properties - Default Values
Picture 8 – Parameter Properties – Default Values

Do the same above-mentioned steps for the GetProvince parameter, with these two differences mentioned bellow.

  1. The GetProvince parameter type will be multivalued
  2. 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.

Report Parameter Properties - Allow multiple values
Picture 9 – Report Parameter Properties – Allow multiple values

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.

Report Parameter Properties - Default Values
Picture 10 – Report Parameter Properties – Default 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.



-- =============================================
CREATE FUNCTION [dbo].[SplitString] 
( @vcDelimitedString NVARCHAR(MAX), 
  @vcDelimiter NVARCHAR(10)) 
  RETURNS @tblArray TABLE    
      ElementID INT  IDENTITY(1,1)   
      Element VARCHAR(MAX)   
    ) AS BEGIN 
  SET @siDelSize      = LEN(@vcDelimiter)     
  WHILE LEN(@vcDelimitedString) > 0     
  SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)   
      IF @siIndex = 0  
         INSERT INTO @tblArray VALUES(@vcDelimitedString)      
            INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))           
            SET @siStart = @siIndex + @siDelSize               
            SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)        

Main dataset  changed to SQL server stored procedure with Parameters for Region and Province filter.

@OrderDateKey INT , @OrderDateKey2 INT,
@CountryRegion VARCHAR(2),
@Province VARCHAR(MAX)
  ,SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount
  ,SUM(FactInternetSales.OrderQuantity) AS Sum_OrderQuantity
  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
  FactInternetSales.OrderDateKey >= @OrderDateKey
  AND FactInternetSales.OrderDateKey <= @OrderDateKey2
  AND ISNULL(@CountryRegion ,DimGeography.CountryRegionCode )= DimGeography.CountryRegionCode

Map dataset the DataSet1 to the Stored Procedure. Click on Refresh Fields to get the dataset fields and parameters to actual state.

Dataset Properties
Picture 11 – Dataset Properties

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.

Delete parameter
Picture 12 – Delete parameter

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.

Dataset properties - set parameters
Picture 13 – Dataset properties – set parameters

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.

Dataset Parameter Expression
Picture 14 – Dataset Parameter Expression

On the picture bellow you can see final Dataset Properties – Parameters tab.

Dataset Properties - Parameters
Picture 15 – Dataset Properties – Parameters

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.

Report Parameters Selection
Picture 16 – Report Parameters Selection

Select Country Region parameter and set Canada for example, GetProvince parameter province values are taken from Canada region only.

Report Parameters Selection
Picture 17 – Report Parameters Selection

GetProvince parameter members are filtered based on Country Region parameters selection. You can download report here ReportSalesCascadingParameters.

Reporting Services Report Tablix altering color

IAltering color in Report Tablix is common task for reporting developer. It helpes user to be better oriented in output data.

I will demonstrate this functionality on report created in one of previous post. I chose report with grouping. I will implement altering colour per row on each of Tablix group region. Lets start with EnglishCountryRegionName group region. Select Textboxes, highlighted on picture bellow.

Go to Report Properties pane to Fill section. There is set static color as you see on the picture bellow.

Replace the static colour with following expression.

=IIF(RUNNINGValue(Fields!EnglishCountryRegionName.Value,CountDistinct,"DataSet1") MOD 2 = 1, "#9eb6e4", "White")

I try to explain the expression in more detail. We use function RunningValue which takes tree arguments. The first one is the field value, we use EnglishCountryRegionName value returnign name of each country region. The second one parameter is aggregate function. In our case it is count distinct, which will be calculated for each EnglichCountryRegionName field. The last one parameter sets scope for which the function is used. In our case we can put Nothing or “DataSet1” as the scope.

In design mode, there will not be visible any colour, Expression will be rendered when the report is running.

When running report, we can see that background colour changes for each EnglishCountryRegionName value.

Repeat the same for other groups to achieve similar report design on picture bellow.


SSAS – user does not have permission to create a new object

I deployed OLAP database to SSAS on customers servers many times. Few times I got stuck of that I was not able to create object trough XMLA script and I was so close, and I didn’t know that.

Of course, it depends on your permissions, but in my case, just run Management Studio like Administrator. What is the key to succeed

Sometimes the solution is so easy.