Using Filters in Custom Reports

Tags Reporting

Overview

When building a custom report, you may want to narrow the returned results down so they are relevant for your needs or the specific needs of your organization/group(s). Adding filters to your reports allows this to be possible.

The filters appear below the column choices in the report builder. A new custom report comes preloaded with five blank filter rows with each row representing a filter condition, where each represents a rule that the entries must match in order for them to appear in the report results.

The columns and filters available vary depending on the report source, but the functionality is consistent across all custom reports. For more information on the columns, values, and filters available in each report source, click the Help button in the toolbar of the report builder.

Where to Find This

This is found in custom reports as part of the filtering options in the TDNext interface. It exists in all the applications that include the custom reports feature.

Navigate to custom reports following these paths:

  • TDNext > [Application] > Reports
  • TDNext > Analysis
  • TDClient > Reports (Viewable only. Reports can only be edited in TDNext)

Using Custom Report Filters

Adding Filters to a Custom Report

To add filters to a report:

  1. In TDNext, navigate to the Analysis application.
  2. Create a new custom report or edit a previously saved report.
  3. After setting up the columns in the report builder, scroll down to Add filtering to your report.
  4. Click the Column dropdown and select the field you want to use to narrow down your results.

You can filter on values that have not been included as columns, the filter will apply to values in the background.

  1. Select an Operator from the dropdown.

The Operator choices vary depending on the type of data contained in the Column field.

  1. Select the Values you want applied to your report results.

Value choices vary based on the type of data in the selected Column field.

  1. Click the Prompt checkbox to include an on-the-fly filter control at the top of the report.

This allows filter values to be changed without needing to edit the saved report. It also allows other users who have access to the report to modify filters.
Setting the prompt field as required means the field cannot be left blank when the report is run.

  1. Enter the Column, Operator and Values, to the next row if you want to have more than one filter condition.

Adding multiple filter rows behaves as an AND operator, so the conditions in all the rows must be true for an entry to appear in the results. To set up filters so the results meet one of the conditions, use an OR operator in the advanced filter (see the Using the Advanced Filter section below).

  1. To see how filtered results are returned in your report, click the Save and Run button.
  2. To make adjustments to your filters or columns, click the Actions button and select Edit.
  3. When finished, click Save.

Once saved, the report will appear in the left navigation.

Using the Advanced Filter

The advanced filter in TDX supports the AND / OR Boolean operators. Boolean operators are part of basic database query logic and connect your search words together to either narrow (AND) or broaden (OR) your set of results.

By default, each row in the filter table acts as an AND function, and the criteria in all the rows must be true for an entry to appear in the results. But you may want to use an OR in your search to:

  • See results for two or more similar values.
  • Broaden your results, telling the database that any of your filter conditions can be present in the resulting records.

To create an advanced filter:

  1. Setup more than one condition with Columns, Operators, and Values.

The numbers in the leftmost column are used to represent each condition in advanced filtering.

  1. Open the advanced filter field, by clicking the Show Advanced button.
  2. Enter a Boolean string into the text field using the condition numbers.

To expand your search to show results that are true for any one of your conditions, enter “OR” between the condition numbers. For example, “1 OR 2 OR 3”
The advanced filter also supports using AND OR in combination. For example, “(1 OR 2) AND 3”.
The advanced filter must include all of the conditions set up in the table. Any omitted conditions will not be included when the report is run. For example, If I have four filter conditions, but my advanced filter only addresses 1, 2 & 3, rule #4 will be ignored when the report is run.

  1. To see how filtered results are returned in your report, click the Save and Run button.

Gotchas & Pitfalls

Filtering Results Based on Time Relative to A Date

Users often have trouble understanding how to configure conditions for a time-based filter relative to a date to see the results they want. The run date value represents the day the report is run, which usually means now unless the report has been exported.

To check for items based on times relative to the run date, you can select the plus/minus drop down, add a value, and designate the time increment you wish to add to or remove from the run date to arrive at the correct date/time for the filter in question.

For example, if you wanted to find tickets where the Created date of a ticket was within the last 7 days from the run date, you would use the filter value of the run date minus 1 week or 7 days.

On the other hand, if you wanted to look for tickets with a Due date upcoming within the next 7 days, you would use 2 filters based on the Due column, like so:

  1. Due, Operator of either greater than or greater than or equal and a Value of the run date (leave the remaining Values boxes untouched to just set this as the run date with no modifications). This looks for everything with a Due date greater than the run date.
  2. Due, Operator of either less than or less than or equal and a Value of the run date plus 7 days. This looks for everything with a Due date less than (or potentially also equal to) the run date plus 7 days into the future.

Inclusive and Exclusive Filters

Sometimes you want to find entries in an application that are missing information or to surface outliers. Report filters can support this with the exclusionary operators is or is not one of or, is or is not a member of.

For example,

To see the results for a ticket report that are not High priority, you would add a filter where:

·         Column = Priority

·         Operator = is not one of

·         Value = High

Without an Advanced Filter Expression

If you are not using an advanced filter expression, using a not one of or a not a member of filter type equates to a filter that is effectively:
[Column] has no value or [Column] is not one of ([Value 1], [Value 2], etc.)

With an Advanced Filter Expression

If you are using an advanced filter expression, using a not one of or a not a member of filter type equates to a filter that is effectively:
[Column] is not one of ([Value 1], [Value 2], etc.)

Note that using that advanced filter does not automatically include the has no value check because advanced filtering is meant to give complete control of the filter. To add this rule, include another condition row for [Column] Operator: has no value, and then update your advanced filter expression to use this additional option with an OR operator.

If the report builder did not work this way in advanced filter expression mode, you would not actually be able to use not one of or a not a member of filtering to find only items where a value was present and also was not not the filter value(s) specified.

Source: https://solutions.teamdynamix.com/TDClient/1965/Portal/KB/ArticleDet?ID=67920

Print Article