Advanced Spreadsheets and Reporting Documentation
Index

Indicator Filters

Filters determine which records from the source table are included in the indicator calculation. This guide explains all filter types and how to configure them effectively.

Understanding Filters

Purpose

Filters narrow down the data that an indicator processes:

  • Include only relevant records
  • Exclude unwanted data
  • Apply date restrictions
  • Respect report dimension filters

Filter Processing

When an indicator calculates:

  1. Each filter is applied to the source table
  2. Filters combine with AND logic
  3. Only records matching ALL filters are processed
  4. The calculation method runs on the filtered set

Filter Types

Static Filter

A fixed filter value that doesn't change.

Configuration:

  • Filter Type: Static
  • Filter: Your filter expression (the operator is part of the expression)

Date literals in a static filter are parsed in the session's regional date format. Client-side tokens such as TODAY or WORKDATE are not resolved and cause an error; use a Column Period filter for date-relative filtering. A blank static filter removes the filter (it does not filter for blank values).

Examples:

Field: Document Type
Type: Static
Value: Order

Field: Status
Type: Static
Value: Open|Released

Field: Amount
Type: Static
Value: >0

Filter Syntax:

Pattern Meaning Example
Value Exact match Order
Value1&#|;Value2 Either value Open&#|;Released
Value1..Value2 Range 10000..20000
>Value Greater than >0
Less than <100
>=Value Greater or equal >=1000
<=Value Less or equal <=500
<>Value Not equal <>Blocked
*Value Ends with *Sales
Value* Starts with CUST*
Value Contains Order

Global Dim 1 Filter

Uses the Dimension 1 Filter from the financial report (the filter set on the row definition lines when the report runs, not anything on the column definition).

Configuration:

  • Filter Type: Global Dim 1
  • Filter: (not used)

Behavior:

  • When the report has a Dimension 1 Filter: That filter is applied
  • When the report has no Dimension 1 Filter: No filter applied

Use Case: The indicator should respect the same department/cost center filter as the G/L rows.

Example:

Report filter: Dimension 1 = 'SALES|MARKETING'
Indicator filter: Type = Global Dim 1

Result: Indicator filters by Department = 'SALES|MARKETING'

Global Dim 2 Filter

Uses the Dimension 2 filter from the financial report.

Configuration:

  • Filter Type: Global Dim 2
  • Filter: (not used)

Behavior:

  • Same as Global Dim 1, but for Dimension 2
  • Typically project or customer group

Column Period Filter

Applies date filtering based on the column definition's date calculation.

Configuration:

  • Filter Type: Column Period (accepted only on Date fields)
  • Date Formula: Optional adjustment formula (Date fields only; validated when entered)

Behavior: This is the most powerful filter type. It:

  1. Takes the column's calculated start and end dates
  2. Optionally shifts both dates with your Date Formula
  3. Applies a range based on Row Type and Column Type

Date Range Calculation:

Column Type Row Type Date Filter Applied
Net Change Net Change Start Date to End Date
Net Change Beginning Balance Up to the closing date before Start
Net Change Balance at Date Beginning to End Date
Balance at Date Net Change / Balance at Date Beginning to End Date
Balance at Date Beginning Balance Empty range (returns zero)
Beginning Balance Net Change / Beginning Balance Beginning to the closing date before Start
Beginning Balance Balance at Date Empty range (returns zero)
Year to Date Net Change Fiscal Year Start to End Date
Year to Date Beginning Balance Up to the closing date before Fiscal Year Start
Year to Date Balance at Date Beginning to End Date
Rest of Fiscal Year Net Change Day after End Date to Fiscal Year End
Rest of Fiscal Year Beginning Balance Beginning to End Date
Rest of Fiscal Year Balance at Date Beginning to Fiscal Year End
Entire Fiscal Year Net Change Fiscal Year Start to Fiscal Year End
Entire Fiscal Year Beginning Balance Up to the closing date before Fiscal Year Start
Entire Fiscal Year Balance at Date Beginning to Fiscal Year End
Formula (any) No period filter is applied

Date Formula Field:

Shifts the column's start and end dates (each date is moved with the formula); it is not relative to today. The fiscal-year boundaries used by Year to Date, Rest of Fiscal Year and Entire Fiscal Year are not shifted.

<-1M>   = Move both dates one month back
<+1Y>   = Move both dates one year forward
<-1Q>   = Move both dates one quarter back
<CM>    = Move each date to the end of its month
<-CM>   = Move each date to the start of its month

Example:

Column: Net Change for January 2024
Field: Posting Date
Type: Column Period
Date Formula: (blank)

Result: Posting Date filtered to January 1-31, 2024

Example with Date Formula:

Column: Net Change for January 2024
Field: Posting Date
Type: Column Period
Date Formula: <-1Y>

Result: Posting Date filtered to January 1-31, 2023

Adding Filters

Step-by-Step

  1. Open the Define Indicator page
  2. In the Filters section, click on an empty row
  3. Click the assist-edit on Field Name
  4. Select a field from the source table
  5. Set the Filter Type (Column Period requires a Date field)
  6. Enter the Filter (for Static) or Date Formula (for Column Period; Date fields only, validated on entry)

Multiple Filters

You can add multiple filters to one indicator:

  • Each filter applies independently
  • All filters must match (AND logic)
  • No limit on number of filters

Example: Open Sales Order Lines

Filter 1:
  Field: Document Type
  Type: Static
  Value: Order

Filter 2:
  Field: Outstanding Quantity
  Type: Static
  Value: >0

Filter 3:
  Field: Shipment Date
  Type: Column Period

Common Filter Patterns

Open Documents

Field: Status
Type: Static
Value: Open|Released

Posted Transactions

Field: Posting Date
Type: Column Period

By Dimension

Field: Global Dimension 1 Code
Type: Global Dim 1

Non-Zero Amounts

Field: Amount
Type: Static
Value: <>0

Specific Document Types

Field: Document Type
Type: Static
Value: Invoice|Credit Memo

Date Range Comparison

Field: Order Date
Type: Column Period
Date Formula: <-1Y>  // Same period last year

Filter Design Best Practices

1. Start Specific

Begin with narrow filters and broaden if needed:

  • Easier to add data than fix wrong data
  • Prevents unexpected results

2. Test Iteratively

After each filter change:

  • Use the Test feature
  • Verify against source data
  • Check performance impact

3. Document Complex Filters

When filters are complex:

  • Add comments in row descriptions
  • Maintain external documentation
  • Explain the business logic

4. Consider Performance

More records = slower calculation:

  • Add filters to reduce record count
  • Use indexed fields when possible (the order of the filter rows has no effect)

5. Use Column Period for Dates

For period-sensitive indicators:

  • Always use Column Period type
  • Ensures consistency with financial report periods
  • Handles fiscal year logic correctly

Troubleshooting Filters

Indicator Returns Zero

Possible Causes:

  • Filters too restrictive
  • Date range has no data
  • Filter values spelled incorrectly

Solutions:

  • Remove filters one by one to identify the issue
  • Verify filter values against actual data
  • Check date filter ranges

Indicator Returns Too Much

Possible Causes:

  • Missing filters
  • Filters too broad
  • Date filter not applied

Solutions:

  • Add filters to narrow results
  • Make filter values more specific
  • Add Column Period filter for date fields

Wrong Date Range

Possible Causes:

  • Wrong field selected for Column Period
  • Date Formula incorrect
  • Row Type/Column Type combination unexpected

Solutions:

  • Verify the date field is correct
  • Review Date Formula syntax
  • Check the column definition configuration

Dimension Filter Not Working

Possible Causes:

  • Wrong field selected
  • Report doesn't have dimension filter
  • Global Dim 1/2 don't match table structure

Solutions:

  • Verify the field is the dimension code field
  • Add dimension filter to the report
  • Check that the table uses global dimensions

Advanced: OR Logic Simulation

Filters use AND logic. To achieve OR logic:

Option 1: Multiple Indicator Lines Create separate indicators and sum with a formula row:

Row 10: Invoices (Indicator, Doc Type = Invoice)
Row 20: Credit Memos (Indicator, Doc Type = Credit Memo)
Row 30: Total (Formula: 10+20)

Option 2: Pipe Syntax in Static For the same field, use pipe for OR:

Field: Document Type
Type: Static
Value: Invoice|Credit Memo