You are currently browsing the tag archive for the ‘MDX’ tag.

It is pretty straightforward to get measure values for members in a hierarchy. In Adventure Works, say you wanted to get [Internet Sales Amount] for all products by color, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Color].[Color].MEMBERS ON 1
FROM [Adventure Works]

Sales By Color

and, to get [Internet Sales Amount] for all products by class, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Class].[Class].MEMBERS ON 1
FROM [Adventure Works]

 Sales By Class

It gets trickier if you want to get the output to show both results in the same query

Sales By Color And Class

You can UNION the two result sets by creating a set of members from the 2 hierarchies, using their [All] members respectively. Something like

WITH MEMBER [Measures].[Attribute] AS
IIF([Product].[Color].CurrentMember IS [Product].[Color].[All]
, [Product].[Class].CurrentMember.NAME
, [Product].[Color].CurrentMember.NAME)

SELECT {[Measures].[Attribute], [Measures].[Internet Sales Amount]} ON 0,
([Product].[Class].[Class].MEMBERS, [Product].[Color].[All]),
([Product].[Class].[All], [Product].[Color].[Color].MEMBERS )} ON 1
FROM [Adventure Works]

Union Sales By Color And Class

A lot of times the MDX query needs to look at current date/today date/system date as the reference member. There is no function equivalent to TSQL getdate(), but you can use the following piece of cide snippet to solve the purpose:

StrToMember(“[Date].[Date].[” + Format(now(), “yyyyMMdd”) + “]”)

As an example, if you want to see the Sales for the last 7 days from the system date:

 [Measures].[Sales]  ON COLUMNS,
 LastPeriods(7,  StrToMember(“[Date].[Date].[” + Format(now(), “dd-MM-yyyy”) + “]”) )  ON ROWS
FROM [Cube]

As one of the tasks on hand, I had to figure out the backlog which by definition is a running total of all ‘open’ incidents at the end of a day. The dimension table being used has all the incident details and related attributes such as status, priority, category, etc 

So, I created a caculated measure, named [Incident Backlog Count], based on the [Incident Count] measure in the measure group, as: 

IIF ([date hierarchy].CurrentMember IS NULL,
 IIF(IsEmpty(Measures.[Incident Count]),
  IIF([date hierarchy].Parent.FirstChild IS [date hierarchy].CurrentMember,
   (Measures.[Incident Backlog Count], [date hierarchy].Parent.PrevMember),
   (Measures.[Incident Backlog Count], [date hierarchy].PrevMember)),
  IIF([date hierarchy].LEVEL IS [date hierarchy].[Date],
   (Measures.[Incident Count]),  IIF(IsEmpty((Measures.[Incident Backlog Count],[Date Hierarchy].LastChild)),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild.PrevMember),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild))

The recursion worked well and the MDX seemed to simulate the LastNonEmptyChild function, except that at the leaf level, all the values were getting populated – even for the empty members !   

For the life of me, I could not figure out the reason why this was happening, until I went in the Calculated Member Form Editor for the calculated measure and set the Additional Property Non Empty Behavior to the default measure [Incident Count] 

Here’s what Microsoft has to say about the Calculated Member Form Editor 

If the Non Empty Behavior property is blank, the calculated member must be evaluated repeatedly to determine if a member is empty. If the Non Empty Behavior property contains the name of a measure, the calculated member is treated as empty if the specified measure is empty. 




August 2017
« Mar    

Blog Stats

  • 42,577 hits