Filters narrow the scope of your report. There are two kinds of filters: standard filters and run-time filters. Most document and object fields are available as filters. Reports with multiple reporting objects also allow Relationship Constraint filters.
You can learn more about reports in Creating Reports.
Filters
Standard filters are those that you create and apply in the report builder. At run-time, Vault simply filters based on your criteria. Some standard filters use run-time tokens, for example, a report could filter documents on Approver equals “Current User”.
How to Add Filters
To add a filter to the report:
- In the Filters section of the report builder, select a field from the drop-down.
- Choose an operator and field value, if needed. You can also click the Calculator icon to open the Formula Editor, where you can either type in a formula for your filter or select from a list of options to create one. If you’re filtering on a multi-select picklist, see details for operators.
- Add more filters by clicking the plus (+) icon to the right. You can delete a filter by clicking the minus (-) icon.
If you’re adding a filter on a multi-select object-type document field, see information on operators. You can use up to 100 values in one of these filters.
Advanced Logic
Advanced logic allows you to group filters together and add AND or OR operators between them.
Once two eligible filters or prompts have been added, Add advanced logic appears below the filters. Click Add advanced logic to edit the formula using the numbers next to the filters. The Validate button checks the syntax.
AND operations are evaluated before OR operations by default. You can use parentheses to determine which logic is evaluated first.
Note: Filters are locked while editing advanced logic. To modify or delete a filter, remove the advanced logic.
Limitations
The following limitations apply to advanced logic:
- The connector OR can’t be used between fields on different objects.
- Some filters are ineligible for advanced logic, and will not have a corresponding number. These filters include roles (Approver, Owner, etc.), formula fields, and relationship constraints.
- Reports that use advanced logic cannot be exported from the reports list page. You can, however, still export it while the report is open.
- Dashboard previews are not available for reports with advanced logic.
The following report types do not support advanced logic:
- Workflow
- Multi-pass
- Document relationship
- Binder
- R&U
Run-Time Filter Prompts
Run-time filters prompt the report viewer to enter filter criteria. You create these filters in much the same way as any other filter, but you skip the final step of selecting a field value and select the Prompt checkbox.
For example, you’d create a report with the filter prompt Created Date is in the range. The report viewer would select an actual date range for the filter at run-time.
Optional Prompts
You can allow report viewers to skip filter prompts by selecting the Optional checkbox. At run-time, users can select the Skip checkbox to skip the filter prompt. Vault treats skipped filter prompts as All and does not filter on the field. Report viewers can leave the filter prompt blank and filter on blank and null values.
For example, you create a report with the optional filter prompt Subtype in and the filter prompt Created Date is in the range. The report viewer can select Skip on the Subtype in prompt but must select a value in the Created Date prompt.
To skip all optional filter prompts at run-time, users can select the Skip All checkbox, which selects the Skip checkbox for every optional filter prompt. If one of the Skip checkboxes is deselected, the Skip All checkbox is also deselected.
Filter Aliases
Using a filter alias allows you to relabel a filter to show a different label in the report viewer, exported report results, and dashboard.
Admins can localize aliases using Bulk Translation export and import.
How to Add Filter Aliases
To set a filter alias:
- Under Advanced Options, set the Enable filter and column aliases checkbox.
- Click the Add filter alias link within the Filters section.
- Enter an Alias. The token {#####} represents the filter criteria value, for example, the date in Created Date is before “1/1/2010”.
- Click OK.
How to Edit Filter Aliases
To change or remove a filter alias, click the Edit link.
Run-Time Tokens
Run-time field value tokens are values that Vault dynamically populates based on conditions such as the user running the report and that user’s settings.
For fields where the value is a specific user:
- Current User: User currently logged in and running the report
For fields where the value is a
- Current Year: Date within the current calendar year, for example, if today is 5/3/2014, the filter value is 1/1/2014 - 12/31/2014.
- Previous Year: Date within the previous calendar year, for example, if today is 5/3/2014, the filter value is 1/1/2013 - 12/31/2013.
- Next Year: Date within the next calendar year, for example, if today is 5/3/2014, the filter value is 1/1/2015 - 12/31/2015.
- Current Quarter: Date within the current calendar quarter, for example, if today is 5/3/2014, the filter value is 4/1/2014 - 6/30/2014.
- Previous Quarter: Date within the previous calendar quarter, for example, if today is 5/3/2014, the filter value is 1/1/2014 - 3/31/2014.
- Next Quarter: Date within the next calendar quarter, for example, if today is 5/3/2014, the filter value is 7/1/2014 - 9/30/2014.
- Current Month: Date within the current calendar month, for example, if today is 5/3/2014, the filter value is 5/1/2014 - 5/31/2014.
- Previous Month: Date within the past calendar month, for example, if today is 5/3/2014, the filter value is 4/1/2014 - 4/ 31/2014.
- Next Month: Date within the next calendar month, for example, if today is 5/3/2014, the filter value is 6/1/2014 - 6/30/2014.
- Today(): Current date. To use the Today() run-time token, users must select a Custom Period condition and enter Today() in the date selector. For example, if today is 5/3/2014, the filter value is 5/3/2014.
For the Study field in Clinical Operations application Vaults:
- Current Study: Study selected in Study Selector
Example
Gladys configures a report to show the average days each workflow takes to complete. She applies a filter to the report to only show workflows where the Workflow Owner is the user running the report.
When Teresa Ibanez runs this report, the Workflow Owner filter automatically updates to show her.
Operators
The following sections describe operator behavior for filters, prompts, and multi-select picklists.
Contains
For report filters and prompts, returned results include only those where the field partially matches the filter value. Vault automatically applies a wildcard to the beginning and end of the query. Note that the filter value is case sensitive.
Example Filter: Study > Study Name contains Alpha
- Match: Study name is Alpha Cholecap
- Match: Study name is 123-Alpha-Cholecap
- No Match: Study name is ALPHA
The following limitations apply to the contains operator:
- You cannot use the operator in report views.
- You cannot use the operator to filter on the following fields:
- long text or rich text fields
- formula fields or conditional fields
- multi-value picklist fields in raw objects
- object reference fields
- You can only define three (3) filters using the contains operator per report.
- The filter value must be between three (3) and 50 characters in length.
Equals
For multi-select picklists, returned results include only those where all filter values are in the field. The field may also contain additional values that are not in the filter.
Example Filter: Approver equals Bruce Ashton, Tracy Lee
- Match: Approvers are Bruce Ashton, Tracy Lee
- Match: Approvers are Bruce Ashton, Tracy Lee, Robyn Newsome
- No Match: Approver is Bruce Ashton
In
For multi-select picklists, the “in” operator returns results that include at least one of the filter values.
Example Filter: Approver in Bruce Ashton, Tracy Lee
- Match: Approvers are Bruce Ashton, Tracy Lee
- Match: Approvers are Bruce Ashton, Tracy Lee, Robyn Newsome
- Match: Approver is Bruce Ashton
Not Equal To
For multi-select picklists, returned results (documents, tasks, etc.) include only those where the field does not contain any of the filter values.
Example Filter: Country is not equal to Fiji, New Zealand
- Match: Country is United States
- No Match: Country is Fiji, United States
- No Match: Country is Fiji, New Zealand
- No Match: Country is Fiji
Format Masks
The report filters, conditional fields, and prompts will behave according to any format masks configured by your Admin. For example, if you select Phone Number from the drop-down and enter “5555555555” as the field value, the format mask will modify your input to a designated format once you click away from the field, such as “(555) 555-5555”.
Format masks can include email, phone number, and percentage fields. For fields configured with a Percent format mask using the Percent() function, enter the actual percentage rather than a decimal (for example, “50” for 50% instead of “.5”). In addition, if you use a starts with operator on a Phone Number field, you must enter the number without any format, such as “5555555555”.
The values entered in format mask fields will display in the report results under columns labeled with the specific format, such as Formatted Number, Formatted Text, or Email. For example, if you add a field with a percent format mask, the percent will display in the report results under the Formatted Number column.
Note: Aggregate calculations and report groupings will continue to display the original value entered rather than the format mask value.
Inactive & Deleted Picklist Values
In some Vaults, you can filter by deleted or inactive picklist values. An Admin must enable this setting.
Relationship Constraint Filters
By default, multi-object report types (Product with Document, Study with Site and Product, etc.) will return all matching records for the primary reporting object, even if those records are not linked to related records. For example, the Product with Document report would return a list of products that includes those without any associated documents. Relationship Constraint filters limit the report output to records with at least one related record. In the example, only products that have at least one related document.