Filter data

1. Select and right-click the column in the table for which you want to filter data.
2. Select Filter > Filter. In the Filter dialog box, the Filter By field displays the column on which to apply the filter.
3. If necessary, select Static as the type of filter.
4. In the Condition field, select the comparison test, or operator, to apply to the selected column. Depending on the operator you select, one or two Value fields or a completed filter condition appear.
5. If you select an operator that requires a comparison value, you can specify the value in one of the following ways:
   • In the Value field, type the value.
   • To select from a list of values, click Select Values.
   • If the list of values is long, type a value in Filter Text and click Find. If found, Report Studio displays this value in the list. When you select the value, it appears in Value.
   • To specify null values, select No Value.
   • If you are filtering a a summary table or a detail table containing aggregate data, you can recalculate the aggregate data values for the retrieved data rows by selecting Recalculate Totals. To calculate totals for all the data rows in the table, deselect Recalculate Totals.
5. Click OK. The filter takes effect. In cases where the sample data in the report design does not meet the filter condition, the report design does not display any data and Report Studio sends an error message. Always test a filter by running and viewing the generated report in the Viewer.

<context-param>
<param-name>FilterValueDelimiter</param-name>
<param-value>,</param-value>
</context-param>

When you create a new report design, Report Studio provides the Table Builder wizard to help you select data fields and create a data set filter. After these data fields appear in the report design, you can no longer invoke Table Builder. Instead, you use the Advanced Filter to create or modify a data set level filter condition. When you select Filter > Slicer Filter from the context menu of the table handle, the filter condition you specify using Advanced Filter is evaluated at the data set level.

Create a data set filter

1. In Data, select the data set and then select Filter. The Advanced Filter dialog box appears.
2. To create a filter condition, complete the following steps:
   1. In the Filter By field, select a column from the drop-down list.
   2. In the Condition field, select an operator. Depending on the operator, one or two value fields appear, or a completed condition.
   3. In the Value fields, do one of the following:
   4. Enter a value.
   5. Click Select Values, then select a value from the list of values that appears.
   6. Use the calendar tool to select a date.
   7. Select No Value to choose a null value.
   4. Click Add Condition. Conditions displays the new condition.
   5. Click Validate to verify the syntax of the new condition.
   6. Click Add Filter. Filters displays the condition.
3. Click OK. Report Studio displays rows that meet the filter condition in the report design.

Define multiple filter conditions

You can add as many filters as you want, but each condition narrows the scope of data further, and adds complexity to the filter. Design and test filters with multiple conditions carefully. As you add filter conditions, Report Studio inserts the logical operator AND between each filter expression. You can change this operator to OR. You can also add the NOT operator to either the AND or the OR operator to exclude a small set of data.

When you define more than two conditions, you can use parentheses to group conditions to get the results you expect. For example, A AND B OR C is evaluated in that order, so A and B must be true or C must be true to include a data row. In A AND (B OR C), B OR C is evaluated first, so A must be true and B or C must be true to include a data row.

Use the Advanced Filter to specify multiple filter conditions. Advanced Filter also displays all the filters for the table. The procedure to modify and delete filter conditions in the Advanced Filter is the same as when modifying and deleting data set level filter conditions in the Advanced Filter.

When creating filter conditions for a summary table that uses a data model data source, the following conditions apply:

• You cannot use Advanced Filter to filter data in a measure column.
• You cannot use Advanced Filter to filter data in dimension and attribute columns if the summary table already contains a measure column with a defined filter condition.
• You can use Advanced Filter to create filters for dimension and attribute columns in a summary table using a data model data source, if no filter condition is defined on a measure column.

To define multiple filter conditions:

1. Select and right-click the column that contains the values to filter by, then select Filter > Filter.
2. In the Filter dialog box, select Advanced Filter.
3. In the Advanced Filter dialog box, select Static, if necessary. The selected column is selected in Filter By field. To use a different column, select a new column from the drop-down list.
4. Define a filter condition as follows:
   1. In the Condition field, select a comparison operator.
   2. Specify a value.
   3. Click Add Condition.
   4. The filter condition appears in the Conditions area.
5. Define the next filter condition as follows:
   1. In the Filter By field, select another column.
   2. Repeat step 4.
   3. In the Conditions area, the second filter condition appears after the first condition. By default, the second condition is preceded by the logical operator, AND.
6. Select a different logical operator, if necessary.
7. If the report design contains aggregate data, do one of the following:
   • To recalculate aggregate data values across the filtered data rows, select Recalculate Totals, if necessary.
   • To calculate aggregate data for the unfiltered data, deselect Recalculate Totals.
8. To add additional filter conditions, repeat step 4.
9. If you create more than two filter conditions and you use different logical operators, you can use the parentheses buttons to group conditions to determine the order in which conditions are evaluated.
10. Click Validate to verify the syntax of the filter conditions, then click Add Filter.
11. The defined filter conditions appear in the Filters area
11. Click OK.
12. Run the report to verify that it displays the expected results.

A static filter parameter is a filter condition that the report developer defines, which prompts the user to specify one or more values when running a report. The filter condition typically consists of an operator, specified by the report developer, and corresponding values to be specified by the report user when running the report.

A dynamic filter parameter differs from a static filter parameter in one important respect: the report developer can provide report users with a list of operators to use to construct a filter condition. The user selects an operator, then specifies one or more corresponding values when running the report. The report displays data for the filter parameter condition the user constructs.

When you create a parameter, you can require the user to specify a value, or you can make the value optional. If you make the value optional, the user can view the report with all the data. For example, the Parameters dialog box contains a list of cost centers to choose from. If a parameter is not required, the user can leave the field blank to view the data for all the cost centers.

You can require the user to supply a value if displaying all the data results in a very long report. A report that runs into hundreds of pages is not only difficult to read, but takes longer to generate. In Report Studio, a dynamic filter parameter is always optional.

To make a parameter value optional:

1. In the Filter dialog box, select Link to parameter.

2. Click Create New Parameter.

3. Deselect Is Required.

Users often want to select several values for a filter condition. To support the selection of multiple values, create a filter parameter as follows:

• Select List Box as the display type.
• Select the In operator as one of the operators to provide to the user.
• Create a list of values.

When specifying properties for a static filter parameter, you can select the display types shown below for the list of values. When specifying properties for a dynamic filter parameter, you can specify List Box or Text Box as the display type.

To create a useful parameter, provide the user with a list of values from which to choose. In some cases, providing a list of values, such as employee names or cost centers, is necessary.

You create a list of values using one of the following techniques:

• Create a static list of values.
• In a static list, the values you select to display to the report user are fixed in the report design. You can either select from a list of values, or type values to populate the list of values. Create a static list if you want to control the list of values displayed to the report user, for example, if you want to display only some of the values.
• Create a dynamic list of values.
• In a dynamic list, the system generates the list of values when the report runs, using the current values in the table or the data set. You can create a dynamic list of values at the data set level, or at the table level. Create a dynamic list for values that are frequently updated in the data source, such as new employee or cost centers.

1. Select and right-click the column that contains the values for which you want to create a static prompt, then select Filter> Filter.
2. In the Filter dialog box, select Link to parameter.
3. In the Condition field, select an operator.
4. Click Create New Parameter.
5. In the Edit Parameter dialog box, complete the following steps:
   1. In the Parameter Name field, type the parameter name.
   2. In the Prompt Text field, type the text to display.
   3. In the Help Text field, optionally type a tooltip to assist the user in selecting the values for which Report Studio displays data.
   4. Select Is Required. To make the parameter optional, deselect Is Required.
   5. In the Display Type field, select an option from the list.
   6. Click Select Values to select the values to make available to the report user when the report runs. Select a value from the list of values that appears in Find, then press Ctrl and select each additional value to display.
   7. If you selected Text Box as the display type, set a default value. If you do not specify a default value, the first value you selected is set as the default value. When finished, click OK.
6. If the report contains aggregate data, do one of the following:
   • To recalculate aggregate data values across the filtered data rows, select Recalculate Totals, if necessary.
   • To calculate aggregate data for the unfiltered rows, deselect Recalculate Totals.
7. When finished, click OK. The filter condition takes effect.

When you create a static filter parameter and select an available display type other than Text Box, you can create a static or dynamic list of data values to provide to the user. To create a static filter parameter with a list of values complete the following steps in the Edit Parameter dialog box:

1. In the Parameter Name field, type the name of the parameter.
2. In the Prompt Text field, type the display text for the prompt.
3. In the Help Text field, optionally type a tooltip to help the user select values for which Report Studio displays data.
4. To make the parameter required, select Is Required. To make the parameter optional, deselect Is Required.
5. In the Display Type field, select an option other than Text Box from the list of options.
6. In the List of Values section, do one of the following:
   • Select Static to create a static list of values that the report developer specifies. If you select Static, go to step 7.
   • Select Dynamic to create a list of values that is retrieved from the data set when the report runs. If you select Dynamic, go to step 9.
7. To select the values to make available to the report user when the report runs, select Select Values.
8. In the Find field, select a value from the list of values, then press Ctrl and select any additional values to display. The specified values appear in the Selected Values list.
8. From the Selected Values list, select a value to set as the default value.
   • If the parameter is optional, you do not need to specify a default value.
   • If the parameter is optional, No Value appears in the Edit Parameter dialog box. Select No Value to display data rows that have null values.
9. Click OKand go to step 11.
9. From the Data Set drop-down list, select Current column (No Data Set) to create the list of values from the table or select an available data set to create the list of values from the data set.
10. If you selected the current column, do the following:
    • Click Select Values. In the Find field, select a value from the list of values, then press Ctrl and select each additional value to display. The first value you select is set as the default value.
    • In the Sort area, optionally select Ascending or Descending in the Sort Direction field. If you do not specify a sort direction, Report Studio displays the default value first, and arranges the remaining values in ascending order below the default value.
11. Click OK and go to step 11.
10. If you selected an available data set, do the following in Data Set:
    • In Select Value Column, select the column whose values are filtered according to the parameter condition.
    • In Select Display Text, select a column from the drop-down list, containing the values displayed to the user. For example, you can choose to display the sales office cities for the user to choose from, instead of the office code values.
    • Choose Select Values. In Find, select a value from the list of values that appears, then press Ctrl and select the remaining values you want to display. The first value you select is set as the default value. Alternatively, use the method described in “How to select multiple values for a filter condition,” earlier in this chapter.
    • In Sort, specify a field by which to sort the list of values.
    • In Sort Direction, select Ascending or Descending from the drop-down list. If you do not specify a sort direction, Analytics Studio displays the default value first, and arranges the remaining values in ascending order below the default value.
11. Click OK.
11. In Filter, click OK.
12. To test the prompt in Report Studio, select Data > Parameters. The Parameters dialog box displays the prompt.

1. In Data, select the data set and then select Filter.
2. In the Advanced Filter dialog box, select a column from the Filter By drop-down list.
3. Select an operator from the Condition drop-down list.
4. Select Link to parameter.
5. Select Create New Parameter.
6. In the Edit Parameter dialog box, create the parameter as you did earlier in this topic, then click OK.
7. Select Add Condition. The condition appears in the Conditions field.
8. Select Add Filter. The static filter parameter condition appears in the Filters field. Click OK.

To test the prompt in Report Studio, select Data > Parameters.

1. Select and right-click the column for which you want to create a parameter. Select Filter > Filter. The Filter dialog box appears and the selected column appears in Filter By.
2. Select Link to parameter.
3. Select Create New Parameter. In the Edit Parameter dialog box, complete the following steps:
   1. Select Dynamic parameter. The Dynamic Filter Condition dialog box appears.
   2. In the Parameter Name field, type the name of the parameter.
   3. In the Prompt Text field, type the prompt.
   4. In the Help Text field, optionally type a tooltip that assists the user select the parameter value.
   5. In the Display Type field, select List Box.
   6. In the Dynamic Filter Condition, in Operator all operators are selected by default. To select operators from the list, deselect All Operators. Then, press Ctrl and select each operator from the list of available operators.
   7. Select a default operator from the drop-down list.
   8. In the List of Values area, do one of the following:
   9. Select Static to create a static list of values.
   10. Select Dynamic to create a dynamic list of values.
4. In the Edit Parameter dialog box, click OK.
5. If the report contains aggregate data, do one of the following:
   • To recalculate aggregate data values across the filtered data rows, select Recalculate Totals.
   • To calculate aggregate data for the unfiltered data, deselect Recalculate Totals.
6. In the Filter dialog box, click OK.
7. To test the prompt in Report Studio, select Data > Parameters.

1. In Data, select the data set and select Filter.
2. In the Filter By drop-down list, select a column.
3. Select Link to parameter.
4. Select Create New Parameter.
5. In the Edit Parameter dialog box, create the parameter condition.
6. Select Add Filter. The dynamic filter parameter condition appears in Filters. Click OK.
7. To test the prompt in Report Studio, select Data > Parameters.

Note: When working with a dynamic prompt for a column containing numeric data that uses the Match or Not Match operators, if you use the value 1 as the string value to match, you must use double quotation marks (" ") to enclose the value. Type the value as "1" in the text box or combo box, so that Analytics Studio evaluates it accurately as a literal string. If you use other numbers as strings, such as 2, 3, and so on, you do not need to enclose them in double quotation marks.

You can specify multiple static filter parameters, dynamic filter parameters, or a combination of both types. You create multiple filter parameters from the Advanced Filter dialog box.

To create multiple filter parameters

1. Select and right-click the first column for which you want to create a parameter. Select Filter> Filter. The Filter dialog box appears with the selected column in the Filter By field.
2. To define a static filter parameter, complete the following steps:
   1. In the Condition field, select an operator.
   2. Select Link to parameter.
   3. Select Create New Parameter.
   4. Create a static filter parameter as described in Create a static filter parameter with a list of values.
   5. In Filter, select Advanced Filter.
   6. In the Advanced Filter dialog box, select Add Condition. The filter parameter condition you previously specified appears in the Conditions area in Advanced Filter.
   7. Create additional conditions if necessary.
   8. Select Add Filter.
3. To define a dynamic filter parameter, complete the following steps in the Advanced Filter dialog box:
   1. In the Filter By drop-down lis, select a column.
   2. Select Link to parameter.
   3. Select Create New Parameter.
   4. Create a dynamic filter parameter as described in Create a dynamic filter parameter at the table level
   5. Select Add Filter.
4. If the report contains aggregate data, do one of the following:
   • To recalculate aggregate data values across the filtered data rows, select Recalculate Totals.
   • To calculate aggregate data for the unfiltered data, deselect Recalculate Totals.
5. Click OK.
6. To test the parameters in Report Studio, Select Data > Parameters. Parameters displays the prompts with the conditions for which the user can supply values.