Building Cascading Prompts

Prompts act as questions that help users to customize the information in a report to suit their own needs.

They are composed of three interrelated components.  Parameters form the questions you want to ask users.  Prompt controls provide the user interface in which the questions are asked.  Finally, parameter values provide the answers to the questions.

For an example create a report that displays courses by subject and academic period. 

(If you do not have access to this package or the views within contact the BI team for an alternate example)

Use Package:  X_UST_ODS22_SCT
Use Namespace:  Schedule Offering
From the view: Meeting Time
     Select: 
            SUBJECT_DESC
            TITLE_SHORT_DESC
            SECTION_DESC
            ACADEMIC_PERIOD_DESC
            BUILDING_DESC
            ROOM
            START_DATE
            END_DATE

For this report you will prompt the user first for the class subject, then for a class title and finally for a term.

Click the title of SUBJECT_DESC and while holding down the CTRL key click the titles of TITLE_SHORT_DESC and ACADEMIC_PERIOD_DESC.

On the tool bar, click the Build Prompt Page button .  Based on the report items you selected, Report Studio will build a prompt page that looks like this:



Next create the cascading prompt.

Starting at the bottom of the prompt page, click inside the ACADEMIC_PERIOD_DESC prompt box.

In the properties page, under General, click Cascade Source.

Click the Ellipsis button and, in the Cascade Source box, use the pull-down arrow to select TITLE_SHORT_DESC.  Click OK.

Next, click inside the TITLE_SHORT_DESC prompt box.

In the properties page, under General, click Cascade Source.

Click the Ellipsis button and, in the Cascade Source box, use the pull-down arrow to select SUBJECT_DESC.  Click OK.

To make the parameter values within the prompt box easier to navigate you can sort them.

In the Query tool bar, click on one of the prompts.  Click on the Fact item, then in the properties pane click on SORT.  Use the pull-down arrow to select SORT ASCENDING.  Repeat for the remaining prompts.

There are two ways to create cascading prompt reports:

 A Single-Value Cascading Prompt where the user can get information for one class or item at a time.

A Multiple-Value Cascading Prompt where the user will be re-prompted to select the next set of values for multiple selections.

Creating a Single-Value Cascading Prompt

Click inside the SUBJECT_DESC prompt box.

In the properties pane, under general, click Multi-Select then, using the pull-down arrow, select No.

Again, under General, click Auto-Submit then, using the pull-down arrow, select Yes.  This tells the control to re-prompt automatically once a selection has been made.

Do the same for the TITLE_SHORT_DESC prompt box.

For the final prompt you have the option of leaving it as a multi-select or changing to a single value prompt.

Run the report.  In the Report View window, you will only see values in the SUBJECT_DESC prompt box.  When you click on a subject, names of specific classes appear in the TITLE_SHORT_DESC prompt box.

For Example select:
            ACCT
            Introduction to Accounting
            Spring 2007

Perhaps the user wants to be able to select several subjects at a time.  You can do that by changing the property for each prompt back to Yes for Multi-select.  However, Cognos can not perform the Auto-submit function if a prompt allows for multiple values.  To solve this you simply need to add a re-prompt button.

Creating a Multi-Value Cascading Prompt

In the current report go to the prompt page.

In the Insertable Objects pane, click the Toolbox tab.  Scroll down and click Prompt Button.

Drag the Prompt Button to the right of the SUBJECT_DESC prompt box. 

Drag another Prompt Button to the right of the TITLE_SHORT_DESC prompt box.

Click on the first Prompt Button.  In the Properties pane, under General, click Type then, using the pull-down arrow, select Reprompt.

Repeat for the other Prompt Button.

Click inside the SUBJECT_DESC prompt box.

In the properties pane, under General, click Multi-Select then, using the pull-down arrow, select Yes.  Notice how the Auto Submit property automatically updates to No.

Do the same for the TITLE_SHORT_DESC prompt box.

Run the report.

In the SUBJECT_DESC prompt box ACCT, hold down the CRTL key and click ENGL.  Click the Reprompt button.

In the TITLE_SHORT_DESC prompt box Select All.

In the ACADEMIC_PERIOD_DESC prompt box select Spring 2007.

The report should return a list of the classes offered in Spring of 2007 for Accounting and English.

For more information on Prompts check out  “Understanding Parameters and Prompts”.