2.8 Using Filters and Input Controls

Domains use different mechanisms for screening the data.

  • Domains (and Domain Topics) can be filtered by selecting fields in the Domain and specifying comparison values. The filters can be configured for users to select the data.

  • Within the Domain design, filters based on conditions can also be defined; these filters are not displayed in the report viewer when the report runs.

You can define filters in the Ad Hoc Editor whether you are working with data from a Domain or Topic. Such filters are helpful in improving the view's initial performance by reducing the amount of data the view returns by default.

To prevent users from seeing the full dataset, you can also use input controls in a JRXML Topic or filters defined in the Domain design, which can be hidden from end users. For more information, see Section 2.8.3, Input Controls and Filters Availability.

Input controls and filters interact smoothly. For example, you can create filters in an Ad Hoc View that gets data from a JRXML Topic that includes input controls.

The server refreshes the editor against both the filters and the input controls. Because some combinations of input controls and filters do not return data, this can result in an empty view.

NOTE:If the result set is empty, check for an incompatible combination of filters and input controls, such as a standard filter and a Keep Only filter, or an incorrectly-defined custom filter expression. (Data must meet all criteria in multiple filters, rather than meeting criteria in a subset of those filters). For more information, see Custom Filtering.

JRXML is an XML file format for saving and sharing reports created for the ZENworks Reporting and the applications that use it, such as iReport Designer and ZENworks Reporting. JRXML is an open format that uses the XML standard to define precisely all the structure and configuration of a report.

In some cases, filters can conflict with view parameters, and you must rename the field that is causing the conflict by editing the JRXML file.

2.8.1 Using Filters

You can define filters in the following places:

  • The Data Chooser (While creating a view from a Domain).

  • The Ad Hoc Editor (even when the view is based on a JRXML Topic).

For information about defining filters in the Data Chooser, see Pre-filtering data.

In addition, you can control how and what filters are applied to a field or fields by using custom expressions. For more information, see Custom Filtering.

To create a filter in the Ad Hoc Editor:

  1. Right-click a field in the Data Source Selection panel, then click Create Filter.

    A new filter displays in the Filters panel. If the Filters panel was hidden, it appears when you create a new filter.

    If the results are empty, check for an incompatible combination of filters and input controls. Click to compare input controls with the filters in the Filters panel.

    For Oracle database, you can use the “Null” value while creating a filter. For MS SQL and Sybase databases, you can use blank.

  2. Use the fields in the filter to change its value.

    Depending on the selected data type, the filter can be multi-select, single-select, or text input.

  3. Click , then select Minimize All Filters or Maximize All Filters to toggle expansion of the items in the filter.

  4. Click , then select Remove All Filters to remove the filters.

  5. Click to hide the filter's details.

  6. Click to view the filter’s details.

  7. Click the Select All check box (if it appears in the Filters panel) to select all values in the dataset.

    The Select All box does not appear in the Filters panel for numbers and dates.

NOTE:The Select All check box doesn’t guarantee that all values are selected every time the report runs. Instead, the check box is a shortcut to help you quickly select all the values currently available in the dataset. To ensure that all values appear in the view whenever it is edited or a report is run, remove the filter entirely. On the panel, you can also create a filter from the right-click context menu of a column in a table. On the Chart tab, you must right-click the field in the Data Source Selection panel

When you change a filter, the server uses the new value to determine the data to display. If you change only the operator in a filter, you must deselect the value in that filter and then reselect it to apply the updated filter.

For filters with multiple values, you need not reselect all values. After changing the operator, use Ctrl+click to deselect one of the values and then Ctrl+click to reselect that value.

Relative Dates

You can filter information in your view based on a date range relative to the current system date. You can view this by using date-based filters and specifying a text expression describing the relative date or date span that you want to view, by using the format <Keyword>+/-<Number>.

  • Keyword indicates the time span that you want to use. Options include: DAY, WEEK, QUARTER, SEMI, and YEAR.

  • + or - indicates whether the time span occurs before or after the selected date.

  • Number indicates the number of the time spans you want to include in the filter.

To create a relative date filter:

  1. Follow the instructions in the Section 2.8.1, Using Filters.

  2. In the filter's first text box, specify an expression describing the relative date or span date.

  3. In the filter's second text box, specify the date that you want to base your filter on.

To create a complex filters:

  1. Right-click a group member in a crosstab, then select Keep Only or Exclude.

    When you create a filter against an inner group, the filter that appears might be created as a complex filter.

    A complex filter cannot be edited, but can be removed. Complex filters also appear in the Ad Hoc Editor if a Data Chooser wizard filter was created and locked.

Custom Filtering

When you create multiple filters they are, by default, connected with an implicit AND operator; that is, the data displayed in your table, chart, or crosstab is what remains after all of your filters are applied.

However, with the custom filter functionality, you can control the displayed data by applying a custom expression that includes more complex, nested AND, OR, and NOT operators, as well as by applying multiple filters to a single field.

NOTE:Custom filters are not available for Ad Hoc views created from OLAP connections.

Custom filters are useful in situations such as the following:

  • When using the AND operator isn't sufficient. For example: Consider an international company wants to check the data for non-licensed Windows OS products deployed at their Wall Street offices. In this example, the user has table that include the following columns:

    • Site

    • OS Product Name

    • License Type

    To analyze the specific license data described above, the user creates the following (simple) filters - including two filters for the License Type field:

    • A): Site equals Wall Street

    • B): OS product name contains Windows

    • C): License type equals Unknown

    • D): License type equals Eval

    Then, to display only the required information, the following custom expression is created:

    • A and (B and (C or D))

    This translates to:

    • (Site is Wall Street) and ((OS Product name contains Windows) and ((License Type is Unknown) or (License Type is Eval)))

  • When you need to eliminate some results in a field.For example, if the company wants to check the data for all but fully licensed Windows OS products, a user has a table including the following columns:

    • OS Product Name

    • License Type

    To analyze the specific license data described above, the user creates the following (simple) filters:

    • A): OS product name contains Windows

    • B): License type equals full

    Then, to display only the required information, the following custom expression is created:

    • A and (not B)

    This translates to:

    • (OS Product name contains Windows) and (not (License Type equals Full))

These are only two scenarios where custom filters can complete your results and make your view more precise. There are many other situations where they can be applied.

Other custom filter tasks include the following:

  • Creating a custom expression

  • Editing a custom expression

  • Removing a custom expression

  • Applying multiple filters to a single field

    NOTE:Custom filters are applied to the Views, but filter details do not display on previews or on the report generated from that view.

To create and apply a custom filter:

  1. Create two or more filters for your data, as described in Section 2.8.1, Using Filters. These can be standard field-based filters or Keep Only and Exclude filters.

    As you create the filters for use in a custom expression, the data in your view disappears, since most of the data does not meet all of the filter criteria. When you create your custom expression and change some of the ANDs to ORs and NOTs, again the data appears.

  2. At the bottom of the filters panel, expand the Custom Filter Expression section.

  3. In the text entry box, enter a filter expression using the letter designation, and including the following operators:

    • AND narrows your results and includes only fields that meet the criteria of both filters before and after the operator.

    • OR broadens your results and includes fields that meet the criteria of either filter before or after the operator.

    • NOT excludes results that match the criteria.

    • Parentheses combines multiple filters into a single item in the expression.

      NOTE:Filter letter designations are case sensitive, and must be UPPERCASE.

  4. Click Apply. Your view is updated to reflect the newly-applied filter criteria.

After creating a custom filter, you can add another filter to the expression or remove existing expression.

NOTE:If the simple filter you want to delete is part of a custom filter, you must first remove it from the custom filter expression; otherwise deleting the filter deletes the custom filter expression.

To add a new filter to an existing custom expression:

  1. Create a new filter in the Filter panel.

  2. In the Custom Filter Expression, click inside the text entry box to edit the expression.

  3. Add a new filter to the expression.

  4. Click Apply to apply the new criteria.

To remove a filter from a custom expression:

  1. Expand the Custom Filter Expression section.

  2. In the text entry box, remove the unwanted filter from the expression and adjust the expression as needed.

  3. Click Apply to apply the new criteria.

When working with custom expressions, you can delete an existing expression and create a new expression.

To remove custom expression from the view:

  1. Expand the Custom Filter Expression section.

  2. Clear the expression from the text entry box.

  3. Click Apply. The expression is removed, leaving the remaining filters intact.

When you refine your custom expression, you can delete unused filters from the Filters panel.

  • If you want to remove the filter from the custom filter, place your mouse on in the Filter's title bar, then click Remove Filter.

  • If you want to remove all existing filters, including the custom expression, place your mouse on , in the Filters panel, then click Remove All Filters.

2.8.2 Using Input Controls

In the Ad Hoc Editor, you can view the input controls defined in the Topic as visible to users. You can accept the default values of the controls or enter other values. The Ad Hoc Editor indicates that the view has input controls by displaying the icon as active on the tool bar. Click the icon to select new values or to save values as the new defaults for this view.

To add an input control to the view using a filter:

  1. Create a new filter, or use an existing filter in the Filters panel.

  2. In Filters, click next to the new filter, then select Show Operator.

  3. Select an operator from the drop-down and specify the value in the text box.

    The filter displays as an input control when the view is used to run the report.

  4. Place the cursor on , then select Save Ad Hoc View as....

  5. Name the view, select a location, then click Save.

  6. On the tool bar, click .

    Only the input controls defined in the topic are displayed. If no input controls were defined in the topic, the button is grayed out.

To edit the values for a view's input controls:

  1. On the tool bar, click .

    A window listing the input controls defined in the Topic displays.

  2. Select new values.

  3. To change default values of input controls, select the check box labeled Set these values as defaults when saving your view.

    The selected values become the default values when you save the view.

  4. Click OK.

    The Ad Hoc View displays the updated data.

2.8.3 Input Controls and Filters Availability

Input controls and filters display in the Editor and when you run a report.

  • Input controls can be set to be visible or invisible when you edit a view:

    • Input controls that are set to Always prompt are displayed in the editor and always appear before the report is run.

    • Input controls that are not set to Always prompt are hidden in the editor and appear when the report is run.

  • Filters created in the Data Chooser wizard can be locked or unlocked:

    • Filters that are unlocked display filter details in the editor and are available from the Options button when the report is running.

    • Filters that are locked display input controls in the Editor when you click this icon . However, they are not available from the Options button when the report is running. You can remove the filter from the Editor, allowing them to see all the unfiltered data, when the report is running.

  • Filters defined in the editor are always available in the Filters panel of the editor and from the Options button when the report is run.

When setting up input controls for a large view that takes a long time to run, consider setting the view to Always prompt. Before a report is run, the report viewer prompts you to provide the input options, preventing the report from running with the default input options.

Filters that are unlocked are available. When input controls or filters do not display in the report viewer, click the Options button to view them.

To set an input control to prompt:

  1. Locate a Topic, in the repository, then click Edit.

  2. In the Controls & Resources panel, under Input Control Options, click Always prompt.

To determine whether an input control is visible:

  1. Locate a Topic, such as the Parameterized Report Topic, in the repository, then click Edit.

  2. On the Controls & Resources panel, click the name of an input control.

  3. On the Locate Input Control panel, click Next.

    At the bottom of the Create Input Control panel, if the Visible check box is selected, the input control displays on the report when it runs.

    If you do not specify a default value for the input control, users are prompted to select a value when they create a View based on the Topic.

To lock a filter:

  1. In the Data Chooser, after selecting the fields, Click Pre-filters.

  2. Double-click a field in the Fields panel.

  3. In the Filters panel define a filter.

    For more information, see Pre-filtering data.

  4. Select the Locked check box, then click OK.

  5. Click Table to open the Ad Hoc Editor.

    In the Filters panel, the name of the filter and a note about the lock displays under the Locked heading.