PROPERTIES() MDX – if you have a composite key defined in your dimension and you want to get its key…

For example, a site may have more than one camp and a camp may have more than one room. Hence the hierarchy of this dimension would be Site > Camp > Room.

An example of a Room A01 member in MDX is  [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01]. As you can see in here, 5 is the SiteID and 16 is the CampID. A01 is the Room Name.

To get the SiteID and CampID from the Room member, use the PROPERTIES function.

Example code:

WITH

 MEMBER [Measures].[RoomSiteMemberKey] AS [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01].Properties(‘Key0’)

MEMBER [Measures].[RoomCampMemberKey] AS [Rooms].[Hierarchy].[Room].&[5]&[16]&[A01].Properties(‘Key1’)

SELECT { [Measures].[RoomSiteMemberKey], [Measures].[RoomCampMemberKey] } ON 0

FROM Cube

The above code will return the RoomSiteMemberKey = 5 and RoomCampMemberKey = 16.

Read Full Post »

Dynamic column grouping in matrix SSRS report

I have  a report requirement to display a list of incident statistics based on the organisation structure. The org chart has 8 levels. When Level 1 is selected, display the stats for Level 2 (i.e. one level below) etc.

The MDX function to get the next level down is DESCENDANTS() function, for example, DESCENDANTS(STRTOMEMBER(@ClassValue, CONSTRAINED), 1).

To get the level of the next level in MDX.

MEMBER [Measures].[NextClassValueLevel] AS STRTOMEMBER(@ClassValue, CONSTRAINED).LEVEL.ORDINAL  + 1

This level is used to group the matrix column dynamically. Its column cell  and column group grouping is set to:

=Fields(“Level_” & Fields!NextClassValueLevel.Value).Value

 

Example code:

WITH
MEMBER [Measures].[Last 12 Months TRIFR] AS SUM( [Recordable Injuries], [Measures].[Last 12 Months Injury Frequency Rate] )
MEMBER [Measures].[NextClassValueLevel] AS STRTOMEMBER(@ClassValue, CONSTRAINED).LEVEL.ORDINAL  + 1

SELECT NON EMPTY  {  [Measures].[Last 12 Months TRIFR]
                                      , [Measures].[Last 12 Months Lost Days Injuries FR]
                                      , [Measures].[Last 12 Months Full Shift Lost Severity Rate]
                                      , [Measures].[NextClassValueLevel]
                                   } ON COLUMNS
                   , DESCENDANTS(STRTOMEMBER(@ClassValue, CONSTRAINED), 1)
                                     ON ROWS
FROM ( SELECT STRTOMEMBER(@ClassValue, CONSTRAINED) ON COLUMNS
FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled] }  ON COLUMNS
FROM [Cube] ) )
WHERE  ( STRTOMEMBER(@DateMonth) )

 

 

 

Read Full Post »

How to count the number of injuries rolling 6 months from the date selected in MDX?

Example code is shown below:

WITH
SET [Last6Months] AS LastPeriods(6, STRTOMEMBER(@DateDate, CONSTRAINED).Parent)

SELECT NON EMPTY  [Measures].[Events Count] ON COLUMNS    
            , NON EMPTY   {  OpeningPeriod( [Date].[Monthly].[Date], [Last6Months].Item(0).FirstChild )
                          :   StrToMember(@DateDate, CONSTRAINED) } 
                         *  [Event Status].[Event Status].[Event Status]
ON ROWS
FROM ( SELECT ( STRTOMEMBER(@ClassValue, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT -{ [Event Status].[Event Status].&[Cancelled] } ON COLUMNS
FROM [Cube] ) )

 

Read Full Post »

Using Case Statement on the report parameters in MDX report

I have report with an injury type parameter of LTI, MTI, RWI and TRI. TRI is LTI, MTI and RWI. When TRI is selected, set it to grab from [Recordable Injuries] set, else grab it from the injury type selected. To do a case statement in MDX for this requirement:

SELECT  {   [Measures].[Last 12 Months Injury Frequency Rate]
                , [Measures].[Last 3 Months Injury Frequency Rate]
             } ON COLUMNS
       
              , LastPeriods(12, STRTOMEMBER(@DateDate, CONSTRAINED ).Parent )
                 ON ROWS
 FROM ( SELECT  STRTOMEMBER(@ClassValue, CONSTRAINED)  ON COLUMNS
 FROM ( SELECT CASE WHEN InStr(STRTOMEMBER(@InjuryType, CONSTRAINED).Name,”TRI”)>0 THEN [Recordable Injuries]
            ELSE STRTOMEMBER(@InjuryType, CONSTRAINED)
                           END ON COLUMNS
 FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled] } ON COLUMNS
 FROM [Cube] ) ) )

Read Full Post »

MDX Report – case study – with a fair bit of challenges.

I have an MDX report to display a list of Health & Safety projects and these projects have not had their Project Risk Assessment prepared or their Project Risk Assessment have not been updated in the previous 3 months. The report has @DateYear and @DateMonth parameters.

As an example, I have the following projects (i.e. class values) for March 2012 report. It lists events till March 2012.

There are 4 distinct projects and only 5B701 project has had their project risk assessment done on 24 Nov 2011. Hence the last review date for this project is 24 Nov 2011 and it should be displayed for the March 2012 report because Nov 2011 is 4 months away.

The other 3 projects have not had any event raised as Health & Safety Risk Assessment event type, therefore these projects have not have their Project Risk Assessment prepared yet and their last review date should be blank.

My final report for March 2012  should look like below:

However, when the report is run for Jan 2012, 5B701 project should not be displayed because its last review date is 2 months away (see below):

Several attempts were made without having to modify the data warehouse and cube structure by using UNION() function but could not achieve the above. 

My source view to populate the data into the data warehouse was modified to add a new column called LastRiskAssessmentReviewDate. This column identifies if a project has had their risk review done and if so when it was done. So my dataset looks like below:

I then populated this new field into fact Events table in the data warehouse and cube and if the field is null set it to 1900-01-01.  My MDX code should return with the following dataset:

SELECT NON EMPTY  { [Measures].[Events Count] } ON COLUMNS

 ,NON EMPTY [Class Values].[Class Value].[Class Value]
                                       * [Risk Assessment Review Date].[Date].[Date]
                               ON ROWS
FROM ( SELECT { NULL : STRTOMEMBER(@DateMonth, CONSTRAINED) } ON COLUMNS
FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled], [Event Status].[Event Status].&[Closed] } ON COLUMNS
FROM [Cube]  ) )
WHERE [Event Types].[Event Type Category].&[Health & Safety]

Now we need to display distinct projects on the report. Notice that 5B701 has 2 dates hence it is necessary to retrieve the latest date, i.e. 24 Nov 2011.  Afterwards, we need to set the date to blank when it is 1900-01-01.   This requirement can be met by using a row group (i.e. group the rows by classvalue) on the tablix in SSRS and using the expression below to get the max date and to set it to blank if it is of 1900 year.

=IIF(Year(CDate(Max(Fields!Date.Value))) = 1900, “”, Format(CDate(Max(Fields!Date.Value)), “dd/MM/yyyy”))

It is also required to hide projects if its last review date is less than three months away from when the report is run. This can be achieved by setting a filter on the Row Group Properties. Set the filter using the following expression

CDate(Max(Fields!Date.Value)) < CDate(Parameters!FirstDayOfPrevious3Months.Value)

where the FirstDayOfPrevious3Months value is obtained from below:

WITH SET [FirstDateOfLast3Months] AS OpeningPeriod([Date].[Monthly].[Date], ClosingPeriod([Date].[Monthly].[Month], STRTOMEMBER(@DateMonth).Lag(3)) )

SELECT  {} ON COLUMNS
         ,  [FirstDateOfLast3Months] ON ROWS
FROM  Cube

Read Full Post »

How to get an absolute value (i.e. abs() function in SQL) in MDX?

 

 

The answer would be to use ABS() function. It is not an MDX function but is supported by the VB function library that is included ith Analsysis Servcies and linked by default.

The following code is to get the day difference between the due date of the actions and the last day of the month. If the value is negative then make it positive by using the ABS() function.

WITH
SET [LastDayOfMonth] AS CLOSINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(@DateMonth, CONSTRAINED)  )
MEMBER [Measures].[DateDiff] AS ‘ABS(DATEDIFF(“d”, [Actions].[Due Date].CurrentMember.Name, [LastDayOfMonth].Item(0).MemberValue ) )’

 

 

Read Full Post »

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 »