Custom filters

Use cell references

Cell references can be used anywhere a constant could be used. Cells are read at the time the query is initiated. You can use a text box for typing values or you can use cell ranges, such as J7, $K$9 for cells or H3:H20, $J$5:$M$15 for ranges. Cells can be on another sheet, for example, Sheet2!:B4, Sheet3!D2R2, or even a named range, such as Sheet1!RangeName. Blank cells are ignored and invalid numeric values are converted to zero (0). You can create Exploration Views and lists on one worksheet to be used as part of a filter expression in another worksheet. A cell reference used in a numeric or date comparison that does contain a valid value will result in the use of 0 and the current date respectively.

Match data types

If you create a filter for a particular data type, such as a numeric data type, but enter criteria for another data type, such as a string, you receive an error. In general, you must enter values that are consistent with the data type of the item for which you are creating criteria. For example, if you create a filter for a monetary amount, which is numeric, but enter a string value, such as "Yen" you receive the following error:

Error 901: QE-DEF-0260 Parsing error before or near position: position of: "filter(MUN)"

The following operators are an exception to the data type rule: IS NULL, IS NOT NULL, LAST N DAYS, and IS IN. In the case of these operators, the target, or second, argument is not a type match with the field, or first argument. In the first two, IS NULL and IS NOT NULL, there are no target arguments. None of these operators can use a database reference as a target argument.

OLAP and DMR data sources

For OLAP and DMR data sources in a list report, criteria are limited to the items that you add to the list itself. To enhance filter capabilities, use lists for relational data sources and use Exploration Views for dimensional sources. Be cautious when mixing context dimensions with filters because they may not yield the result you expect.

In nested explorations using OLAP or DMR data sources, you must apply the filter to the dimension on rows or columns that defined the measure value in the Exploration View. For example, you might have an Exploration View with products and order method on rows, years on columns and quantity sold on measures. Because order method is the inside edge of the nested rows (it appears after products in the Rows drop zone), you must create the filter using order method.

Focus relational data

You can limit the data in your report with filters or query parameters. The method you choose should depend on the capabilities of the data source, the performance requirements, the persistence of the dataset, and how complex you want the report to be. In IBM Planning Analytics for Microsoft Excel, you must use detail filters for list reports in the relational reporting style. These filters remove rows and columns from your reports.

Before aggregation

For relational data sources, you may also have the option of applying filters before aggregation. Select the Before aggregation check box if the field is a measure, however this check box is disabled for non-measures or if the condition is at or below a logical OR operator. In that case, the default is enforced where non-measures are automatically before aggregation and all others are after aggregation.

Combine filters

You can combine filters to create custom AND and OR conditions. When you filter your data based on a number of criteria, there may be filter rules that are optional and filter rules that are required. You use AND to group rules that are mandatory. You use OR to group filter rules that are optional. For example, customers filtered by Revenue > 1000000 AND Discount >.15 gives different results from Customers filtered by Revenue > 1000000 OR Discount > .15. A more complex scenario might require combining both mandatory and optional filter rules.

Filters and calculations

Filters cannot use calculations that reference the set being filtered because this creates a circular reference. Depending on how ranking functions (such as rank or quartile) or percentage functions (such as percent of total) are customized, they may not be available when defining a filter.

IS IN operator

Based on the SQL IN operator, the IS IN operator reduces the need for multiple OR clauses. Use the IS IN operator for numeric and text fields. For numeric fields it would normally be non-measures, such as Year. You can use a text box for typing values or you can use cell ranges, such as J7, $K$9 for cells or H3:H20, $J$5:$M$15 for ranges. Cells can be on another sheet, for example, Sheet2!:B4, Sheet3!D2R2, or even a named range, such as Sheet1!RangeName. Blank cells are ignored and invalid numeric values are converted to zero (0). You can create Exploration Views and lists to be used as part of a filter expression in another worksheet. For more information, see the topics on use cases.

Promote filters to a studio

When you promote an Exploration View or a list to a studio, such as Report Studio, the custom filter that used a dimensional data source is recognized as a set expression. Set expressions are calculated sets of members. For example, children ([2015]) is a set expression that displays the child members of 2015. To edit the custom filter in a studio, you must edit it as a set expression using the Query Calculation editor. For more information about set expressions, see the user guide for the studio.