hi folks,
how is it possible to count all the members which are included in a level.
greets
For better explanation i added a screen shot
i want to count how many members (green) are in the level "Pk-Berater" (red)
please help me
try
count ( 1 within set [Cube].[Dimension].[Hierarchy].[Level])
hi PaulM,
thank you for your reply it works you help me so much
thx
What if you have a Tree Prompt and you want to only COUNT the lowest level based on what the User selects?
???
count(1 within set descendants (#prompt('P','memberuniquename')#,[Cube].[Hierarchy].[Level].[LowestLevel]))
The lowestlevel specified here needs to be from the same hierarchy as the prompt.
I am using the Prompt to also filter the report (via a Tree Prompt):
[Cube].[Hierarchy].[Level] in (?Hier_Par?)
And when I create a new data item with the above code I get prompted again for ?Hier_Par? ... for some reason it doesn't like what is selected in the Tree Prompt even though the above IN works. Maybe the multi-select is screwing it up?
Also, what is the easiest way to figure out what the 'memberuniquename' is (MUM) .. I checked out the MDX from Analysis Studio and found: [PC].[@MEMBER].[267845] ........ Is this the MUM? It will always have to reference a specific member in the cube correct (such as [267845]?
THANKS!!!!!
You're correct. The previous code will not work with a multi-select prompt.
Try this instead:
count( 1 within set descendants (#promptmany('Hier_Par','memberuniquename','','set(','[sales_and_marketing.mdc].[Time].[Time]',')')#,[sales_and_marketing.mdc].[Time].[Time].[Month]))
I've added a set( to the header text and a ) to the trailer text.
An easy way to find the MUN for an item is to drag a member into a query item box. You'll need to disable aliasing the members though. In report studio, click on tools-->options-->report-->uncheck Alias member unique namess. After that is unchecked you can drag members into the query.
A final note. In a cube, it's poor practice to use detail filters as you're doing. It's far more efficient to use slicers. I recommend deleting the filter you have, and putting this prompt into the slicer:
#promptmany('Hier_Par','memberuniquename','','set(','[Cube].[Hierarchy].[Level]',')')#
THANK YOU ... ;D .... Lots of great feedback!!!
One last question on this thread ... On the prompt page for 'MonthPar' (the month parameter) .. which is just a Value Prompt .. I can't figure out how to default this to the current month (minus 2):
[cube].[Date].[Current Month].[Current Month]
When I add that data value into the 'default selections' it ignores it .. Ideally I would like it to be 2 months prior to the current month (but obviously since I can't get the current month working that is moot) ...
thanks for the guidance!!!!
Setting dynamic defaults on prompts can be tricky, but it can be done.
You need to add a static choice to the prompt. Add the query code to the use, 'Current Month' to the display, and set the prompt macro to accept tokens: #prompt('par_year','token')#
Because I'm really pressed for time, and because it's easier for me to give an example than to explain it, I've set up an example report.
THANKS!!!