MDX - getting the level of members currently used in the where clause
I have a simple data cube with an organization structure hierarchy. In my calculations within a cube, I would like to have different calculations depending on which organizational level is currently being used in the WHERE clause in the MDX query.
So, let's say that I have 5 levels of organization chart, and for the last level (storage level) I would like to change the way the calculations are calculated using an expression, like this:
IIF([Organization Structure].[Parent Id].LEVEL IS
[Organization Structure].[Parent Id].[Level 05], 'THIS IS STORE', 'THIS IS NOT')
This in the Visual Studio browser results in what we really want:
and the same for using MDX query like:
SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123]
}
The problem starts when we want to use more than one organizational chart element in the WHERE clause. It is only allowed to have items in this section from one level, and I would still like to know which level it is, but of course when we add the second item to the WHERE like so:
SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123],
[Organization Structure].[Parent Id].&[124]
}
I get the error "currentmember failed because the coordinate for the attribute contains a set."
This is why, in my expression, I tried to use the ITEM (0) function in many different configurations, but I just couldn't find a way to use it in the set of elements that are currently used in the WHERE clause ... So the big question is:
How do I get the set of items listed in the WHERE clause that is currently running, so I can use Item (0) on that set, or is there another way to get the level of the selected items knowing that they should be the same level?
source to share
Use Currentmember
in combination with set
in a sentence is where
potentially problematic.
See this post from chris Webb: http://blog.crossjoin.co.uk/2009/08/08/sets-in-the-where-clause-and-autoexists/
Below is a possible workaround for your situation, you can try adapting to your theme.
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 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]
};
The results are as follows:
So, your expression might look something like this:
IIF
(
(EXISTING
[Organization Structure].[Parent Id].MEMBERS).Item(0).Level
IS
[Organization Structure].[Parent Id].[Level 05]
,'THIS IS STORE'
,'THIS IS NOT'
)
source to share