Overview

Filters enable users to control what data will be pulled into the report when it is run. There are many ways to filter data in Cosmos reports which give the Report Editors a high level of control when building reports and give the Report Viewers options for how they want the report to run.

This article will cover the details of the different types of filters that can be used and how they will influence the output of the report.

Sections:

• Filter Types
• Adding Filters
• Deleting Filters
• Filter Operator Types
• Filtering on Data in Another Table
• Filtering Dynamically on Range Lists
• Filtering on Blank Values
• Filtering Out Multiple Values from a Range

Details

Filters provide users with the ability to limit the data that is returned in a report when the report is run. The following sections describe in detail how to apply and work with filters in your Cosmos reports.

It is important to note that when multiple filters are added to a function that these behave as AND filters, meaning that the criteria for each filter must be met for records to be returned. For example, if the following two filters were applied to a function, only transactions where the Salesperson Code was AMB and where the Country Code was USA would be returned. 

Filter Types

When working with filters in your Cosmos reports it is important to understand that there are two primary types of filters available

• Report Option Filters: Report Options are filters that are set at the report level. Report Options enable Report Editors to define filters that should apply to most (if not all) functions in a report and allow the person running the report to select which filters they want to set when the report is run. 
• Function Filters: Function filters are more limited in scope than Report Options and are meant to apply filters to a specific function in the report. These function filters can apply to Sheet Lists, Range Lists, Aggregates, or be hard-coded values in the report.
  • An example of a hard-coded value in a function could be a Range List that returns customers going down rows; the Report Editor may want to specify that the list should only ever return customers from Canada, so they could add a filter to the Range List where the Country Code = CA. This would be a filter that would always be enforced in the report and the person running the report would not be able to override this when the report is run.

Adding Filters

When a function is added, Cosmos will intelligently look at where the function is being placed and associate the function with other relevant functions in the report. For example, if the user has added a Report Option for Date, when the user then adds a Range List for the Sales Transactions table the Report Option will automatically be added to the Range List function. If relationships have been defined in the data model, then Cosmos can automatically link filters to newly added functions. 

If the user would like to manually add a filter to a function they can navigate to the function, click EDIT, expand the Filters section, and click the ADD FILTER button.

The user can then select the field they would like to apply the filter to, select the operator, and then place the filter value in the Filter Value section. In the example below, the user added a filter to only return records where the Document Type = Invoice.

If the user needs to add more filters, they can click the ADD FILTER button and repeat the process for as many filters as needed. There is no limit to the number of filters that can be added to a function.

Deleting Filters

If the user needs to delete a filter from a function the user can find the function in the Cosmos Task Pane, click EDIT, expand the Filters section, and click the trash can icon to the right of the filter to be deleted. An example is highlighted below in red.

This will remove the filter from the function.

Filter Operator Types

There are a wide variety of operators that can be added to functions to give the user control over what data will be returned in their Cosmos reports. A list of operators and their descriptions are included below. There are some operators that are only available with certain field data types and those are covered in their relevant sections.

Filtering on Data in Another Table

When the user wishes to filter on a field that is in a different table than the function is pulling from, the user will see all related tables when searching in the Filter Field section. If relationships have been defined between the tables in the Data Model the user will be able to easily filter on the field they are looking for. 

In the example below, the user has added a Range List to return customer numbers from the Sales Transactions table, but the user wishes to filter based on the Country Code in the Customer table. 

Filtering Dynamically on Range Lists

It is common for a Report Editor to want to link their filter to something that will be dynamically changing when the report runs, such as a range list. For example, instead of hard coding in a list of customer numbers to sum up sales amounts for, the user may want to link the function to a Range List that was already created in the report. 

When adding the filter, the user can click in the Filter Value section and if there are relevant Range Lists that can be linked, they will appear in the dropdown. The user can click the Range List to select the one to link to. Filters that are linked to a Range List will have a link icon visible in the Filter Value section.

The example below shows the user linking a sum function to a Range List named "Sell to Customer No List"

Filtering on Blank Values

The user may want to apply a filter to a function so that it only returns results where a certain field is empty. After selecting the field to filter on, the user can select the Special option from the dropdown list and in the Filter Value section select Empty.

Filtering Out Multiple Values from a Range

There may be times where you would like to return a range of things in a filter, but exclude some items from that list. For example, you may want to return all GL accounts in the range of 40000 through 49999, but want to exclude 45750 and 45775. To do this, you could add the entire range using the Between operator for the filter, and then you would add additional <> filters for the values to exclude, with each value being a separate filter. The example below shows what this would look like: