Cascading Prompt is an additional feature in BO reports where user wants to input the values from higher level to Granular level which they are not aware. To help them, cascading prompt will provides the facility to Input the values from Hierarchical way (higher level to Granular level).
For example, User wants to enter input as City Montebello (UNITED STATES –> CALIFORNIA –> MONTEBELLO) in report, Cascading Prompt will help the user to select the input values from Country (UNITED STATES) and then select the United States State (CALIFORNIA) and then select the California State City Montebello.
A cascading list of values is a sequence of lists of values associated with a hierarchy in a universe. Prompts are defined for each hierarchy level to return a list of values for the level.
You can associate a default or custom hierarchy with lists of values, called Cascading lists of values.
If the Hierarchy objects are defined in Cascading List of Values (Cascading LOV), when the report uses those objects, while refreshing the report the hierarchy is displayed, and report prompted to choose a level and user can select one or more values from the list of values, before the query is run.
Setting Up Cascading List of Values
1. Go to Tools –> List of Values.. –>Create Cascading List of Values.
2. Select Customer Hierarchy or Default Hierarchy option in the window which one you want to Create Cascading LOV.
3. Double click / select the object and click add button to add the objects in the Cascading LOV section.
Note: Better to select first object as higher granularity in your hierarchy. Or else you change the object order by using up/down button.
4. Once you added the LOV object into the LOV section each object, should have Prompted text. By Default the Prompted text would be “Enter value for <Object Name>”. If you want you can change by double clicking the prompted Text.
Note: Last object selected in the LOV section will not have any Prompted Text and that should be your Conditional Prompt in the universe object.
5. Select check box “Hierarchical View” to get the prompt view like a collapse/expand style of selection in Web Intelligence, disable the check box will result in showing the prompt separately (individually) for each LOV in the cascading List. Means prompts for City, State and Country in the last. But the view will cascade on selected LOV’s.
6. Click Generate LOV’s. It will show “The Cascading List of Values was generated successfully.”
7. Create conditional prompt object for City to get the cascading prompt in the report.
Cascading LOV Behavior in Desktop Reports
3. Run the report. Then the report will ask user to enter / sect the values in the order of City – State – Country.
5. Click the Values. It will ask user “Enter Value for State Address”.
7. Click Values. It will show all the Country names in the LOV.
8. Select UNITED STATES in the list and click OK and then Click OK from Prompt. It will show all the States information from United States Country.
9. Select CALIFORNIA in the list and click OK and then Click OK from Prompt. It will show all the City information from CALIFORNIA State.
10. Select MONTEBELLO in the list and run the report. It will show the product sales for USA–> CALIFORNIA–> MONTEBELLO.
End user, who does not know complete details about the State and City, can select the correct values for their report by using Cascading Prompt / List of values.
Cascading LOV Behavior in Web Intelligence Reports
1. Create the Sales Report for USA–> CALIFORNIA–> MONTEBELLO.
2. Run the report. Then the report will ask user to enter / sect the values in the order of City – State – Country.
3. First it will ask the user “Enter City Name” (This is Conditional Object City prompted text).
Prompt Pop-up will show the values in Tree Structure. User have to expand the Country UNITED STATES (user cannot Select United States in the List and only selection can be done in the City).
Note: In the Hierarchical View, only City prompt will be asked user to select the values and all other information (like Country & State) can be select internal in the List values.
If the Hierarchical View option is not selected in the universe, user will be asked enter three prompts while run the report which is same as normal prompt selection.
By clicking Run Query, report would return the values for USA–CALIFORNIA–MONTEBELLO.