Home > Home > Creation of Cascading Parameters in SSRS report using SSAS cube as a Data Source

Creation of Cascading Parameters in SSRS report using SSAS cube as a Data Source

For Basics MDX please refer here.

Strtomember() function is very useful while writing dynamic (OR parameters) MDX.

Create Two Parameters in SSRS parameters Section

Business Requirement: There are big list of employees, so loading into one parameter with employee name will hamper the performance of the report.

So we can create one parameter which will load A-Z values and based on selection of first parameter second parameter will load the employee name.

Like if first parameter is selected for a value “B” then second parameter will load the employee names starting from “B”.

 

Just create the named column in the DSV of cube as expression:

LEFT(<Name Column>,1)

Now in Employee dimension will look like below screenshot:-

1

 

Let’s create SSRS report using SSAS cube

Just Create SSRS report using wizard, it will create the report without any parameters.

After creation you will see it ill give all the records without filtering.

Now create two parameters as shown in below screen shot:-

2

 

In Datasets pane please create dataset with name StartLetterDS as shown below:-

3

Go to Parameters and edit the parameter StartLetter and set as below snapshot:-

4

 

After that please create one more dataset named “EMP_DS”

Write MDX as below with hardcode value as StartLetter value as “a”, later on it will replaced by parameter.

SELECT NON EMPTY { } ON COLUMNS,  { ([Employee].[Start Letter].&[a] * [Employee].[Empname].[Empname].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [DSV_AD] CELL PROPERTIES VALUE

 

5

Now Replace [Employee].[Start Letter].&[a] with parameter and use strtomember function.

strtomember(“[Employee].[Start Letter].&[“+@StartLetter+”]”)

After change MDX and applying strtomember function , when we run it will ask for parameter is not defined, as we know the parameter is already created in the SSRS. However we need to map the SSRS and SSAS parameter with same name .

Click the icon as shown in below screenshot

 

6

After click the icon please provide the exact name of the parameter defined in SSRS and in MDX and give default value as shown in below screen shot

 

7

After creation of EmpDS dataset , edit the EmpName Parameter and assign the available values as shown in below screenshot:-

 

8

After creation of EmpDS dataset , edit the EmpName Parameter and assign the available values as shown in below screenshot:-

 

9

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home
  1. March 15, 2016 at 4:19 pm

    Prasad,
    could you modify your post to display screenshots correctly?

    • March 16, 2016 at 8:25 am

      Hi Egor,

      Screenshots are updated, any issue let me know.

      Thanks
      Prasad

  2. March 15, 2016 at 4:23 pm

    Yes, I found screenshots are not showing due to this screenshots are hosted on my website earlier. I will post the screenshots again in 1 day.

  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: