Home > MDX, SSAS, SSRS > SSRS parameters measures filters

SSRS parameters measures filters

From MSDN I got this question  SSRS parameters measures filters 

This is the comment of user which he want to do.

For that I created 3 parameter
– the 1st parameter @Typeofmeasure lists the measures that I want to display. I have 2 values (Volume, Sales)
I created these values in specifying these values in the parameters properties

– The 2nd parameter @TypeOfAggregation lists the way that I want these measures to aggregate. I have 3 values ( Year To date, Month to date and Running 12 Month). I created these 3 values in specifying these values in the parameters properties too

– The 3rd parameter @Measures lists the measures that I want to display in my report.
So when user choose “Volume” in the 1rst parameter and “Year To date” for the 2nd parameter I want to have [Measures].[Volume YTD] in the 3rd parameter. Like a cascading parameter.

Before going to SSRS and MDX, please create the Calculated Measures in SSAS for Volume and Sales like Sales YTD , Sales MTD.

In my example I have two Measures and two calculated Measures

[Measures].[Sales Amount]

[Measures].[Order Quantity]

[Measures].[Sales Amount YTD]

[Measures].[Order Quantity YTD]

Lets come to SSRS report, create first parameter

Parameter 1 : – Typeofmeasure

Available values are as shown in below screen:-

Typeofmeasure

Parameter 2 : – TypeOfAggregation

Available values are as shown in below screen:-

TypeOfAggregation

Parameter 3 : – Measures

Available values are as shown in below screen:-

Measures

Measures parameter is cascading one, it has expression based on above two parameters

expression:

=“[Measures].[“+Parameters!Typeofmeasure.Value+ ” “+Parameters!TypeOfAggregation.Value+“]”

Lets create the main dataset for the report as shown in below screen.

MainDataset

MDX is like :

WITH MEMBER [Measures].[KPI]
as
strtomember(@Measures)
select  [Measures].[KPI] on 0,
[Order Date].[Calendar Year].[Calendar Year] on 1
from [Adventure Works DW2008R2]

Lets run the report

Case 1 Sales YTD

SalesYTD

Case 2 Quantity YTD

QuantityYTD

Note : Please keep the Common Name for the Measure in SSRS report so that  field Column will be fixed and it can be used in the SSRS report.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: MDX, SSAS, SSRS Tags: , ,
  1. No comments yet.
  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: