MDX Query using other hierarchy in same dimension as in slicer


The other day a coworker of mine presented me with a problem in one of our solutions. His basic pain was, that the front-end of the application only allowed for one dropdown containing whatever dimension was set up to be displayed. This dimension is to act as slicer in a scorecard application. Since his KPIs were aiming different levels of different hierarchies in the date dimension, he was experiencing problems when faced with the choice of only one available date hierarchy.

Luckily we are able to solve this issue by means of MDX.
The following statement should display the solution, by the use of the method EXISTS and the knowledge of the structure of the date dimension hierarchies.

WITH
	MEMBER [Measures].[Test] AS 
		( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])
SELECT
	{[Test]} ON COLUMNS
FROM [Adventure Works]
WHERE { [Date].[Calendar].[Date].&[20070201] }

We use the EXISTS function to find the members of the [Date].[Calendar Week] hierarchy with the slicer set to  a member of another hierarchy, in this case [Date].[Calendar].[Date].&[20070201]. Note that we need to get Item(1) of the collection returned, as we get the All member at first index (0).

UPDATE:

As mentioned by Hrvoje Piasevoli (blog|twitter) it is a more clean solution to just query by the tuple made by the week hierarchy and the measure, eg.:

([Date].[Calendar Week].CurrrenMember, [Measures].[Internet Sales Amount])

In our case however, we discovered that the attribute relations in the Date dimension was not setup correctly to allow us to “convert” a member in one hierarchy to another. We ended up with the [All] member of that hierarchy.
We solved the issue by making a common root for the two date hierarchies, as seen in the Adventure Works cube.

Reklamer

2 responses to this post.

  1. Hi,
    There is a simpler method: Just use [Date].[Calendar Week].CurrentMember
    Example:
    WITH
    MEMBER [Measures].[Test] AS
    ( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])

    MEMBER [Measures].[Test 1] AS
    ( [Date].[Calendar Week].CurrentMember, [Measures].[Internet Sales Amount])

    SELECT
    {[Test], [Test 1]} ON COLUMNS
    FROM [Adventure Works]
    WHERE { [Date].[Calendar].[Date].&[20070201] }

    Kind regards,
    Hrvoje Piasevoli

    Svar

    • Hi Hrvoje

      Right now I’m “systems off” e.g on vacation, but I am almost certain I tried what you suggest. I Will although try it once I’m back. Thanks for the heads up 🙂

      Svar

Skriv et svar

Udfyld dine oplysninger nedenfor eller klik på et ikon for at logge ind:

WordPress.com Logo

Du kommenterer med din WordPress.com konto. Log Out / Skift )

Twitter picture

Du kommenterer med din Twitter konto. Log Out / Skift )

Facebook photo

Du kommenterer med din Facebook konto. Log Out / Skift )

Google+ photo

Du kommenterer med din Google+ konto. Log Out / Skift )

Connecting to %s

%d bloggers like this: