How to use CASE function in MDX to retrieve a specific value based on the report parameter?

I have an MDX report containing 7 parameters. The first 5 parameters are cascaded.  For example Company > BusinessUnit > ReportingUnit > SubUnit > Project.

I have a requirement to set a target level based on the combination of the parameter values selected. For example, when the Company parameter value gets selected and the BusinesUnit = All and ReportingUnit = All and SubUnit = All and Project = All, set the target level to the Company parameter value. When the Company parameter value gets selected and the BusinesUnit <> All and ReportingUnit = All and SubUnit = All and Project = All, set the target level to the BusinessUnit parameter value. When the Company parameter value gets selected and the BusinesUnit <> All and ReportingUnit <> All and SubUnit = All and Project = All, set the target level to the ReportingUnit parameter value and so on. To achieve this, I have used CASE function in MDX.

The reason it has to be done this way is that the target value is not rolled up i.e. it is set at a specific level within the organisation chart.

My Query Parameters set up on SSRS is below:

My MDX query using CASE function is below:

 WITH
MEMBER [Measures].[AllClassValue] AS
 CASE
  WHEN  STRTOMEMBER(@Company).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION = “All” 
  THEN  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@Company).Item(0).MEMBER_CAPTION + “]”   

  WHEN STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION = “All” 
  THEN  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION + “]”    

  WHEN  STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION = “All”
    AND STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION = “All” 
  THEN  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION + “]”    

  WHEN  STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION = “All” 
  THEN  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION + “]”  

  WHEN  STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@ReportingUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@SubUnit).Item(0).MEMBER_CAPTION <> “All”
    AND STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION <> “All” 
  THEN  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@Project).Item(0).MEMBER_CAPTION + “]”  

 END

SET [TargetLevel] AS STRTOMEMBER( [Measures].[AllClassValue] )

SELECT  {  [Measures].[Non Empty Injury Count]
 , [Measures].[Last 12 Months Injury Frequency Rate]
 , [Measures].[Consolidated Injury Count]
 , [Measures].[Last 12 Months Consolidated Injury Frequency Rate]
 , [Measures].[Fiscal Current YTD Target Value]
             } ON COLUMNS

                ,   LastPeriods(13, STRTOMEMBER(@DateMonth, CONSTRAINED) )
  * [Injury Types].[Injury Type Description].&[Medical Treatment Injury]
  * [Date].[Fiscal].[Month]
                 * [TargetLevel]
                ON ROWS
FROM ( SELECT ( STRTOMEMBER(@Company, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@BusinessUnit, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@ReportingUnit, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@SubUnit, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@Project, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT [Not Cancelled Event Status] ON COLUMNS
FROM [Cube]) )))) )

The STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION returns the name of the dimension member. For example if the member value is [Class Values].[Level 2].&[Cube Consulting]&[WA], the name returned will be WA.

The  “[All Class Values].[Class Value].&[” +  STRTOMEMBER(@BusinessUnit).Item(0).MEMBER_CAPTION + “]”     is used to convert it to a differenct dimension hiearchy, in this example, [All Class Values].[Class Value].&[WA].

In summary,

from [Class Values].[Level 2].&[Cube Consulting]&[WA] to  [All Class Values].[Class Value].&[WA]

 

Read Full Post »

MDX – how to do “OR” condition ?

I have a report to show number of incidents that have a risk final outcome or consequence of insignificant, minor OR moderate recorded against them.

It is very straight forward to implement this in SQL, for example:

WHERE ( FinalOutcome  IN (‘Moderate’,‘Minor’,‘Insignificant’) OR  FinalConsequence IN (‘Moderate’,‘Minor’,‘Insignificant’) )

To implement this in MDX:

WHERE

{ { [Final Actual Outcome].[Risk Consequence].&[Insignificant], [Final Actual Outcome].[Risk Consequence].&[Minor], [Final Actual Outcome].[Risk Consequence].&[Moderate] } * [Final Potential Consequence].[Risk Consequence].[All] }

+

 { [Final Actual Outcome].[Risk Consequence].[All] * { [Final Potential Consequence].[Risk Consequence].&[Insignificant], [Final Potential Consequence].[Risk Consequence].&[Minor], [Final Potential Consequence].[Risk Consequence].&[Moderate] } }

 

 

Read Full Post »

MDX – how to filter the query where a certain dimension member value is equal and another value is not equal to?

T-SQL query as an example:

SELECT  *

FROM c_EventInvestigations

WHERE EventStatus <> ‘Cancelled’

AND  InvestigationStartDate IS NOT NULL

AND InvestigationClosedOutDate IS NULL

My first MDX query for the above was:

SELECT

{ [Measures].[Open Investigation 1 Month]

, [Measures].[Open Investigation 1-3 Month]

 , [Measures].[Open Investigation 3-6 Month]

, [Measures].[Open Investigation 6 Month]

 } ON COLUMNS

FROM ( SELECT [Not Cancelled Event Status] ON COLUMNS

FROM ( SELECTFILTER( [Investigation Start Date].[Date].MEMBERS, [Investigation Start Date].[Date].CurrentMember.Name <> “1900-01-01” ) ON COLUMNS

FROM ( SELECT FILTER( [Investigation Closed Out Date].[Date].MEMBERS, [Investigation Closed Out Date].[Date].CurrentMember.Name = “1900-01-01” ) ON COLUMNS

FROM [Cube] ) ) )

This query returns no values at all, which is incorrect. It should have returned 13 Open Investigations 1-3 Months. Note that I have used the filter function.

If I change the way the filter is performed to below, the query returns with the correct value. Amazingly, I found this out by creating a new data set in SSRS then drag and drop the fields and measures required then had a look at the mdx code produced.

SELECT

{ [Measures].[Open Investigation 1 Month]

, [Measures].[Open Investigation 1-3 Month]

 , [Measures].[Open Investigation 3-6 Month]

 , [Measures].[Open Investigation 6 Month]

 } ON COLUMNS

FROM ( SELECT [Not Cancelled Event Status] ON COLUMNS

FROM  ( SELECT ( { [Investigation Closed Out Date].[Date].&[1900-01-01T00:00:00] } ) ONCOLUMNS

FROM ( SELECT ( -{ [Investigation Start Date].[Date].&[1900-01-01T00:00:00] } ) ONCOLUMNS

FROM [Cube] ) ) )

Note the usage of the minus sign located in front of the dimension member value. 🙂

Read Full Post »

Useful MDX string functions to get texts containing or beginning with a certain word

To obtain texts containing a certain word: InStr( string_expression, certain_word)

For example: to obtain event types containing the word “major”:

WITH SET [Major Event Types] AS 

FILTER( [Event Types].[Event Type].MEMBERS, InStr( [Event Types].[Event Type].CurrentMember.Name, “Major” ) > 0)

To obtain texts beginning with a certain word: Left(string_expression, number_of_characters_of_the_word). Similar rule applies to get a string of a specified number of characters from the right side (end) of a specified string with RIGHT() mdx function.

WITH SET [Major Event Types] AS

FILTER( [Event Types].[Event Type].MEMBERS, Left( [Event Types].[Event Type].CurrentMember.Name, 5) = “Major” )

Read Full Post »