Build Your Own Prompt and Prompt Page

Create your own prompt and prompt page to control how they appear in a report.

Tip: The Promotion Success sample report in the GO Data Warehouse (analysis) package includes a prompt page. For more information about The Great Outdoors Company samples, see Sample Reports and Packages.

Steps

1. Pause the pointer over the page explorer button  and click Prompt Pages.

2. In the Insertable Objects pane, on the Toolbox tab, drag Page to the Prompt Pages box.

3. Double-click the page you just created.

4. In the Insertable Objects pane, on the Toolbox tab , drag one of the following prompt controls to the prompt page.

   Prompt control	Description
   Text Box Prompt	Retrieves data based on a value that users type. Use this control when users know exactly what value they want to enter, such as a name or account number.
   Value Prompt	Retrieves data based on values that users select from a list. Use this control to show a list of possible values from which users can choose. Note: The maximum number of items that can appear in a list is 5000.
   Select & Search Prompt	Retrieves values based on search criteria that users specify. Data is then retrieved based on values users select from the search results. Use this control instead of a value prompt if the list of values is very long, which can slow down performance. Tip: Users have the option of performing a case sensitive or case insensitive search. A case sensitive search is faster, while a case insensitive search usually returns more values. You cannot use this control if you are working with SAP BW data sources.
   Date Prompt	Retrieves data based on a date that users select. Use this control to filter a date column. Users can retrieve data for a specific day, a set of days, or a range of days.
   Time Prompt	Retrieves data based on a time that users select. Use this control to restrict a report to a particular time or time range. For example, users can see how many orders are received after business hours. Users can then use this information to determine the number of staff needed to work after hours.
   Date & Time Prompt	Retrieves data based on a date and time that users select. Use this control to filter a datetime or timestamp column. This control is useful for specifying ranges. For example, users can retrieve all orders received from Monday at 12:00 a.m. to Friday at 5:00 p.m.
   Interval Prompt	Retrieves data based on a time interval that users specify. Use this control to retrieve data that is related to the passage of time. For example, users can retrieve a list of products that were returned 30 or more days after they were purchased.
   Tree Prompt	Retrieves data based on values that users select from a list. Values are organized hierarchically. This control is useful when you are working with dimensional data sources. Data is shown from the top of a dimension hierarchy to the most detailed member, and users can choose the level of detail they want to view in the report. For more information about tree prompts, see Control the Data That Appears in a Tree Prompt.
   Generated Prompt	Selects a prompt control based on the data type of the data item. This control acts like a placeholder. When users run the report, the control is replaced by the appropriate prompt control. For example, if users are prompted for date values, the control is replaced by a date & time prompt.

   The Prompt Wizard dialog box appears.

5. If you are creating a text box, date, time, date and time, interval, or generated prompt, do the following:

   • Create a new parameter for the prompt or use an existing parameter.

   • Click Next.

   • If you create a new parameter, define the expression by selecting a data item from the package and the operator to use.

     Tip: Make the prompt optional by selecting the Make the filter optional check box.

   • Go to step 7.

6. If you are creating a value, select & search, or tree prompt, do the following:

   • Create a new parameter for the prompt or use an existing parameter.

   • Click Next.

   • If you created a new parameter and you want to use the parameter to filter data, select the Create a parameterized filter check box and define the expression by selecting a data item from the package and the operator to use.

     If you are creating a tree prompt, you must choose in in the Operator box.

     You can also use a parameter to provide a value for a layout calculation, such as showing a user's name in the report. When the report is run, you can use a parameter to prompt the user to type his name and have it appear in the report.

     Tip: Make the prompt optional by selecting the Make the filter optional check box.

   • Click Next.

   • If you created a parameterized filter and you have more than one query defined in the report, select the check box for the query on which to filter and click Next.

   • Select the Create new query check box to create the query that will be used to build the list of data values shown when the report is run.

     Tip: Do not create a new query if you want to link the prompt to an existing query or if you intend to create the query at a later time.

   • Click the ellipsis (...) button beside Values to use and click the data item on which to prompt.

   • To choose a data item that is different than what users will see when they are prompted, click the ellipsis (...) button beside Values to display and click the data item.

   • To create a cascading prompt, in the Cascading source box, click the parameter that represents the cascade source.

7. Click Finish.

The prompt control is added to the prompt page. A prompt page is like a report page. You can insert graphics and text and apply formatting.

You can also modify the properties of the prompt control by clicking it and making changes in the Properties pane.

Example - Create a Report Showing Products Shipped for a Specific Time Interval

You are a report author at The Great Outdoors Company, which sells sporting equipment. You are requested to create a report that shows the time interval between closing and shipping dates.

Steps

1. Open Report Studio with the GO Data Warehouse (query) package.

2. In the Welcome dialog box, click Create a new report or template.

3. In the New dialog box, click List and click OK.

4. In the Insertable Objects pane, on the Source tab , expand Sales and Marketing (query) and Sales (query).

5. Expand Sales order and double-click Order number.

6. Expand Product and double-click Product name.

7. Expand Sales fact and double-click Quantity.

8. In the Insertable Objects pane, on the Toolbox tab , drag Query Calculation to the right of Return quantity in the list.

9. In the Name box, type the following and click OK:

   Time Interval

10. In the Expression Definition box, type the following and click OK:

    [Sales (query)].[Time dimension (close day)].[Date (close day)]-[Sales (query)].[Time (ship date)].[Date (ship date)]

11. From the Data menu, click Filters .

12. Click the add button .

13. In the Expression Definition box, type

    [Sales (query)].[Time dimension (close day)].[Date (close day)]-[Sales (query)].[Time (ship date)].[Date (ship date)] > ?p1?

14. Click OK twice.

    A parameterized filter is created that will return data when the difference between the return date and the order date is greater than the value specified by the user.

15. Pause the pointer over the page explorer button  and click Prompt Pages.

16. In the Insertable Objects pane, drag Page to the Prompt Pages pane, and then double-click it.

17. In the Insertable Objects pane, on the Toolbox tab, drag Interval Prompt to the work area.

    The Prompt Wizard dialog box appears.

18. Click Use existing parameter, and then click p1.

19. Click Finish.

20. Run the report.

    An interval prompt appears.

21. In the Days box, type a value and click Finish.

    Tip: You can also type values for the Hrs and Mins boxes.

A list report appears showing all products that were shipped after the time interval you specified. For example, if you typed 5, the list will show products that were shipped more than 5 days after the order date.

---