Filters can be defined at three levels:
| • | In the Domain Designer. |
| • | When creating a view from a Domain (in the Data Chooser). |
| • | In the Ad Hoc Editor (even when the view is based on a JRXML Topic or OLAP connection). |
In this section, we discuss how to define filters in the Ad Hoc Editor. For information on defining filters in the Domain Designer, see The Pre-filters Tab. For information on defining filters in the Data Chooser, see The Pre-filters Page.
In addition, you can control how and what filters are applied to a field or fields by using advanced expressions. For more information, see Advanced Filtering.
To create a filter in the Ad Hoc Editor:
| 1. | Right-click a field in the Data Source Selection panel and select Create Filter. |
A new filter appears in the Filters panel. If the Filters panel was hidden, it appears when you create a new filter.
| If the results are empty and you don’t understand why, check for an incompatible combination of filters and input controls. Click |
| 2. | Use the fields in the filter to change its value. Depending on the datatype of the field you selected, the filter maybe multi-select, single-select, or text input. |
| 3. | Click |
| 4. | Click |
| 5. | Click |
| 6. | Click the Select All check box (if it appears in the Filters panel) to select all values currently available in the dataset. The Select All check box does not appear in the Filters panel for numbers and dates. |
Note that 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 filter's new value to determine what data to display. If you change only the operator in a filter, you must deselect the value in that filter, then reselect it to apply the updated filter.
For filters with multiple values, you do not need to reselect all values. After changing the operator, use Ctrl-click to deselect just one of the values, 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 accomplish this using date-based filters, and entering a text expression describing the relative date or date span you want to display, using the format <Keyword>+/-<Number>.
| • | Keyword indicates the time span you want to use. Options include: DAY, WEEK, QUARTER, SEMI, and YEAR. |
| • | + or - indicates whether the time span occurs before or after the chosen date. |
| • | Number indicates the number of the above-mentioned time spans you want to include in the filter. |
For example, if you want to look at all Sales for the prior week, your expression would be: WEEK-1.
To create a relative date filter:
| 1. | Following the instructions in Using Filters, create a filter based on a date field. The filter appears in the Filters panel. |
| 2. | In the filter’s first text entry box, enter an expression describing the relative date or date span you want to display. |
| 3. | In the filter’s second text entry box, enter the date you want to base your filter on. |
For instance, if you want to display all the sales numbers for the month prior to the current date, the expression in the first text entry box would say MONTH-1, and in the second box you would enter today’s date.
| When you right-click a group member in a crosstab and select Keep Only or Exclude, you create complex filters. When you create a filter against an inner group, the filter that appears may be created as a complex filter; a complex filter can’t be edited but it can be removed. Complex filters also appear in the Ad Hoc Editor if a Data Chooser wizard filter was created and locked. |
Advanced 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 your filters are applied.
However, with the advanced filter functionality, you can exercise greater control over the displayed data by applying an advanced expression that includes more complex, nested AND, OR, and NOT operators, as well as by applying multiple filters to a single field.
| Advanced filters are not available for Ad Hoc views created from OLAP connections. |
Advanced filters are useful in a number of situations, including:
| • | When using the AND operator isn’t sufficient. Consider an international company that wants to view data for stores located on the Pacific Rim; they may create an advanced expression with the following criteria: |
AND
| • | State is California OR Washington OR Oregon OR Hawaii OR Alaska. |
OR
| • | Country is Japan OR Indonesia |
Using the AND operator for all of these criteria returns an empty view, as no store is located in all of those areas.
| • | When you need to eliminate some results in a field. For example, if your food and beverage distribution company wants to view sales for all drinks except for high-price items, you might include the following criteria in an advanced expression: |
| • | Product Group is Beverages |
NOT
| • | Price is greater than 39.99 |
This filter displays all items in the Beverage Product Group, but filters out those with prices over $39.99
These are only two scenarios where advanced filters can hone your results and make your view more precise. There are, of course, many other situations where they can be applied.
In this section, we take you through these tasks:
| • | Creating an advanced expression |
| • | Editing an advanced expression |
| • | Removing an advanced expression |
| • | Applying multiple filters to a single field |
| Advanced filters are applied to views, but filter details don’t appear on previews, or on the report generated from that view. |
To create and apply an advanced filter:
| 1. | Create two or more filters for your data, as described in Using Filters. These can be standard field-based filters, or Keep Only and Exclude filters. |
Note that, as you create the filters for use in an advanced expression, you may find that the data in your view disappears, since most (if not all) of the data won’t meet all of the filter criteria. When you create your advanced expression and change some of the ANDs to ORs and NOTs, data reappears in the panel.
| 2. | At the top of the Filters panel, click |
| 3. | In the Filter Expression, for pane, click the Edit button. The Edit Advanced Filters dialog appears. |
| 4. | In the Edit Advanced Filters dialog, use the filter preview window to locate the filters, and note the letter designations they have been given. |
| Filter letter designations are case sensitive, and must be CAPITALIZED. |
| 5. | Enter a filter expression using the letter designations, 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. |
| 6. | Check your expression by clicking the Validate button. If your expression works with the available filters, you receive a green “Success” validation message; an incorrect expression receives a red invalid message with a brief description of the problem. |
| 7. | When your expression is validated successfully, click Submit. Your view is updated to reflect the newly-applied filter criteria. |
After creating an advanced filter, you may want to add another filter to the expression, or remove one already included in the expression.
| If the simple filter you want to delete is part of an advanced filter, you must first remove it from the advanced filter expression; otherwise, deleting the filter deletes the advanced filter expression. |
To add a new filter to an existing advanced expression:
| 1. | If necessary, create the new filter in the Filter panel. |
| 2. | In the Filter Expression, for pane, click Edit to open the Edit Advanced Filters dialog. |
| 3. | In the Edit Advanced Filters dialog, add the new filter to the expression. Any unused filters appear in the filter preview window highlighted in gray. |
| 4. | Click Validate to check the integrity of the new expression. |
| 5. | When your expression is validated successfully, click Submit. |
To remove a filter from an advanced expression:
| 1. | In the Filter Expression, for pane, click Edit to open the Edit Advanced Filters dialog. |
| 2. | In the Edit Advanced Filters dialog, remove the unwanted filter from the expression, and adjust the expression as needed. |
| 3. | Click Validate to check the integrity of the new expression. |
| 4. | When your expression is validated successfully, click Submit. |
When working with advanced expressions, you may decide to delete an expression and create a new one.
To remove an advanced expression from a view:
| 1. | In the Filter Expression, for pane, click Edit to open the Edit Advanced Filters dialog. |
| 2. | Clear the expression from the Filter Expression text box. |
| 3. | Click Submit. The expression is removed, leaving the remaining filters intact. |
When you refine your advanced expression, you may also want to delete unused filters from the Filters panel.
| • | If the filter you want to remove isn’t part of the advanced filter, hover your mouse over |
| • | If you want to remove all existing filters, including the advanced expression, hover your mouse over |
You can apply multiple simple filters to a single field, if needed, to further refine your advanced filter results. For example, a user may want to view the data in the Shipping Cost field, but only when it meets certain criteria combinations:
| • | When shipping costs to French cities with postal codes that begin with the number 5 are under five Euros |
| • | When shipping costs to German cities with postal codes that begin with the number 1 are under five Euros |
| You can recreate the scenario below using the demo for adhoc topic. |
In the following example a user has a table including the following columns:
To analyze the specific shipping costs described above, the user creates the following (simple) filters - including two filters each for the Country and Postal code fields:
| • | A. Country equals France |
| • | B. Postal code starts with 5 |
| • | C. Country equals Germany |
| • | D. Postal code starts with 1 |
| • | E. Shipping Charge is less than 5 |
Then, to display only the information she needs, she creates the following advanced expression:
| • | ((A and B) or (C and D)) and E |
This translates to:
| • | ((FRANCE and POSTAL CODES THAT START WITH 5) or (GERMANY and POSTAL CODES THAT START WITH 1)) and SHIPPING CHARGES LESS THAN 5 EUROS. |
Recommended Comments
There are no comments to display.