Tag Archives: Cascade Parameter

ByHariharan Rajendran

Cascading Parameter in SSRS – Different Approachs

I saw a query raised by a user in one of the SQL forum. He has explained his query with a scenario.

Scenario: Need to create a simple report with 3 parameters, those parameters are “FromDate”, “ToDate” and “Type”. Two parameters (“FromDate” & “ToDate”) should react when I change the Type parameter value.

The possible values in “Type” field is “Yes” and “No”. If the end user selects “Yes” then “FromDate” and “ToDate” should show one year back from today. i.e. 2016-01-10  to 2017-01-11

If the value is “No” the date should be one month back from today. i.e. 2016-12-10 to 2017-01-11.

By seeing the above requirement, I felt cascading parameter option would be the solution for above requirement but it will not give 100% result because once the value is entered or set on parameter textbox, it won’t be changed until we refresh a page or open the report again. This could be unavoidable.

I thought to document this scenario with possible solutions.

Solution 1:

As usual, create a main query dataset with all the above mentioned three parameters. It could be like below,

Dataset 1

SELECT <column names> FORM <tablename>

WHERE FromDate=@FromDate and ToDate=@ToDate and Type=@Type

The above script in the main dataset will create 3 parameters in parameters folder.

Dataset 2

Create another dataset with following Script,

SELECT

      CASE WHEN Type=’Yes’ then  DATEADD (d,-365,GETDATE()) ELSE DATEADD (d,-30 ,GETDATE()) END as FromDate,

       CASE WHEN Type=’Yes’ then GETDATE() ELSE GETDATE() END as ToDate

  FROM tablename

  Where Type=@Type

Dataset 3

Create another dataset for Type value “Yes” and “No”.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value from dataset 2 and do the same for “ToDate”

Report_Dataset

Run the report, you should see the result as like below.

ReportParameter2

Solution 2:

Follow the same steps as you did with solution 1.

Create Dataset1 & Dataset 3.No need to create dataset 2 as we are going to use an expression in parameter itself.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value with “Specify values” option. Click “Add” and write the below expression.

=IIF(Parameters!Type.Value=”Yes”,DateAdd(“d”,-365,Today), DateAdd(“d”,-30,Today))

For ToDate Parameter,

=IIF(Parameters!Type.Value=”Yes”,Today,Today)

ReportParameter

Report Parameter1

 

1