count of members in a level

[nugget]

hi folks,

how is it possible to count all the members which are included in a level.

greets

[nugget]

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

[CognosPaul]

try

Code Select Expand

count ( 1 within set [Cube].[Dimension].[Hierarchy].[Level])

[nugget]

hi PaulM,

thank you for your reply it works you help me so much

thx

[kado]

What if you have a Tree Prompt and you want to only COUNT the lowest level based on what the User selects?

???

[CognosPaul]

Code Select Expand

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.

[kado]

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!!!!!

[CognosPaul]

You're correct. The previous code will not work with a multi-select prompt.

Try this instead:

Code Select Expand

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]',')')#

[kado]

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!!!!

[CognosPaul]

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.

[kado]

THANKS!!!