Home > SSRS > SSRS Report Parameter change does not refresh default values of dependent parameters

SSRS Report Parameter change does not refresh default values of dependent parameters

SSRS Report Parameter change does not refresh default values of dependent parameters

Let say we have a scenario where we want to implement the cascading parameter.

First Parameter value is use to load the second parameter default value and available values will show complete list and default value will check the checkboxes which are dependent on first parameter.

Let’s stimulate the requirement then we will see what the refresh issue is and try to resolve it.

Create SSRS report with Two Datasets.

First Dataset load the Initial of Names like Query Given below:-

Dataset Name: EmployeeInitial

 SELECT ‘A’ intital

UNION

SELECT ‘B’ intital

UNION

SELECT ‘C’ intital

Second Dataset will have all name with the initial as given below:-

Dataset Name: Employee

SELECT ‘A’ intital,’Annik’ FirstName

UNION

SELECT ‘A’ intital,’Andrew’ FirstName

UNION

SELECT ‘B’ intital,’Bailey’ FirstName

 

UNION

SELECT ‘B’ intital ,’Ben’ FirstName

UNION

SELECT ‘C’ intital,’Chris’ FirstName

UNION

SELECT ‘C’ intital,’Charles’ FirstName

 

Above second dataset is not been cascaded, we will do it as cascading, before that we try to generate report mockup.

FirstName salary
Andrew 55000
Annik 60000
Bailey 40000
Ben 45000
Charles 80000
Chris 70000

 

We want to show the employee and salary based on the parameter.

First Parameter name @EmployeeInitial and use the first dataset mentioned above data to fill the values in the parameter.

Second Dataset Name: Employee will be cascaded as mentioned below:-

SELECT * FROM

(

SELECT ‘A’ intital,’Annik’ FirstName

UNION

SELECT ‘A’ intital,’Andrew’ FirstName

UNION

SELECT ‘B’ intital,’Bailey’ FirstName

 

UNION

SELECT ‘B’ intital ,’Ben’ FirstName

UNION

SELECT ‘C’ intital,’Chris’ FirstName

UNION

SELECT ‘C’ intital,’Charles’ FirstName

 

) T

where T.intital =@EmployeeInitial

 

Create one more dataset Emp_full_list which will have all the data without filter by any parameter.

SELECT ‘A’ intital,’Annik’ FirstName

UNION

SELECT ‘A’ intital,’Andrew’ FirstName

UNION

SELECT ‘B’ intital,’Bailey’ FirstName

 

UNION

SELECT ‘B’ intital ,’Ben’ FirstName

UNION

SELECT ‘C’ intital,’Chris’ FirstName

UNION

SELECT ‘C’ intital,’Charles’ FirstName

 

 

Now we are creating second parameter EmployeeName

Avialable Values from dataset Emp_full_list

Deafult values from Dataset Employee

Below is the structure what we discuss above:-

SSRS Parameter Structure

Just Preview the report, so that we can see how data is refreshed in the SSRS parameters

FirstParameterSelection

 

Let’s select different value in first Parameter.

Value not Refreshed in Second Parameter

In above screen we can see after selected value B in Employee Initial parameter, its not refreshed the EmployeeName parameter. It’s still showing the previously loaded values.

This was the issue what we are talking, Default values not refreshed.

One thing to note here as available values are non dependent one it’s not affected so the values are not refreshed. So try to invalid the available list. So it will refresh the parameter values.

 

Update two datasets query as mentioned below:-

Dataset Emp_full_list:-

SELECT     @EmployeeInitial +’Annik’ AS keyvalue, ‘Annik’ AS FirstName

UNION

SELECT     @EmployeeInitial +’Andrew’ AS keyvalue, ‘Andrew’ AS FirstName

UNION

SELECT     @EmployeeInitial +’Bailey’ AS keyvalue, ‘Bailey’ AS FirstName

UNION

SELECT     @EmployeeInitial +’Ben’ AS keyvalue, ‘Ben’ AS FirstName

UNION

SELECT     @EmployeeInitial +’Chris’ AS keyvalue, ‘Chris’ AS FirstName

UNION

SELECT     @EmployeeInitial +’Charles’ AS keyvalue, ‘Charles’ AS FirstName

 

Dataset Employee:-

SELECT * FROM

(

SELECT ‘A’ intital, @EmployeeInitial +’Annik’ AS keyvalue,’Annik’ FirstName

UNION

SELECT ‘A’ intital, @EmployeeInitial +’Andrew’ AS keyvalue,’Andrew’ FirstName

UNION

SELECT ‘B’ intital, @EmployeeInitial +’Bailey’ AS keyvalue,’Bailey’ FirstName

 

UNION

SELECT ‘B’ intital ,@EmployeeInitial +’Ben’ AS keyvalue,’Ben’ FirstName

UNION

SELECT ‘C’ intital,@EmployeeInitial +’Chris’ AS keyvalue,’Chris’ FirstName

UNION

SELECT ‘C’ intital,@EmployeeInitial +’Charles’ AS keyvalue,’Charles’ FirstName

 

) T

where T.intital =@EmployeeInitial

 

We added one column Keyvalue which is nothing but concatenation of First Parameter, so whenever we change in first column selection it will refresh both the datasets, and it will reflect in Available list and default list of second parameter.

Note: as we are modifying the value field with Keyvalue column, so in Main query which will use these parameters to generate the report data we should remove the concatenation parameter value from the value field.

 After the above changes, just edit the second parameter as below for available values:-

SecondParameterAvilableValues

Do for default values as given below:-

SecondParameterDefaultValues

Preview the Report

 PreviewFirstParameterSelection

After selection changed on first parameter

Value Refreshed in Second Parameter

Create main dataset with below query which will use to show the data into SSRS report

select * from

(

SELECT ‘Annik’ FirstName, 60000 salary

UNION

SELECT ‘Andrew’ FirstName, 55000 salary

UNION

SELECT ‘Bailey’ FirstName, 40000 salary

UNION

SELECT ‘Ben’ FirstName, 45000 salary

UNION

SELECT ‘Chris’ FirstName, 70000 salary

UNION

SELECT ‘Charles’ FirstName, 80000 salary

 

)T

where t.FirstName in (@EmployeeName)

After Running the report we will get the below report

NoDatainReport

We have data as we can see in the Main Dataset, but still we not getting any rows, in above there is a note in bold where we mentioned we should replace the concatenated string, or we can just add that concateinated string infront of FirstName in LHS(left hand side) of the where condition as shown below:-

select * from

(

SELECT ‘Annik’ FirstName, 60000 salary

UNION

SELECT ‘Andrew’ FirstName, 55000 salary

UNION

SELECT ‘Bailey’ FirstName, 40000 salary

UNION

SELECT ‘Ben’ FirstName, 45000 salary

UNION

SELECT ‘Chris’ FirstName, 70000 salary

UNION

SELECT ‘Charles’ FirstName, 80000 salary

 

)T

where @EmployeeInitial+t.FirstName in (@EmployeeName)

 

Now when we run the report we can see the data in the report as shown below:-

DatainReport

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSRS Tags:
  1. February 15, 2015 at 3:09 am

    Reblogged this on Dinesh Ram Kali..

  2. damodar
    February 10, 2016 at 7:44 pm

    Thanks for the article! I am facing the same refresh issue. we are using SSAS and MDX for creating the SSRS report …

    Can you please explain the same approach if we are using SSAS Cube as a backend and we need to write MDX queries instead of the SQL queries in your article.

    We are facing issue in replace the concatenated string in the main dataset. the issue is that object to string conversion error

    • February 11, 2016 at 8:24 am

      When you doing using MDX query, your value comes after concatenation should be a member in cube. So one thing you can do create calculated member concatenation of two member values in cube . use same calculate member to reset the parameters values.

      I will to create one more post on this using SSAS cube MDX query in the report, But i need some time to do this.

      • Damodar
        February 11, 2016 at 10:49 am

        Thank you so much for valuable suggestion and your response in such a short time. please post SSAS with MDX as soon as possible.

    • February 14, 2016 at 8:38 pm

      Hello Damodar,

      Please look into my YouTube video on this. Link below:-

      Any issue in understanding, please let me know.

      Thanks
      Prasad

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: