Feeds:
Posts
Comments

Archive for the ‘MDX’ Category

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 »

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 »

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 »

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 »

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 »

 

 

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 »

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 »

Older Posts »

%d bloggers like this: