Archive for the ‘Functions’ Category

How to hide Calculated Members in MDX SSAS



With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.

For example:

Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])

This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time.

Here is how I did this:

Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Semester] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Quarter] or
[Date].[Calendar].level is [Date].[Calendar].[Month]) then
aggregate(ytd([Date].[Calendar].currentmember),[Internet Sales Amount])
else “Use Date Hierarchy”


Now the user will see the message “Use Date Hierarchy”  if they are not at a level in the correct Hierarchy. The only downside to this is the grand total shows the message instead of the total.


If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.


How do I?

 have seen developers searching for a last minute MDX query / script to create a calculated member on the cube. Many start in an organized fashion with a start from some simple MDX tutorial, and learning tuples, namedsets, axis, MDX Select Query Syntax etc… But still after that, many queries remain unsolved, and the real answer comes after a hands-on experience of some real-time project when one really starts applying functions, tweaking or nesting queries and parameters.
Any material found in MDX should be treasured. By far I have just seen 3-4 books on SSAS flavor of MDX. Below are the links to code-snippets which are FAQ’s or used frequently while either querying the cube or creating calculated members on the cube.


Hope this helps all the last minute MDX requirements of those developers who don’t have time to spend in learning all the syntax, structure and theory behing MDX syntax just to solve a little query or requirement on a cube in production or last-minute patch.



Download MDX Tutorial – Code Snippets of Various FAQ Queries (PDF)

Download MDX Tutorial – Code Snippets of Error Handling (PDF)

Download MDX Tutorial – Cope Snippets SCOPE statement (PDF)

    1. MDX – How do you get Last month in the time dimension
    1. MDX – Query example that returns list of months from start of year up to specified month
    1. MDX – Query to show the first day of the last date month in the cube
    1. How in the report can I order date dimension members in descending order?
    1. MDX – query to get the last Month loaded into a cube
    1. MDX – Query to get the first month of the last year loaded into a cube
    1. MDX – How do you write query that uses execution date/time ( NOW() ) as a parameter?
    1. MDX – Query to get latest months and previous years same months data
    1. MDX – Query to show year level data for all years except last one, and month level data for last year
    1. MDX – How to create calculated member for AVG sales over last 3 years based on NOW()?
    1. MDX – How do I calculate sales for 12 Month to date?
    1. MDX – How can I get Last (Previous) Year to Date (YTD) values?
    1. MDX – Query to get count of months with sales amount > 0 in defined period

MDX – How do you calculate monthly average of one year, optionally including empty months?

  1. MDX – How do you calculate monthly average of one year, optionally including empty months?

What are the popular MDX functions ?

There are many MDX functions. The exact number depends on how you count (are methods and properties are counted, what about operators, are polymorphic functions counted once or as many times are there are overloads, what version of the Analysis Services we are talking about etc), but around 150 is probably right. Of course, not all of them are equally useful. Some seem to pop up in almost every MDX expression (CurrentMember for example), while others are so rarely used, that nobody even know they exist (LinRegR2 anyone ?). I always wanted to find out which functions people really use, and which ones we should not have bothered to add. So I decided to mine microsoft.public.sqlserver.olap newsgroup for answers. First, I thought I would use Google Web API, and even wrote a small C# program which hooked the output of DISCOVER_FUNCTIONS schema rowset with doGoogleSearch SOAP method. However, it turned out, that doGoogleSearch doesn’t work with “group:<newsgroupname>” query term, so I had to send the search queries manually. For each MDX function I checked with Google how many different threads in microsoft.public.sqlserver.olap newsgroup was it mentioned. Note – this is not the number of times it was mentioned, but number of distinct threads. I collected results for some 108 MDX functions, but data required some more filtering/cleaninsing:

  • Some of the MDX function names are also common English words, like Except, Order, Count, Lead etc. Those functions are excluded from statistics.
  • Some other MDX function names are English words commonly used in OLAP terminology, like Filter, Aggregate, Sum, Members, Hierarchy etc. Those functions are excluded from statistics as well.
  • Some MDX functions names are concatenation of two words for similar concepts in Analysis Services, but don’t have space, like VisualTotals, DistinctCount, DefaultMember. Such functions are not excluded from statistics, because cursory look over the threads revealed that the discussion was around the functions.
  • Still there were many MDX functions which also had independent meaning in English and OLAP, like Descendants, Ancestor, Head, Min, Rank etc. I decided to include them (otherwise I would only be counting functions with names like DrilldownMemberBottom – surely cannot be confused with any English word, but how useful is it). The numbers for these functions are probably overrepresented.
  • The frequency of postings into newsgroup doesn’t necessarily indicates how useful the function is, but also how much trouble people have with it. Best example is NonEmptyCrossJoin, which made it into Top 10. Yet I am inclined to think that this is not because everybody uses it, but because nobody understands this function. I have actually never met anybody who fully understands how NonEmptyCrossJoin works. Even I have trouble with it from time to time.

Taking into account all the if’s and but’s above, the results are still interesting. Some things are predictable. CurrentMember is a clear leader, as expected. IIF, Generate, CrossJoin – are all in the top 10 as well. Another expected thing is symmetric functions like PrevMember and NextMember. PrevMember was mentioned 10 times more then NextMember – and this is natural, in calculations we tend to go back in time rather then forward. In fact, I was surprised that NextMember got 17 hits at all. Same story with TopCount vs. BottomCount – who cares about worst ten, we always look at best 10, aren’t we ? Well, I will put both statistics in my report though 🙂 But Head and Tail are going head to head with almost equal ranking. Not sure why is that, given that Head and Tail are used in pretty distinct scenarios. Also, the popularity of Ancestor surprised me, somehow I didn’t deal with it much, but it overtook StrToMember, PrevMember and Lag, which are thought are much more widely used. Overall, results are interesting to look at. Definitely, when choosing which MDX function to tune performance for before we ship AS2005, I am going back to this list.

Functions mentioned in more then 200 threads

MDX Function # threads
CurrentMember 3660
IIF 1340
Descendants 826
Generate 670
Crossjoin 593
NonEmptyCrossJoin 373
Avg 350
Ytd 285
IsEmpty 265
TopCount 211
Functions mentioned in less then 5 threads:

MDX Function # threads
DrilldownMemberBottom 0
DrilldownMemberTop 0
IsSibling 0
LinRegR2 0
LinRegVariance 0
StdevP 0
VarianceP 0
CalculationCurrentPass 1
CovarianceN 1
DrilldownLevelTop 1
DrillupMemebr 1
StddevP 1
StripCalculatedMembers 1
VarP 1
BottomSum 2
DrilldownLevelBottom 2
DrillupLevel 2
LinRegIntercect 3
TupleToStr 3
Covariance 4

Full list can be found here: