cascading prompts in slicer

[Manu0521]

Hi All,

I have 3 prompts and and prompts 2 and 3 are cascading.
I wanted to filter my report by these values selected in prompt and they dont appear in columns or rows of the report.

And all these 3 prompts are from same dimension ,but are of different levels.



Level 1 -  All levels -> Member  -> All regions
Level 2 - Regions -Individual members
Level 3 - Purchasers -Individual members
Level 4 -Sales Representatives -Individual members
Level 5 - Customers -Individual members


My propmts are level 3, level 4 and level 5. I dont have a all value to choose my MUN from these levels.- Every level starts with individual members.


Now currently my detail filter has 3 conditions for the 3 propmts and I want them to move to Slicers.

How should i prompt macro expression be .

Thanks,

[MFGF]

Hi All,

I have 3 prompts and and prompts 2 and 3 are cascading.
I wanted to filter my report by these values selected in prompt and they dont appear in columns or rows of the report.

And all these 3 prompts are from same dimension ,but are of different levels.



Level 1 -  All levels -> Member  -> All regions
Level 2 - Regions -Individual members
Level 3 - Purchasers -Individual members
Level 4 -Sales Representatives -Individual members
Level 5 - Customers -Individual members


My propmts are level 3, level 4 and level 5. I dont have a all value to choose my MUN from these levels.- Every level starts with individual members.


Now currently my detail filter has 3 conditions for the 3 propmts and I want them to move to Slicers.

How should i prompt macro expression be .

Thanks,

You probably don't need a macro approach to achieve this.

Try this:

1. Define a slicer filter in your crosstab query containing a slicer member set. The expression for this would be set([Your Customer level] -> ?CustomerParam?)
2. Define a value prompt for your purchasers on a prompt page. It will use the Purchasers level as the Use Value and set a parameter called ?PurchaserParam?
3. Define a value prompt for your sales representatives on a second prompt page. It will set a ?SalesRepsParam? parameter and the Use Value (defined as a query calculation in the query delivering the sales representatives prompt values) will be descendants(set([Your Purchasers level] -> ?PurchaserParam?),[Your Sales Representatives level])
4. Define a value prompt for your customers on a third prompt page. It will set a ?CustomerParam? parameter and the Use Value (defined as an query calculation in the query delivering the customer prompt values) will be descendants(set([Your Sales Representatives level] -> ?SalesRepParam?),[Your Customer level])

Cheers!

MF.

[Manu0521]

Hi MFGF,

I used the above approach, but I used all the prompts in the same report page.

If I use descendants then I need not give cascade property right?

When I run my report , my report page shows up with three generated prompts one after the other and I choose one value from higher level, the second prompt doesnt get changed wrt to those values.It asks for all three prompt values. I want by default to be all purchasers and then user can do any changes.

Here my purchasers which is at the higher level doesnt have any all value in the member, it starts with a,b,c..

Can you let me know where am I going wrong?

Thanks,

[MFGF]

I used the above approach, but I used all the prompts in the same report page.

The technique I posted requires you to have each prompt on a separate prompt page so that a prompt is submitted before the next one renders.

With prompts on a report page, you will need to define a Default Selection for each prompt - pick a Purchaser member, a Sales Rep Member and a Customer member and put the MUN for each in as the default selection for the prompt. You will also need to either set each to Auto Submit=Yes or else you will need to add a prompt button to submit each prompt.

MF.

[Manu0521]

If the value in the propmt should be dynamic, then how would i give a specific mun value. And is it advisable to have all value in each levels? I dont have an all value for purchasers. And how to do i assign a mun value for my cascaded propmt that is second propmt


Thanks,

[MFGF]

Hi,

From the Package Tree, right click the desired member and go into it's properties - you can copy the MUN from there. You need to have a default selection defined for each prompt or else you will get generated prompts when you initially run the report. Pick a purchaser member as the default for your Purchaser prompt, pick one of that member's children at the Sales Rep level as the default for your Sales Rep prompt and pick one of the Sales Rep member's children at the Customer level as the default for your Customer prompt.

MF.

[Manu0521]

Hi MFGF,

I understand we need to give mun values by going to its properties, copy the mun name and then paste it in the default selection. How would I know which purchasers mun should be copied, values might change if different users log in right?

[MFGF]

How would I know which purchasers mun should be copied, values might change if different users log in right?

MUNs shouldn't change - if they do, all manner of reports would break. A MUN will be the same no matter which user is logging in. Or am I misunderstanding your question?

MF.

[Manu0521]

you are correct,Mun doesnt change for users. I am telling values in my prompt might change based on different users logging in. So how will I know which default mun should be given to which user.

But currently i am not sure how my security works, but if i login as a purrchaser from a specific region say east , I see only purchasers from east. I should handle something like this to set my default Mun Value.