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

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


I had a requirement recently where the customer wanted a measure to be formatted differently, based on its value.

In this case, it was two numeric measures with 2 decimal point precision, and for cases where there were no decimal digits, it was supposed to look like an integer.

I looked for a MOD function in VBA or SSAS that can be used here, but came up empty ūüė¶

I used the SCOPE statement to override the default formatting

SCOPE({[Measures].[Sales], [Measures].[Cost]});
FORMAT_STRING(This)= IIF(Round([Measures].CurrentMember.VALUE, 2)=[Measures].CurrentMember.VALUE, “#,###;;0;”, “#,###.00;;0;”);

For the measures in question, the SCOPE statement compares its ROUNDed value to its actual value. If these are equal, that means that the number is a whole number, and has to be formatted without decimal places shown. Hence the FORMAT_STRING expression.

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. 



I’ve been working with the other components of BI (SSIS, SSAS, SSRS, PPS) for the past 6 weeks and haven’t had much chance to work on the MOSS component.

Here is something I had to struggle with and hope you guys find it useful:
In the cube, one of the most common tasks is to do data analysis/comparison for different time periods and almost all the time, the reference point is NOW.
How did our numbers compare to a period in the past w.r.t. today/this month/this quarter, etc

Here are some MDX examples to solve 90% of those worries. Might come in handy if you want to create a named set based on such:

1. Current Month MDX
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”))

2. Current Quarter MDX
The VBA function format() does not have a provision for returning the current quarter, so here is what you do – as above, you get to the current month and lookup its parent to get to the quarter. Now, the caveat is that your date hierarchy should be defined as such (Month to be a child of Quarter). In my case, it is Year -> Quarter -> Month -> Date
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”).Parent)

It saved my day and hope it helps you.
In case you know of another (better or worse) way, let me know.


July 2018
« Mar    

Blog Stats

  • 44,396 hits