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.
- 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
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.
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 point to Report Options, Range Lists, 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.
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, 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 in the Filter Field section 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.
If the user needs to delete a filter from a function the user can select the function in Excel, open the Filters section in the Cosmos Report Editor, and click the "-" 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 is included below. There are some operators that are only available with certain field data types and those are covered in their relevant sections.
|=||Returns results that equal the specified filter value(s)||Salesperson Code = AMB will only return results where the salesperson on the transaction was AMB.|
|<>||Returns results that do not equal the specified filter value(s)||Salesperson Code <> AMB will only return results where the salesperson on the transaction was not AMB.|
|Between||Returns results that are between the specified filter values. This will include the values on each end of the range||Year Between 2019 and 2022 will only return results where the date of the transaction took place between 2019 and 2022.|
|>||Returns results that are greater than the specified filter value||Amount > 1000 will only return transactions where the amount was 1000.01 or higher.|
|>=||Returns results that are greater than or equal to the specified filter value||Amount >= 1000 will only return transactions where the amount was 1000 or higher.|
|<||Returns results that are less than the specified filter value||Amount < 1000 will only return transactions where the amount was 999.99 or lower.|
|<=||Returns results that are less than or equal to the specified filter value||Amount <= 1000 will only return transactions where the amount was 1000 or lower.|
|Report Option||Returns results that match what the user specifies for the selected Report Option when the report is run||If the user set the Country Code = USA when the report is run, only customers that have the country code of USA will be returned|
|Contains||Returns results that contain the specified filter value. This is only available when filtering on a text field.||Description Contains bicycle will return all items where the item description contains the text "bicycle"|
|Does Not Contain||Returns results that do not contain the specified filter value. This is only available when filtering on a text field.||Description Does Not Contain bicycle will return all items where the item description does not contain the text "bicycle"|
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 click the Field Value section and will see a dropdown available with a special filter for "Blank Value".
Clicking on Blank Value will insert a special filter type which will only return results where the value for the field is empty. Once the Blank Value has been selected it will show as a special filter type in the Filter Value section with a link icon next to it.
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: