Hello there
I am using Richard Kutchaks CellsetGrid though this issue is of a general kind. I want to filter on a hierarchy. This filtering occurs as a subcube. I intend only to chose one member at a time. The thing is that I want to read the property "level" from the chosen (filtered) member. I can't use currentmember since no member is actually chosen, but the filtered hierarchy points at a member.
Is there a way to catch that member and through that actually read properties of it? I don't care if it fails when more than one member is chosen.
This code worked when I used the old way of filtering which cannot be used with CellsetGrid:
CASE WHEN [Prisme Dimension].[Hierarki Prisme Budget].currentmember.LEVEL.NAME = [Anvisning].[Hierarki Anvisning].currentmember.Properties("Level")
THEN 1
ELSE 0
END
This is the code that the CellsetGrid fires (with me modifying the calculated member so you can see partly what I want to achieve:
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).name'
SELECT
{[Measures].[Test2]} ON COLUMNS
from
(Select
{{[Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]}
}
on 0 from [writebacktest]) CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
Background for interested folks:
Each member of a organisation has a property with the name of a level of another dimension. I want to combine these two things. When a user choses a organisation member, it automatically gets the other dimensions level and will, when the user drillsdown to that level, active a action. This action is the key to enable input of forecasts (they should only be made on the level that is given by the organisation's property).
Johan
I actually found out by extensive search in this forum, the very answer. It doesn't fit perfectly but it is an answer. This MDX will work. Notice how I've added the filtered dimension once again in the query. This is not supported out of the box in the CellSetGrid which unfortunately is the only "MDX Compatibility=2" viewer I've got. SQL 2005 tools are not level 2 compatible (funny enough).
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).Properties("Level")'
SELECT
{[Measures].[Test2]} ON COLUMNS,
[Anvisning].[Hierarki Anvisning].[Anvisning 2] ON ROWS
from
(Select
{{[Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]}
}
on 0 from [writebacktest]) CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
//Johan
|||What we should tell Richard, is that CellSetGrid should modify its query generation to do the same thing as Excel 2007 does. When there is a single member selected - use WHERE clause. I.e.
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).Properties("Level")'
SELECT
{[Measures].[Test2]} ON COLUMNS
from [writebacktest])
WHERE [Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
|||Well, this issue has learned me a bit about the new query practices. Richard has been so kind to expose his sourcecode. I thought of actually to try to see how I could modifiy the query methods myself. It might be that I need to add more dimensions and so it should be possible to set a query parameter: Use Where Clause and Use Subcube.
//Johan
sql