PromptMany macro throwing error when used in cascading prompt

[cognos05]

Hi,

I am using dimensional reporting . I have two prompts, one is Sales Reps and other is Accounts under each rep. I wanted to them to be filtered based on user selection. I have allowed user to select multiple values on both the prompts.

Query for Reps -> Filter(Reps, Tuple([Sales],[CurrentFYTD])>0)
Query for Accounts-> This is DQM model it expects a query to be in relation with first value prompt., so the query should be like

Filter(Descendants(set(#promptmany('prmSalesRep','mun','[ALL REP]','(','',')')#),1),tuple([H0 Sales])>0)

I am not sure about the above expression within Descendants

If user doesnt select any rep by Default I will be showing all reps, i.e [ALL REP].


After this my slicer expression are

#promptmany('prmAccount','mun','[All Accounts]','(','',')')#

[All Accounts] - #promptmany('prmSalesRep','mun','[ALL REP]','(','',')')#

When I do a multiselect on my REP, I am getting the below error :

QE-DEF-0260

      Parsing error before or near position: 165 of: "Filter(Descendants((([Sales].[ShipTo Customer].[ShipTo Customer].[ShipToRep]->:[PC].[@MEMBER].[TEAM CAPE -REP];"


I think this error is due to my Account prompts query used.

Please advise the correct syntax for Account query




Thanks,
Nithya

[Bark]

Hi,

I think you are overcomplicating the solution but I don't know the model to be certain. Could you post the tree structure and what you want to achieve?

In any case, from the top of my head, I think the problem is with the parenthesis:

Filter(Descendants(set(#promptmany('prmSalesRep','mun','[ALL REP]','(','',')')#),1),tuple([H0 Sales])>0)

Also, in the slicer, if it is returning a set, shouldn't it be:
set(#promptmany('prmAccount','mun','[All Accounts]','(','',')')#)

Same for prmSalesRep, I believe.

Hope it helps.

Regards,

Bark

[cognos05]

Hierarchy is like Reps and then Accounts.

SO first prompt's query is

Rep - Set ( Reps)

Now the second prompts query will have to return a default value of Set(reps) when no Accounts are selected or return multiple accounts based on Reps selection.

Filter(Descendants(set(#promptmany('prmSalesRep','mun','[ALL REP]','(','',')')#),1),tuple([H0 Sales])>0)

Please help me how to return a default set of reps when no accounts is selected.

Thanks,
Nithya