Using prompts in slicers- Dimensional Data

[Manu0521]

Hi All,
I will be using four prompts in my report which are cascading prompts and each two from different hierarchies say Product and Region.
I want a propmt to be added inside the report and not a generated prompt.
I will drag 4 value prompts and this will generate four different queries.and a 4 conditions in my default filter for the main query..

How should I do this using slicers. Should I add the " ->?prm1?" in the slicer of all the prompt queries. Will this automatically filter my main query..

Thanks,

[MFGF]

Hi,

What are you trying to control using the prompts? The members in the rows? The members in the columns? The values in the cells? The answers to these questions will dictate where you add the relevant dimensional expressions to reference the prompt parameters:

If you want a prompt to control the members in the rows or the columns, use a dimensional expression in the rows or the columns such as:

[desired level /hierarchy / set] -> ?Parameter?

or if you want to be able to select multiple members

set([desired level /hierarchy / set] -> ?Parameter?)

If you want a prompt to filter the measures in the cells so that they are in context for the member(s) from other dimension(s) selected, use a slicer member set in the slicer filter of your query with the expression

set([desired level /hierarchy / set] -> ?Parameter?)

Cheers!

MF.

[Manu0521]

Hi MFGF,

Thanks for the response and I am half way through .
1) My requirement - Prompt control the members of the rows.

I have a level country used in my report. I drag a value prompt and add country level .next i add a data item (cross tab)add its expression as [desired level /hierarchy / set/Country] -> ?country->?name?

1) This gives me the country based on my selection. But by default it asks me to chose one country as a generated prompt. While running the report ,I wanted to show all the country and then allow user to have an option to select.
2) I wanted to show the country retrieved in prompt based on certail filter criteria .So how and where should the filter be. (Example show the sales man for certain particular regions , so my prompt will show  only the sales man in east region)

And finally,
if i am using state as a prompt which is cascaded with the country and I show them nested in rows. How should that be done. is that I create one more data item for state and [state]->?state? should I define cascading.

I read that we should avoid using detail filters so I wantted to know how to achieve this without detail filters in dimensional reporting
your help is appreciated.

Thanks,

[MFGF]

Hi,

The way I see it, to get your countries, this is what you need to do:

1. Use a Query Calculation as the rows of your crosstab. Give it a name of Countries and set the expression to be [Your Countries hierarchy] -> ?CountryParam?
2. Define a prompt based on the existing CountryParam parameter. For now, choose the Country level from the hierarchy as the Use Value.
3. Go to the query the prompt uses, and add a new calculated query item with the expression union([All Countries member], children([All Countries member]))
4. Back on the report page, set this item as the Use Value for your prompt.
5. Set the Auto Submit property of the prompt to Yes.
6. Expand your package tree on the left, find the All Countries member, right-click on it and choose Properties. Find and copy the Member Unique Name of this member.
7. Go to the Default Selections property of the prompt, add a new default selection, and paste in the MUN of the All Countries member as the default.

I'm not really sure what you mean by point 2. Do you mean you want the countries displayed in the prompt to be filtered somehow by salesman? Do you mean you want the salesmen in your report to be only for the specified country? Something else?

For the state, do you just want to prompt for (and see) a single state or do you want to see all states for the selected country? If it's the former, you would use the same approach as for Country within the crosstab, and the calculated item in your State prompt query would be descendants([Your Countries hierarchy] -> ?CountryParam?, [Your States level])

Cheers!

MF.

[Manu0521]

Hi MFGF,

I am stuck at step 3( union([All Countries member], children([All Countries member])))

I dont have some thing like All countries member, at my country level I have only individual members .
How to get the all ?   I have the mun name for a member like [Country].[@MEMBER].[UnitedStates].

And also this prompt query will now have two fields right , one the country level member from the hierarchy as the Use Value and other the calculated query which will now be changed to the use value.

Thanks,

[Manu0521]

Hi MFGF,

Any help on this.  I am  stuck at step 3 .

Thanks,

[MFGF]

Hi MFGF,

I am stuck at step 3( union([All Countries member], children([All Countries member])))

I dont have some thing like All countries member, at my country level I have only individual members .
How to get the all ?   I have the mun name for a member like [Country].[@MEMBER].[UnitedStates].

And also this prompt query will now have two fields right , one the country level member from the hierarchy as the Use Value and other the calculated query which will now be changed to the use value.

Thanks,

I assumed that the Country level was the first real level in the hierarchy - is this the case? If it is, then all Country members will belong to the root (ALL) member of the hierarchy. If not, then perhaps this:

union([Root member of the hierarchy], descendants([Root member of the hierarchy], [Your Country level]))

Cheers!

MF.

[Manu0521]

My prompt should show only the values for 5 countries and All - which includes these 5 countries based on the few fixed regions say east and north .
I will use this union([Root member of the hierarchy], descendants([Root member of the hierarchy], [Your Country level])) and where will i mention the filter condition

Thanks,

[Manu0521]

Hi MFGF,
My dimension will look like this ,
Dimension - Dimension Name - Sales
Level 1 -  All levels -> Member  -> All regions
Level 2 - Regions -> Member -> East, West,North
Level 3 - Purchasers -> Member -> A, B, C
Level 4 -Sales Representatives -> Member ->W, D, Davis
Level 5 - Customers ->member -> john, mike

Say, I wanted to show the purchasers for specific regions in my propmt and in my row of the cross tab.

I added a value prompt and say its use value would be descendants( set(North, East),Purchasers)
And my data item will have a query calculation with Purchasers->?above prompt name?

In this way I am not able to select a default all value to my prompt and it always shows a generated propmt to select a purchaser.

If I add a new query item to my promp which does union([All Purchasers member], children([All Purchasers member]))
Here I dont see a all purchasers, it directly starts from A, B,C. And also how to include the region filter here.

Your help is appreciated.

Thanks,

[MFGF]

Hi MFGF,
My dimension will look like this ,
Dimension - Dimension Name - Sales
Level 1 -  All levels -> Member  -> All regions
Level 2 - Regions -> Member -> East, West,North
Level 3 - Purchasers -> Member -> A, B, C
Level 4 -Sales Representatives -> Member ->W, D, Davis
Level 5 - Customers ->member -> john, mike

Say, I wanted to show the purchasers for specific regions in my propmt and in my row of the cross tab.

I added a value prompt and say its use value would be descendants( set(North, East),Purchasers)
And my data item will have a query calculation with Purchasers->?above prompt name?

In this way I am not able to select a default all value to my prompt and it always shows a generated propmt to select a purchaser.

If I add a new query item to my promp which does union([All Purchasers member], children([All Purchasers member]))
Here I dont see a all purchasers, it directly starts from A, B,C. And also how to include the region filter here.

Your help is appreciated.

Thanks,

Whoa! Let's pause for a moment and take stock. What issue are we trying to solve here? I thought we were working through a process to get your Country prompt working? Is this a new requirement? What happened with your country prompt?

MF.

[Manu0521]

I just took country as an example. both are the same requirement. Not able to find all set and perform union. So I displayed my dimension structure.