Home > MDX > MDX – Child Aware Calculations

MDX – Child Aware Calculations

Any business domain reporting solutions has a requirement to analysis its measures depending on its KPI thresholds. It is very common to have business entities hierarchy involvement in this analysis.
Here discussing one possible common mistake and easy approach to solve this using a simple MDX calculation using AdventureWorks Cube. Scenario is to get the products category/sub-category threshold message depending on its product sales to a threshold level.

WITH 
// Get the Hierarchy Level of the Current member
MEMBER [Measures].[ProductLevel] AS
[Product].[Product Categories].CurrentMember.Level.Ordinal

MEMBER [Measures].[KPIStatusMessage] AS
// KPIStatusMessage message depending on threshold level value
IIF( [Measures].[Reseller Sales Amount] < 15000 , "Failure" , "Success" )

MEMBER [Measures].[KPIMissingTargetChildsCount] AS
// KPIMissingTargetChildsCount child members missing Threshold value
COUNT(
    FILTER(       
       nonempty(
       
               DESCENDANTS ([Product].[Product Categories].CurrentMember ,      [Product].[Product Categories].[Product])
             , [Measures].[Reseller Sales Amount]
                )                
           , [Measures].[Reseller Sales Amount] < 15000
          )
     )

MEMBER [Measures].[KPIStatusMessageFromChilds] AS
// KPIStatusMessageFromChilds message depending on childrens threshold level value
IIF(
 
  COUNT(
    FILTER(
       
       nonempty(
       
               DESCENDANTS ([Product].[Product Categories].CurrentMember , [Product].[Product Categories].[Product])
             , [Measures].[Reseller Sales Amount]
                )
                
           , [Measures].[Reseller Sales Amount] < 15000
           )
        ) 
        > 0 
        
        , "Failure" 
        , "Success" 
     )
     , fore_color = iif([Measures].[KPIMissingTargetChildsCount] > 1 , 255 ,0)

SELECT {[Measures].[Reseller Sales Amount]
      , [Measures].[ProductLevel]
      , [Measures].[KPIStatusMessage]
      , [Measures].[KPIMissingTargetChildsCount]
      , [Measures].[KPIStatusMessageFromChilds]} ON COLUMNS
, nonempty({[Product].[Product Categories].Members - [Product].[Product Categories].[All] }
          ,[Measures].[Reseller Sales Amount]
          ) ON ROWS
FROM 

( SELECT {
         [Product].[Product Categories].[Category].&[4] -- Accessories
         
         } ON COLUMNS
  FROM [Adventure Works]
)
CELL PROPERTIES 
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR],[BACK_COLOR]

1

Advertisements
Categories: MDX
  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: