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