Analysis Services · Excel · MDX · Multidimensional

Obscure MDX Month: Recreating The Star Ratings Measure In MDX Using Excel Functions

I still love MDX, but I’m aware that I blog about it less and less – which is a shame, I know. Therefore I’ve decided that for the next four weeks I’m going to write about some obscure MDX topics that hopefully will make all you SSAS MD diehards out there feel less neglected… even if they don’t have much practical use.

Let’s start off with recreating my ever-popular DAX star-ratings measure in MDX. Well, not exactly pure MDX, but did you know that in MDX you can call some Excel functions (in the same way you can call some VBA functions)? It’s a really, really bad thing to do from a query performance point of view, but it does allow you to do some useful calculations that might otherwise be impossible. Here’s a query on the Adventure Works cube that uses the Excel Rept() and Unichar() functions (functions that do not exist in MDX proper) to recreate my start-ratings measure:

[sourcecode language=”text” padlinenumbers=”true”]
CINT([Measures].[Internet Sales Amount]/10000))
10-CINT([Measures].[Internet Sales Amount]/10000))

SELECT {[Measures].[Internet Sales Amount],MEASURES.STARS} ON 0,
[Measures].[Internet Sales Amount],
ON 1
[Adventure Works]



Here’s the same measure used in a PivotTable:

4 thoughts on “Obscure MDX Month: Recreating The Star Ratings Measure In MDX Using Excel Functions

  1. Great! Especially in the pivot table environment!

    What do you think about the possible extensions to achieve some kind of automatic bar charts in the pivot table?:
    – instead of stars repeating blocks (filled rectangles)
    – automatically find the maximum value to scale properly in the given context

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.