Tag Archives: parameters

ByHariharan Rajendran

SSRS Mobile Report with Cascading Parameter

Configuring a cascading parameter for SSRS report is easy. We used to play with multiple datasets. To make it simple, if we have two parameters in a report, the second parameter values are based on first parameter selected value.

Example,

I have following two parameters in a report.

  1. Country
  2. City

Country Parameter will show all the available country and city parameter will show all the cities irrespective of country. In a business scenario, this needs to be fixed as users will expect to see only cities relevant to the countries.

If user Selects India, then the city should show only the cities from India.

Check out my below post for cascading parameter in SSRS report.

Cascading Parameter in SSRS

The same logic can be applied in SSRS mobile report but with different procedure.

Pre-Requisites:

  1. Shared Dataset
  2. Mobile Report Publisher

In this scenario, I am taking AdventureWorksDW database for better understanding.

Follow the below steps,

Step 1: Create a shared Dataset and deploy to report manager.

Step 2: Open Mobile report publisher and add the shared dataset. In this case dataset from AdventureWorksDW database.

Step 3: Drag and Drop two selection list. You can maximise or minimise to show only one value at a time as like below.

Step 4: Go to Data and Add data. Here I added a shared dataset which contains Product Category Name and Product Subcategory Name fields.

 Step 5: Rename the Selection list 1 to Category and selection list 2 to Sub Category

Step 6: Choose our dataset for both the selection lists. Make sure to choose Category Key and label for selection list 1 and Subcategory Key and Label for selection list 2.

Step 7: Add the checkbox on “Filter these dataset when selection is made”. Choose the Product Category.

Step 8: Click the options on subcategory data, choose Category check box under “Filtered by”

Step 9: By default “Allow select all” option is ON, now off for both the selection list.

Step 10: Preview the report. You can check the sub category shows only Bike information. If we choose some other value in category then it will show respective sub category results.

ByHariharan Rajendran

Cascading Parameter in SSRS

Cascading parameter means, one parameter values are based on another parameter value.

This article explains this concept in a very simple way with the below scenario.

Pre-Requisites:

  1. Main Dataset with 2 parameters
  2. Dataset for parameter 1 distinct values
  3. Dataset for parameter 2 distinct value with parameter 1 in where clause

Main Dataset:

Select EnglishProductCategoryName as ProductCategoryName, EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category and EnglishProductSubcategoryName=@SubCategory

Dataset 2 for Parameter 1 Values:

Select distinct EnglishProductCategoryName as ProductCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Dataset 3 for Parameter 2 values with Parameter 1 in where clause:

Select distinct EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category

Assign the available values for both parameters

Run the report.

Initially, it will show the first parameter with drop down values and the second parameter will not show any values.

As per the above scenario, we are passing the first parameter value to the second parameter.

We need to choose a value of the first parameter so that we can see the second parameter values.

Based on the first parameter value, second parameter values are changing.

1