Archive

Archive for the ‘MDX’ Category

SSRS Report Parameter change does not refresh default values of dependent parameters Using SSAS cube as Data Source

February 14, 2016 Leave a comment

I have already resolved this issue inmy previous blog with SQL server as data source .

Now, I tried to resolve this using SSAS cube as datasource.

Follow below Video  link to see, how to do things to get the default value of parameter to refresh.

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: Home, MDX, SSAS, SSRS Tags: , , ,

SSRS parameters measures filters

March 12, 2015 Leave a comment

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

Categories: MDX, SSAS, SSRS Tags: , ,

Creating Date Parameters in SSRS and pass it to MDX query

February 5, 2014 Leave a comment

Date Parameters in SSRS will open the calendar object, after selecting date it will pass to MDX as date format default in SSRS.

Let’s create one sample SSRS report with MDX.

Generate a report with birthdate as start and end date, based on these dates show employee names with gender.

Default MDX created by drag and drop in SSRS query designer for MDX as shown below:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Employee].[Birth Date].&[1955-05-07T00:00:00] : [Employee].[Birth Date].&[1985-05-07T00:00:00] ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

 1.DefaultMDX

If you see in above MDX the date is like its stored in the member 1955-05-07T00:00:00

 

Lets create parameters for this report.

Parameters created like in below screenshot:-

2.MDX-Parameters

 

Mapped the parameter in MDX . After modification MDX looks like

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( strtomember(@FromDate) : StrtoMember(@ToDate) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After completing wizard by clicking next à next it will generate report like below screenshot:-

3.DefaultReport

 

Date Parameters in above report is look like drop down not like usually we want as date calendar icon which should open as calendar and user can select date easily.

Now we need to modify MDX and parameters to provide the date calendar facility in the report.

Modify the MDX as below query:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( Strtomember(“[Employee].[Birth Date].&[“+@FromDate +”]”) : strtomember(“[Employee].[Birth Date].&[“+@ToDate+”]”) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After that please modify the parameters as shown in below screen shot:-

4.ParametersModified

 

If you run the report after changes it will throw error, modify the SSRS parameters also.

Create two more parameters which actually have the date data type.

@ BirthFromDate as shown in below screen shot

5.NewParameter

 

@BirthToDate as below screenshot:-

6.NewParameter2

 

So above created parameters will pass date as in the format to old parameters which are bond with the report

Format : 1946-06-13T00:00:00

Edit old FromDate Parameter and do as shown in below screenshot:-

7.OLD-Parameter-Modification

 

Expression pasted in above expression is

=cstr(Year(Parameters!BirthFromDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthFromDate.Value)<10,”0″+cstr(Month(Parameters!BirthFromDate.Value)),Month(Parameters!BirthFromDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthFromDate.Value)<10,”0″+cstr(Day(Parameters!BirthFromDate.Value)),Day(Parameters!BirthFromDate.Value)))+”T00:00:00”

Same expression should be pasted in default value of the parameter

Same procedure for Old parameter ToDate.

Onle change is the expression as given below:-

=cstr(Year(Parameters!BirthToDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthToDate.Value)<10,”0″+cstr(Month(Parameters!BirthToDate.Value)),Month(Parameters!BirthToDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthToDate.Value)<10,”0″+cstr(Day(Parameters!BirthToDate.Value)),Day(Parameters!BirthToDate.Value)))+”T00:00:00”

Both old parameters should be hidden.

Run the report, you will find the report like below screenshot:-

8.FinalReport

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: Home, MDX, SSAS, SSRS

MDX – String Functions

September 17, 2012 Leave a comment

String functions used mostly when one wants to write dynamic MDX.  Input parameters for the MDX used as string which converted into member or set using these string functions.

Please follow the below link for more details

MDX-String-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Set Functions

September 13, 2012 Leave a comment

Please follow the below link for more details

MDX-Set-Functions

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Time Functions

September 13, 2012 Leave a comment

Please follow the below link for more details

MDX-Time-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Navigation Functions

September 11, 2012 Leave a comment

Please follow the below link for more details

MDX-Navigation-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags: