What is a Filter?

IMPORTANT: Dimension value filters are contained within the cplan block.

= Filter

This filter only includes the dimension value you specify. It means "equals."

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `value("Resp SalesPerson") = "Tony"` 
  } 

The filter is focusing on the Resp SalesPerson dimension value Tony. Any values that are not Tony are filtered out while the Tony values remain.

!= Filter

This filter includes every dimension value except for the value you specify. It means “does not equal.”

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `value("Resp SalesPerson") != "Tony"` 
  } 

The filter is focusing on the Resp SalesPerson dimension value Tony, which it filters out. The values that are not Tony remain.

or Filter

This filter includes all the data from the two or more dimension values you specify. It means “or,” so it looks for the named values and filters out all other values. Each value must include a specified dimension. This can be the same dimension, or different dimensions.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter ```
    value("Resp SalesPerson") = "Tony" or 
    value("Resp SalesPerson") = "Chris" or
    value("Resp SalesPerson") = "Doug" 
    ``` 
  } 

The values are from the same dimension, Resp SalesPerson, and the values Tony, Chris, and Doug are included; all other values are filtered out.

is_in Filter

This filter is similar to the “or” filter, except that a dimension is defined once and then the values are listed. All values mentioned must be in the dimension specified. This allows you to list the dimension once, and then list multiple values in that dimension. Values not specified for that dimension are not included.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `is_in(value("Resp SalesPerson"), "Tony", "Chris", "Doug", "Kathy", "Roger", "Fred")` 
  } 

The values Tony, Chris, Doug, Kathy, Roger, and Fred for the dimension Resp SalesPerson are included, while all other values are filtered out.

and Filter

This filter looks for data that apply to all the dimensional values listed. It means “and”, so the data must be specific to all value filters. This filter requires the use of two different dimensions; if you were to use the same dimension, you would get an empty set. Data cannot have multiple values under one dimension.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `value("Resp SalesPerson") = "Tony" and value("Corporate BRAND") = "ALICE"` 
  } 

The data is filtered by both Tony from the Resp SalesPerson dimension and ALICE from the Corporate BRAND dimension. Data applying to both dimension values is displayed. If you were to use the same dimension, for example Resp SalesPerson dimension values Tony and Doug, you would get an empty set because the data cannot be specific to both Tony and Doug simultaneously.

The and Filter can also be expressed in two separate lines.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `value("Resp SalesPerson") = "Tony"` 
    filter `value("Corporate BRAND") = "ALICE"` 
  } 

You are filtering by a certain value, Tony, for the Resp SalesPerson dimension and then filtering the data further by Corporate BRAND dimension value ALICE.

The and filter can also be used in conjunction with other types of filters.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `value("Resp SalesPerson") = "Tony" and is_in(value("Customer City"), "BROOKLYN", "SPRING VALLEY")` 
  } 

The dimension value Tony is filtered even further by the values from dimension Customer City.

Date Value Filters

Date value filters are used to filter data by periods of time. Though the filters are similar to those for summary values, date value filters are written differently in the code and are displayed as dimensions.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `year(value("Posting Period")) > 2011` 
  } 

In this example, the dimension values for Posting Period that are later than the year 2011 are included, while those equal to or earlier than 2011 are excluded. Please take note that since a month is not specified, the filter excludes all months in 2011.

Since the dimension values for Posting Period are written as Year-Month, you can also filter more exactly by including the month. For example, October of 2016 would look like 2016-10. It is important that the dimension values match the format used in the cPlan, or the filter does not work properly.

For example:

  cplan "/cplans/basic_101.cplan" { 
    filter `year(value("Posting Period")) > 2011-10` 
  } 

In this example, the dimension values that are later than the month of October 2011 are included, while those earlier than, and including October 2011 are excluded.

Summary value filters use numerical data, so it is possible to use more mathematical filters in the process. Since these values are integers, they do not need quotation marks in the code.

NOTE: You can filter on a calc, such as Cases MTD Y-1, without displaying it as a column.

Summary value filters are contained in the page type block.

= Filter

This filter looks for summary values that equal a specified value. This filter is exact; if there are no values that equal the filtered value, there is no matching data and therefore no results.

For example:

  filter `value("9 Liter Cases") = 50` 

This only returns data where the summary value for 9 Liter Cases is exactly 50. For this particular example, there are no values that are exactly 50, so there are no returned values in the DiveTab client.

> Filter

This filter includes any data that is greater than the specified number. It means "greater than," so all numbers greater than the filtered summary value are included; all numbers less than or equal to the filtered value are excluded.

For example:

  filter `value("9 Liter Cases") > 50` 

Values greater than 50 in summary 9 Liter Cases are included, while values less than or equal to 50 in summary 9 Liter Cases are filtered out.

< Filter

This filter includes any data that is less than the specified number. It means "less than," so all numbers less than the filtered summary value are included; all numbers greater than or equal to the filtered value are excluded.

For example:

  filter `value("9 Liter Cases") < 50` 

Values less than 50 in summary 9 Liter Cases are included, while values greater than or equal to 50 in summary 9 Liter Cases are filtered out.

notnull Filter

This filter excludes data values that are blank. For example, if there is no SCC Code for County Cayuga, all information related to that county, including the Cost and Cases, is excluded from the page. This filter is used primarily by summaries, but dimensions that are used as text columns can also use this filter.

      report-page { 
        cplan "/cplans/basic_101.cplan" { 
          filter `notnull(value("SCC Code"))` 
        } 
        title "NotNull Summary Filter" 
        
        dimension "County" 
        text column = "Cost" { 
          value-format "$#,###.00" 
        } 
        text column = "Cases" 
        text column = "SCC Code" 
      } 

IMPORTANT: Zero (0) does not count as a null value.