Print      Open PDF Version of Online Help

Analytics > Designing an Analysis > Step 3: Defining Prompts (Optional) > Adding Column Filter Prompts

Adding Column Filter Prompts

A column filter prompt allows users to select values to filter a column in a report. You can define the range of possible filter values and how the user selects the filter values for the report.

You define column filter prompts in the Define Prompts page. When you have multiple column filter prompts constructed for a report, they are presented to the user one at a time, in the order they are listed, from top to bottom.

To add or modify a column filter prompt

1. In the Define Prompts page, click Create Prompt, and select Column Filter Prompt.
2. In the Prompt Properties dialog box, enter a caption for the column filter prompt in the Caption text box.

   The caption appears when a user runs the report.

3. Enter a description in the Description text box (optional).
4. From the Filter on Column drop-down list, select the column in the report that you want to filter, and then, from the Operator drop-down list, select the operator to use.

   If you want the user to select the operator, select the operator *Prompt User.

5. Specify how the user should select values by clicking the appropriate option.

   Users can select values from a drop-down list, or browse through choices and enter them into a text box.

   NOTE: A maximum of 1000 values are permitted if selected from a drop-down list.

6. To allow the user to select only one value for the column filter prompt, select the Single Value Only check box.
7. Under the What values should be shown to the user item, select one of these options:
   • To show no values, click None.

     The user will need to enter the value.

   • To show all possible values, click All Values.
   • To show a subset of values, click Filter Limited Values or SQL Results.

     Filter Limited Values. Limiting values is useful when prompting for multiple columns, or when the results already contain a filter. It prevents a user from choosing values that would result in no data. For example, suppose a report contains a country column and a city column, and already contains the filter Country=France. By selecting limited values for the city column filter, the choices are constrained to cities in France only, which prevents the possibility of choosing mutually exclusive filters, such as Country=Italy and City=Paris. Limiting values might require more time to process the prompt compared to the two other options.

     SQL Results. To limit the values to the results with an SQL statement, click the option SQL Results and type the SQL statement into the text box. When you use an SQL Results prompt, the following conditions apply:

     • You cannot chain SQL Results prompts. For example, if you have two SQL Results prompts, the filtered values from one prompt do not affect the other prompt.
     • If the first prompt is an SQL Results prompt and the second prompt is a normal prompt, then the result of the second prompt is limited to the results of the first prompt if the second prompt has the option, Filter Limited Values, selected. If the first prompt is a normal prompt and the second prompt is an SQL Results prompt, then the two prompts cannot be chained.
     • Non-SQL Results prompts can always be chained regardless of the order of the prompts.
     • The maximum number of values available for the prompt drop-down list is 1,000. If the SQL result returns more than 1,000 records, then only the first 1,000 records are displayed.
8. Make selections for the options in the Other options area:
   • To allow the user to constrain the filter choices, select the Allow user to constrain choices check box. If this check box is selected, the users are presented with a picklist where they can use conditions (such as begins with, ends with, and contains) to constrain the choices.

     Constrained choices help the user locate the values to use as a filter. For example, if a filter contains all the cities in France, a user can constrain the filter choices to those cities starting with P.

   • To allow a user to skip this column filter, select the Allow user to skip prompt check box.

     This causes a Skip Prompt option to appear. The user can then click this option to bypass making a selection for this filter.

9. When you are finished, click OK.

   The column filter prompt is shown on the Define Prompts page.

10. To view the column filter prompt, click the Preview Analysis button.

To modify the properties of a column filter prompt

1. Click the properties button for the prompt.
2. In the Prompt Properties dialog box, make your changes, and then click OK.

To move a column filter prompt up or down in the execution order

• Click the up and down arrows until the prompts are in the order that you want.

  The up and down arrows appear only when there is more than one prompt for the analysis.

To delete a column filter prompt

• Click the delete button for the prompt.