MDX - Retrieving a level of members currently used in where clause

Question

I have a simple data cube with organization structure hierarchy defined. In my calculations inside the cube I would like to have different calculations depending on which level of organization items is currently used in WHERE clause in MDX query.

So let's say that I have 5 levels of organization structure, and for the last level (store level) I would like to change the way that calculation is being made using expression for instance:

IIF([Organization Structure].[Parent Id].LEVEL IS [Organization Structure].[Parent Id].[Level 05], 'THIS IS STORE', 'THIS IS NOT')

expression from datacube

This in Visual Studio browser result in something that we actually want: results from browser

and same for using MDX Query like:

SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123]
}

Problem starts, when we want to use more than one organization structure item in WHERE clause. It is allowed to have items in this clause from the same level only, and I still would like to know which level is it, but of course when we add second item to WHERE like so:

SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123],
[Organization Structure].[Parent Id].&[124]
}

I get error that "currentmember failed because the coordinate for the attribute contains a set".

That's why in my expression I have tried to use ITEM(0) function in many different configurations, but I just couldn't find a way to use it on a set of items that are currently used in WHERE clause... So the big question is:

How to get a set of items, that are listed in WHERE clause that is currently being executed so I can use Item(0) on that set, or is there any other way of retrieving Level of currently selected items knowing that they must be the same level?

Why are you interested in having more than one member in `WHERE` clause, when all those values belong to the same hierarchy? Could this be avoided? — SouravA, May 27 '15 at 10:25
Thanks for reply! I'm interested in having more than one member, since I'm using one MDX query to retrieve for instance Sales profit data for 10 stores at the same time, or for 10 regions etc. The thing is that I would like to make different calculation depending on level type as in example that i used above, in my case profit would be calculated differently for stores and for regions. I hope that I clarified my use case. — Bartek Boro, May 27 '15 at 10:42
Got it. Is the hierarchy in which the members are supposed to belong going to be the same or that might vary? Like, here you have members from `[Organization Structure].[Parent Id]` dimension. Is this fixed, or there could be other hierarchy too? — SouravA, May 27 '15 at 11:08

whytheq · Accepted Answer · 2015-05-28T08:19:12.943

Using Currentmember combined with set in the where clause is potentially problematic.

See this post from chris Webb: http://blog.crossjoin.co.uk/2009/08/08/sets-in-the-where-clause-and-autoexists/

Here is a possible workaround for your situation: you can try adapting to your curcumstance.

WITH 
  MEMBER [Measures].[x] AS 
    IIF
    (
       (existing [Geography].[Geography].[State-Province].members).item(0).Level
      IS 
       [Geography].[Geography].[State-Province]
     ,'THIS IS state'
     ,'THIS IS NOT'
    ) 
SELECT 
  {[Measures].[x]} ON COLUMNS
FROM [Adventure Works]
WHERE
(
{[Geography].[Geography].[State-Province].&[77]&[FR],
[Geography].[Geography].[State-Province].&[59]&[FR]}
);

Expanding the above to prove it works:

WITH 
  MEMBER [Measures].[x] AS 
    IIF
    (
        (EXISTING 
          [Geography].[Geography].[State-Province].MEMBERS).Item(0).Level
      IS 
        [Geography].[Geography].[State-Province]
     ,'THIS IS state'
     ,'THIS IS NOT'
    ) 
  MEMBER [Measures].[proof] AS 
    (EXISTING 
      [Geography].[Geography].[State-Province].MEMBERS).Item(0).Member_Caption 
  MEMBER [Measures].[proof2] AS 
    (EXISTING 
      [Geography].[Geography].[State-Province].MEMBERS).Count 
SELECT 
  {
    [Measures].[x]
   ,[Measures].[proof]
   ,[Measures].[proof2]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  {
    [Geography].[Geography].[State-Province].&[77]&[FR]
   ,[Geography].[Geography].[State-Province].&[59]&[FR]
  };

Results in the following:

enter image description here

So your expression could become something like the following:

IIF
(
    (EXISTING 
      [Organization Structure].[Parent Id].MEMBERS).Item(0).Level
  IS 
    [Organization Structure].[Parent Id].[Level 05]
 ,'THIS IS STORE'
 ,'THIS IS NOT'
)

That's not quite right, as `(EXISTING [Organization Structure].[Parent Id].MEMBERS).ITEM(0)` is pointing at first element of **whole** hierarchy, not first element of set out of where clause — Bartek Boro, May 27 '15 at 12:57
Hello Bartek - the AdvWrks example I have provided is a fully tested example - this technique *works* - I will expand it to prove this to you. Please play around with adapting it to your cube. — whytheq, May 28 '15 at 08:07
`(EXISTING ` is a function that means it is not pointing at the whole hierarchy. — whytheq, May 28 '15 at 08:11

MDX - Retrieving a level of members currently used in where clause

1 Answers1