creating a custom member and using in a prompt .(Dimensional Reporting)

[cognos05]

Hi ,

I have a countries dimension and it has members like

NAM,LATAM-North and LATAM South and countries under each .

Now I have a query which shows all countries under LATAM NORTH and LATAM South .

Descendants(set(LatamNorth,Latam South),Countries).


Now user requires a custom member to also be shown in prompt .

That is LATAM North - Mexico (Exclude Mexico from LATAM North)


So I wrote a custom item where
Except(LatamaNorth , Mexico )

And then tried to do a union to the  query that feeds the prompt

Uninon(Descendants(set(LatamNorth,Latam South),Countries),Except(LatamaNorth , Mexico ) )


But I get a member to value coercion error .

Can anyone suggest me how to handle this .

[MFGF]

Hi ,

I have a countries dimension and it has members like

NAM,LATAM-North and LATAM South and countries under each .

Now I have a query which shows all countries under LATAM NORTH and LATAM South .

Descendants(set(LatamNorth,Latam South),Countries).


Now user requires a custom member to also be shown in prompt .

That is LATAM North - Mexico (Exclude Mexico from LATAM North)


So I wrote a custom item where
Except(LatamaNorth , Mexico )

And then tried to do a union to the  query that feeds the prompt

Uninon(Descendants(set(LatamNorth,Latam South),Countries),Except(LatamaNorth , Mexico ) )


But I get a member to value coercion error .

Can anyone suggest me how to handle this .

Hi,

Your expression doesn't seem to make logical sense - at least not to me

If I'm understanding, you have three members on one level - [NAM], [LATAM-North] and [LATAM South]
The members at Countries level below this are your Country members?

If that is true, what are you trying to achieve with the part of your expression that is Except(LatamaNorth , Mexico ). These are two individual members at different levels? Are you trying to get all the Country members below [LATAM-North] other than the [Mexico] member? If so, wouldn't the expression for this be

Except(descendants([LATAM-North], [Countries]), [Mexico])

Can you elaborate?

MF.

[cognos05]

sorry , you were correct , i changed the expression to except(Descendants(LatamNorth],Country),[Mexico]).

This works with out error .

But i want this as 1 member shown in propmt ,along with other countries, now its showing all the countries except mexico . I want this to be grouped as one member and shown as a value in prompt.

Thanks

[CognosPaul]

The except function is set function that is creating a list of members. What you want to do is to create a calculated member that will merge the resulting set into a single member.

Use the following syntax:

member(aggregate(currentMeasure within set except(Descendants(LatamNorth],Country),[Mexico]))
,'LATAM North - Mexico'
,'LATAM North - Mexico'
,INSERT_HIERARCHY_HERE)

Add that as a static item to your prompt.

[cognos05]

I have created that member under the name test .

And have the following expression .

member(aggregate(currentMeasure within set except(Descendants([LATAM-NORTH],[LATAM Sales].[Countries].[Countries].[Country]),[MEXICO])
,'LATAM North - Mexico'
,'LATAM North - Mexico'
,[LATAM Sales].[Countries].[Countries]))




But how do i know show all the countries and this one member in prompt .


If I do union  , it will throw me an error as the member expression is not a set .

can I have static items , as well as query fed to a same prompt .

If its static then i can only add text values , should i do some calc when that text is selected .

[CognosPaul]

You can absolutely have both query derived items and static items in a single prompt. Give it a try and see what happens.

[cognos05]

how do i add the static member .

I have a member created in the query called test  which has the calculation .

When i go to static choices add , it asks for use value and display value .


Use value should it be the member name in the query which is test .

[CognosPaul]

Best would be to put the member() function in the use value and LATAM North - Mexico as the display.

If you have the [test] data item in your query, you could probably get away with having [test] as the use.

[cognos05]

No Luck with it .Getting the below error:

Invalid format for prompt 'PrmCountry'. Expected format is unknown.

getting this error when that member is selected from prompt .

report filters on prompt value used as slicer below .Works fine if other members from the query are selected .

Slicer used : #prompt('PrmCountry','mun','[LATAM-NORTH]')#

Any help is appreciated

[CognosPaul]

Use token instead of mun.