I have to create cascaded prompt by which i have to provide the option to the users that:
Whenever users refresh the report, the users have to select the any level from the cascading prompt. So i have created cascaded prompt like below:
Objects are : Group->Region–> Country–>Segment–>Sector->Division
I able to select low level (Division) values in the prompt, but i cant select Group or Region or Country or Sector or Segment.
That’s how Cascading Prompts work Only the final value is actually included in the Report query. But, you should be able to choose values from all the Prompts (before, finally, choosing the final prompt).
In case you want to give users the flexibilty to choose any one (or more) of the filter options, you can consider creating optional prompts.
You will have to provide certain details. It depends on what do you actually want to accomplish.
Cascading Prompts are used when your initial pick list(s) is very huge or when you want the Users to go through multiple Objects (data columns) to arrive at the final pick list (Division, in your case).
For example, considering your hierarchy, if ‘Division’ is related with multiple Sectors, and Sectors are related with multiple Segments and so on, and you want your User to specifically choose a particular ‘Division’ based upon a ‘Group’, ‘Region’, ‘Country’, then a simple Cascaded prompt will work. It will of course, include only the final prompt value (‘Division’) in your SQL Query. (What’s the use? Users know their business, they can relate information).
Now, if you want that a User should see the report by filtering on any particular Object; say, the demand is to see the Report for only a particular ‘Group’ (so, including the complete grain within Group), or perhaps, view the report only based upon a particular ‘Group’ and ‘Region’, you can consider creating Optional Prompts.
How will this help you? It will give you all the Prompts, and the User can choose (any combination of them). He/She can enter values for ‘Group’ and ‘Region’, put a default wildcard value in rest of the Prompts (which means the report will fetch all records for selected ‘Groups’ and ‘Regions’) and view the report. Just a word of caution, Optional Prompts are not supported directly on Universe (you would need to do a workaround). They are as of now supported only on Reporting layer.
Only the final value is actually included in the Report query. But, you should be able to choose values from all the Prompts (before, finally, choosing the final prompt).
(BOB member since 2008-08-28)