Filter data
A data set can provide more information than your report needs. You can select specific data rows to use in a report by using a filter. Filtering data helps you work effectively with large amounts of data to find the pieces of information that answer specific business questions. Filtering data in a report design can measurably reduce the load on the database server, improving processing speed, because it reduces the number of data rows retrieved when the report is run.
Report Studio supports the use of static filters and dynamic filters.
- Use a static filter to define a filter condition at report design time. The data displayed in the report depends on the filter condition specified.
- Use a dynamic filter to prompt a user to specify values at run time. The data that appears in the report depends on the values the user specifies when generating the report.
When you use filters in a report design, Report Studio typically evaluates the filter condition against the rows of data previewed in the report design, 20 rows by default. Increase this number to the maximum value of 200 rows when creating filter conditions.
Note: If you use the default preview value of 20 data rows to test a filter condition, Report Studio can fail to return any data that matches the filter you define. Run the report in the interactive viewer to view the actual data, and verify that the result is what you expect.
To create a filter, you define a condition specifying which data rows to display. A filter condition is an If expression that must evaluate to true for a data row to be included.
Examples
If the worked hours is greater than 40
If the location is San Francisco
If the start date is between 4/1/2008 and 6/30/2008
You can filter data at the data set level and at the table level.
- Filtering at the data set level narrows the scope of data retrieved from the data set and can improve design-time performance if the data set contains a large amount of data.
- Filtering at the table level narrows the scope of data displayed in a table and is the typical filtering option. If a report design contains filters at both the data set and table levels, Report Studio executes the filter at the data set level first, then at the table level.
When creating a filter condition, you can specify whether or not the aggregate data in the report is recalculated to meet the filter condition. This feature is useful when comparing the filtered data values with the unfiltered totals, for example, when performing a percentage calculation of the unfiltered aggregate totals.
When you create a filter condition on a column containing Float or Double data type, the In, Not In, Equal to, and Not Equal To operators do not work as expected. To make sure you obtain the results you expect, do one of the following:
- When using the In or Not In operators in a filter condition specified on a computed column, make sure you round the values in the column to a specified number of digits. For example, the following expression rounds the value obtained by three decimal places:
- ROUND([dbo_ITEMS:PRICEQUOTE]*[dbo_ITEMS:QUANTITY]*0.001, 3)
- Use the Between operator in filter conditions in place of the Equal to or Not Equal To operators.
- Select and right-click the column in the table for which you want to filter data.
- Select Filter > Filter. In the Filter dialog box, the Filter By field displays the column on which to apply the filter.
- If necessary, select Static as the type of filter.
- 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.
- 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.
- 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.
When selecting multiple values for a filter condition, be aware of the following:
The filter conditions IN and NOT IN require a list of values, for example IN ('Boston', 'New York', 'Philadelphia'). You can enter a list of values for a filter condition in the Filter or Advanced Filter dialog boxes using either of two methods, the method described above or the method described below.
To add values to a filter condition, type the values in the Enter Values field, separated by a delimiter such as newline, or copy and paste the values. Then, select Add Values.
To remove values from a filter condition, type the values in the Enter Values field, separated by a delimiter such as newline, or copy and paste the values. Then, select Delete Values.
Make sure none of the values in the Value field is selected. Selected values are also deleted when you click Delete Values.
The default delimiter is the newline character. To change the delimiter, you must change the value of the FilterValueDelimiter parameter in AC_SERVER_HOME\modules\BIRTiHub\iHub\web\birtservice\WEB-INF\web.xml. Supported delimiters include comma (,), semicolon (;) and newline. For example, to change the delimiter to a comma (,) modify web.xml as follows:
<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.
- In Data, select the data set and then select Filter. The Advanced Filter dialog box appears.
- To create a filter condition, complete the following steps:
- In the Filter By field, select a column from the drop-down list.
- In the Condition field, select an operator. Depending on the operator, one or two value fields appear, or a completed condition.
- In the Value fields, do one of the following:
- Enter a value.
- Click Select Values, then select a value from the list of values that appears.
- Use the calendar tool to select a date.
- Select No Value to choose a null value.
- Click Add Condition. Conditions displays the new condition.
- Click Validate to verify the syntax of the new condition.
- Click Add Filter. Filters displays the condition.
- Click OK. Report Studio displays rows that meet the filter condition in the report design.
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:
- Select and right-click the column that contains the values to filter by, then select Filter > Filter.
- In the Filter dialog box, select Advanced Filter.
- 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.
- Define a filter condition as follows:
- In the Condition field, select a comparison operator.
- Specify a value.
- Click Add Condition.
- The filter condition appears in the Conditions area.
- Define the next filter condition as follows:
- In the Filter By field, select another column.
- Repeat step 4.
- 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.
- Select a different logical operator, if necessary.
- 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.
- To add additional filter conditions, repeat step 4.
- 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.
- Click Validate to verify the syntax of the filter conditions, then click Add Filter.
- The defined filter conditions appear in the Filters area
- Click OK.
- Run the report to verify that it displays the expected results.
You can create a parameter that accepts a value or multiple values for a filter when a report is run. A filter parameter enables users to control the content of a report without editing the report.
In Report Studio, you can create a static or dynamic filter parameter at the data set level or at the table level.
To construct a filter parameter, complete the following tasks:
- Select a filter parameter type.
- Define a condition.
- Specify whether the parameter is required or optional.
- Specify a display type.
- Specify a list of values from which a user can select values. You can specify a static list or a dynamic list. You can create a dynamic list of values at the data set level or at the current column level.
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.
Option | Description | Use |
---|---|---|
Combo box | Provides a combination of a drop-down list and a text box, where the user can select a value from the list or type a value | Enables the user to select one or more values, or type a specific value for which Report Studio displays data. |
List box | Displays available values in a drop-down list | Enables users to select one or more values. If you use the IN operator, the list box is the only available display type. |
Radio button | Displays the values as radio buttons | Enables the user to select one value at a time. This option enables you to provide the user with a limited number of values from which to choose. |
Text box | Requires that the user type a value | Enables the user to type a value for which Report Studio displays data. |
Text box - Auto Suggest | Provides the user with the available values that match a certain number of typed characters | Activate auto suggest after any number of typed characters. For example, if you activate auto suggest after one character, and if the user types the letter S, all the values beginning with that character appear in a list from which the user selects a value. In case there is no match, Report Studio displays the message No Suggestions. |
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.
You can create a static filter parameter at the table and data set level.
- Select and right-click the column that contains the values for which you want to create a static prompt, then select Filter> Filter.
- In the Filter dialog box, select Link to parameter.
- In the Condition field, select an operator.
- Click Create New Parameter.
- In the Edit Parameter dialog box, complete the following steps:
- In the Parameter Name field, type the parameter name.
- In the Prompt Text field, type the text to display.
- In the Help Text field, optionally type a tooltip to assist the user in selecting the values for which Report Studio displays data.
- Select Is Required. To make the parameter optional, deselect Is Required.
- In the Display Type field, select an option from the list.
- 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.
- 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.
- 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.
- 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:
- In the Parameter Name field, type the name of the parameter.
- In the Prompt Text field, type the display text for the prompt.
- In the Help Text field, optionally type a tooltip to help the user select values for which Report Studio displays data.
- To make the parameter required, select Is Required. To make the parameter optional, deselect Is Required.
- In the Display Type field, select an option other than Text Box from the list of options.
- 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.
- To select the values to make available to the report user when the report runs, select Select Values.
- 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.
- 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.
- Click OKand go to step 11.
- 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.
- 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.
- Click OK and go to step 11.
- 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.
- Click OK.
- In Filter, click OK.
- To test the prompt in Report Studio, select Data > Parameters. The Parameters dialog box displays the prompt.
- In Data, select the data set and then select Filter.
- In the Advanced Filter dialog box, select a column from the Filter By drop-down list.
- Select an operator from the Condition drop-down list.
- Select Link to parameter.
- Select Create New Parameter.
- In the Edit Parameter dialog box, create the parameter as you did earlier in this topic, then click OK.
- Select Add Condition. The condition appears in the Conditions field.
- 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.
Dynamic filter parameters provide users with more control over the data that they view in a report.
Dynamic filter parameters modify the underlying query, so only data rows that meet the filter criteria are retrieved from the data set.
The basic properties you specify when you create a dynamic filter parameter are similar to the properties for a static filter parameter, with one exception. In the Edit Parameter dialog box, you specify the operators to provide to users, and set a default operator.
Dynamic filter parameters are always optional.
- 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.
- Select Link to parameter.
- Select Create New Parameter. In the Edit Parameter dialog box, complete the following steps:
- Select Dynamic parameter. The Dynamic Filter Condition dialog box appears.
- In the Parameter Name field, type the name of the parameter.
- In the Prompt Text field, type the prompt.
- In the Help Text field, optionally type a tooltip that assists the user select the parameter value.
- In the Display Type field, select List Box.
- 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.
- Select a default operator from the drop-down list.
- In the List of Values area, do one of the following:
- Select Static to create a static list of values.
- Select Dynamic to create a dynamic list of values.
- In the Edit Parameter dialog box, click OK.
- 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.
- In the Filter dialog box, click OK.
- To test the prompt in Report Studio, select Data > Parameters.
- In Data, select the data set and select Filter.
- In the Filter By drop-down list, select a column.
- Select Link to parameter.
- Select Create New Parameter.
- In the Edit Parameter dialog box, create the parameter condition.
- Select Add Filter. The dynamic filter parameter condition appears in Filters. Click OK.
- 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
- 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.
- To define a static filter parameter, complete the following steps:
- In the Condition field, select an operator.
- Select Link to parameter.
- Select Create New Parameter.
- Create a static filter parameter as described in Create a static filter parameter with a list of values.
- In Filter, select Advanced Filter.
- In the Advanced Filter dialog box, select Add Condition. The filter parameter condition you previously specified appears in the Conditions area in Advanced Filter.
- Create additional conditions if necessary.
- Select Add Filter.
- To define a dynamic filter parameter, complete the following steps in the Advanced Filter dialog box:
- In the Filter By drop-down lis, select a column.
- Select Link to parameter.
- Select Create New Parameter.
- Create a dynamic filter parameter as described in Create a dynamic filter parameter at the table level
- Select Add Filter.
- 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.
- Click OK.
- To test the parameters in Report Studio, Select Data > Parameters. Parameters displays the prompts with the conditions for which the user can supply values.
You can create a parameter for a filter in a chart or table. You can then reuse the parameter in other report elements. For example, a report contains a chart that uses Country as the category axis (x-axis) and a table that contains a Country column.
You can create a filter parameter for the chart, and then use the same parameter for the table, or vice versa. In other words, you can use a report parameter in the same way that you use a data selector in a dashboard. You can reuse both static and dynamic parameters. You cannot reuse a parameter in a crosstab, however.
When working with parameters, it is helpful to give report elements descriptive names so that they are easily identifiable in the Manage Parameters dialog box.
You can unlink a filter from a parameter by selecting None in the Manage Parameters dialog box. If you unlink a filter from a static parameter, the chart or column is filtered by the value or values in the parameter’s list of values. For a dynamic parameter, unlinking removes the filter.
- Create a filter parameter for a column in a table or a chart.
- Select a column in another table or select another chart.
- Select Filter.
- In the Filter dialog box, select the column in the Filter By field.
- For a static parameter, select the condition (operator).
- In the Value field, select Link to parameter.
- Select the parameter from the drop-down list. The prompt text you provided for the parameter appears in the Parameter drop-down list. Click OK. The filter is linked to the parameter.
1. Select Data > Manage Parameters.
2. In the Manage Parameters dialog box, select Link Filter.
3. Select None for the appropriate report element.
4. Select Update Filter Parameter. Click Close.
5. Select the chart or column for which you unlinked the filter.
6. Select Filter. If the parameter is a static parameter, the chart or column is filtered on the value or values in the parameter’s list of values. If the parameter is a dynamic parameter, the filter is removed.
1. Select Data > Manage Parameters.
2. In the Manage Parameters dialog box, select Edit Parameter.
3. Edit the parameter as necessary.
4. Click Save Parameter.
5. Click Close.
Deleting a parameter unlinks any filters that use the parameter.
1. Select Data .> Manage Parameters.
2. In the Manage Parameters dialog box, select Delete Parameter.
3. Click Delete Parameter.
4. Click Close.