Selecting query filters will impose restrictions upon the report being produced and return a set of results that matches the filters you set. For example, if you select the Status Open, then only open issues will be displayed. Filters can be combined together, for example to show Open issues for the product named XXX that have been updated in the last 7 days.
There are potentially four options that you can apply when creating filters. They do not all appear all the time according to how your administrator has configured the site and according to other options chosen.
After setting up filters, you may want to save them for future recall. To achieve this, simply use the Save / Update Filters button. After saving the filters, you can recall them by selecting the name you used in the select list. Saved filters may be personal, and with permission you can save them to be shared by all users. These saved filters are independent of the reports saved within ExtraView, although you can include any saved filters within a report that you create.
Saving filter sets
Dialog for saving filters
Using the popup dialog, you can also delete an existing filter set.
There are a few caveats on the use of saved filter sets:
Saved Filters within a Hierarchical Report Editor screen
Note: With Advanced Filters only, you can create saved filters where the fields have the value of * Any *. Whereas filters with the value of * Any * are typically ignored, and discarded when saved, there is a purpose to this feature. Consider that you might have a set of many filters that you want to use frequently as you create reports. These reports may or may not require all the filters, but you want them to be easily available when creating these reports. This provides a rapid way to construct the filters for these reports.
This allows you to choose the values for fields, upon which records are selected for the report. There is a set of filters available directly on the Query / Report screen, as well as from the screens where you prepare or edit reports that you save for future use. Typically, the Query / Report screen will show the most common filters you use, while the remaining screens show a more complete set of filters to which you have access. The fields available for your use are set up by your administrator.
Standard query filters
If a query filter you select is the parent in a relationship, for example, the Module field may be dependent on the Product field, then the screen will refresh, and the child field will be populated with the valid entries for the parent you selected. If your administrator has enabled the feature that allows you to search for inactive users, an additional prompt appears on the screen alongside the Query Filters heading. This is a checkbox with the label Show Inactive Users. When you check this, all the selection lists of users, will be refreshed, and will show inactive as well as active users, allowing you to perform searches for users who were at one time, but are not currently, licensed users of ExtraView. Note that this feature only works in Standard Query mode, and is not operable in Advanced Query mode.
If your administrator has given you access to advanced query mode, you first will select an individual field as the first filter to use in selecting individual records for the report. Then you select an operator such as “equals” or “greater than”, and then you select the value. For example, a filter may be: Status not equal to Closed You may add as many filters as you like, with each one beginning with a conjunction such as “and” or “or”. Advanced filters take a little longer to set up than standard mode filters, but they allow much more flexibility to create a complex set of query filters.
Advanced filter selection
To add a new filter, use the button to the left of the filter. Note that you can add filters following any existing filter. If you want to delete an individual filter from a query, use the button. This will eliminate the filter. In advanced mode, you can set the same filter field multiple times and use the “or” value. For example, you may set two filters as follows:
Priority equals P1 or Priority equals P2
Alternatively, you can use the Expanded search mode described below, and select multiple values from the Priority list.
There is no limit to the number of filters that you may use to compose a query. One construct that is often useful is to be able to determine several filters grouped together. This is equivalent to using parentheses around parts of your query to separate these parts of the query from other parts of the query. The grouping of filters is achieved with the arrows beneath the and buttons. When you click on the right arrow to indent the filter, you are effectively adding parentheses around the indentation. As an example consider:
This query is equivalent to:
Area = Helpdesk
AND (Last Modified during Last 4 weeks AND Assigned To = Bill Smith)
AND
Area = Customer Issues
AND (Last Modified during Last 4 weeks and Assigned To = Bill Smith)
The operators that appear in the drop down list are sensitive to the display type of the field you select. This allows you to select operators that only make sense and are eligible for each field display type. This table shows how this operates:
Field display type | Operator values in Condensed Filter Mode | Operator values in Expanded Filter Mode |
Currency, Decimal, Number |
Equals Greater than Less than Not equal Less than or equal to Greater than or equal to Empty Not empty |
Equals Greater than Less than Not equal Less than or equal to Greater than or equal to Empty Not empty |
Checkbox, List, Pop-up, Tab, User |
Equals Not equal |
Equals In Not equal Not In |
Date, Day |
Equals Greater than Less than Not equal Less than or equal to Greater than or equal to Between Empty Not empty During After Before After or during Before or during |
Equals Greater than Less than Not equal Less than or equal to Greater than or equal to Between Empty Not empty During After Before After or during Before or during |
Keyword | Contains | Contains |
Text Field |
Equals Contains Not Equal Empty Not Empty |
Equals Contains Not Equal Empty Not Empty |
The operator values are largely self-explanatory, but a word of explanation is needed about the Empty and Not Empty values. When a numeric field (currency, decimal or number display types) is stored in ExtraView, and you do not enter a value, zero is not the value stored. A null, or empty value is stored and these are treated differently. Therefore searching for a value of empty will retrieve a different set of results that searching for a value of zero. Also, note that for Checkbox, List, Pop-up, Tab and User fields when in Expanded Query mode, there are two additional operators, In and Not In. These allow you to define a list of values to search for that must be present or must not be present. If you want to search for both empty and zero values, you may combine two filter conditions together, as exemplified by the following: Amount Equals 0 or Amount is Empty.
Fields with a display type of Date and Day present different options for their values, according to whether an arithmetic operator such as equals or greater than is chosen, or a duration such as during or after is chosen. When an arithmetic operaor is chosen, you may enter dates into one or both date value fields. When you choose a duration, you are presented with a list of durations such as last month, this fiscal quarter and this month to date. ExtraView understands the meaning of these durations and will filter your query or report using the duration you select.
The following conjunctions can be used between the different filters of a report:
Conjunction | Meaning |
and | This filter will ensure that both filter conditions on each side of the and are true, before including the results |
or | This filter will include results from the search, if either of the conditions on each side of the or is true |
union |
This will take the results of all the filters together preceding the conjunction union, together with the results of the filters in the query following the conjunction, and merge the results of both queries. For example, if you union two queries together, then you will see the results of both queries being returned on the report. The area within the red outline shows the results returned by two queries using the union conjunction. |
minus |
This will take the results of the query filter(s) before the conjunction minus, and remove all results in common with the results of the query filter(s) following the conjunction. For example, if the query before the minus returns 100 rows, and the query following the minus returns 60 rows, 30 of which are to be found in the first part of the query, you will see 70 rows on the report that is generated. The area within the red outline shows the results returned by two queries using the minus conjunction. |
intersect |
This will take the results of the query filter(s) before the conjunction intersect, and only display the results that are in common with the results of the query filter(s) following the conjunction. For example, if the first part of your query before the intersect returns 100 rows, and the second part of the query after the intersect returns another 100 rows, and 30 of the results are in both parts of the query, you will see these 30 rows returned on the report. The area within the red outline shows the results returned by two queries using the iintersect conjunction. |
If your ExtraView installation is running on a Microsoft SQL Server database prior to the 2005 version or on the MySQL database0 not all the conjunctions are functional due to database limitations. Only conjunctions that are known to work are operable. Check with your administrator if you are not certain which database is used by your company. If you have more than one union, minus or intersect conjunction in a query, then the filters up to the first one that contains the conjunction are grouped together. The order in which you choose filters is significant if you choose to use the “or” conjunction in your query. The rules for parsing the queries are as follows, with the parentheses showing the precedence.
Order of conjunctions | Significance |
One “or” as the last filter | The condition in the filter with the “or” is used over the whole query. For example: Product equals XYZ and Priority equals P 1 and Status equals Open or Severity equals Severe This query is parsed as follows: (Product equals XYZ and Priority equals P 1 and Status equals Open ) (or Severity equals Severe ) |
One “or” that is not the last filter | The “and” operators take precedence over the “or” operator. For example: Product equals XYZ and Priority equals P 1 or Priority equals P 2 and Severity not equal Severe This query is parsed as follows: (Product equals XYZ and Priority equals P 1 ) (or Priority equals P 2 and Severity not equal Severe ) |
There are multiple “and” and “or” filters | Once again, the “and” operators take precedence over the “or” operators. For example: Product equals XYZ and Priority equals P 1 or Priority equals P 2 and Severity not equal Severe or Severity not equal Major This query is parsed as follows: (Product equals XYZ and Priority equals P 1 ) (or Priority equals P 2 and Severity not equal Severe ) (or Severity not equal Major ) |
Note: You can toggle between standard search mode filters and advanced search mode filters at any time, but the filters currently on the screen are reset. Filters are not lost when you change between condensed and expanded filters.
Allowed values do not operate within filters selected for advanced queries. You will see all the parent and all the child values in the filter lists for these queries.
Within Advanced Search lists, an entry * Show disabled values * will appear in any list that contains values that have been disabled by the administrator. For example, you may wish to search for issues where the originator of the issue has left your company and their account has been disabled. If the field contains any values that are disabled, then you can select * Show disabled values * and the screen will refresh, showing all the disabled values. You can revert to only displaying enabled values with the prompt * Do not show disabled values * that will now be in the list.
This applies when applying conditional formatting to multiple conditions within the filters of date fields. From the following screenshot, the example shows that we want to apply a conditional format of a red dot to a date field named Contract Date. First, we only want to look at dates that are not empty and then we want, for the same field, to look for values that changed between two specific dates. The filter is only applied to issues where the date range includes field values where the latest change is within the two date values specified in the filter.
Day and Date display type fields have two basic ways in which they are used as query filters, depending on the operator you select.
Operator | Functionality | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Equals Greater than Less than Not equal Less than or equal to Greater than or equal to Between Empty Not empty |
When you choose one of these operators, two date input fields appear, each with a popup calendar. Depending on the operator you select, you will enter a date (and potentially time) into none, one or the two fields. When you select Equals, Greater than, Less than, Not equal, Less than or equal to, or Greater than or equal to then you only enter a date into the first field. When you select Between, you enter two dates. If you select Empty or Not empty, then you do not enter any date values.
When using a date field as a filter, the time of day is adjusted as follows when the time component is not entered as part of the filter:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
During After Before After or during Before or during |
When you choose one of these operators, you are presented with a number of date / time expressions which complete the filter. The list of valid expressions and their meanings is as follows. This table assumes that today's date is Friday, November 3rd, 2017 and the fiscal year setting of your installation starts on January 1.
|
* These expressions are not commonly used. If you require to use these, ask your administrator to turn them on within the data dicationary by setting the Filter Criteria of the expression fields to Yes.
On the Search/Report screen, you can type multiple issue ID’s into the Issue ID field. Separate these by a semicolon ‘;’.
There are occasions when it is useful to be able to search for a null value, or the absence of a value. For example, you may want a report where the filter is to be “show me all the records that have an empty customer field”. If the field is a list field, you can select the entry * None *. If the field is a text field, however, you can enter the character string {null}, to signify that you are looking for a null.
User fields have two options related to their ability to search, using the selected value or values as filters. First, there is an entry within the search list, * Current User *. When this entry is selected, the name of the user currently signed in is used as the filter value. If your administrator has enabled the option, there may also be an entry in the list, * Include Inactive Users *. By default, each list of users only includes activated users. When you select this option, the screen refreshes and the user list will include both active and inactive user names. If your installation uses pop-up selection for users, a checkbox will allow any search you make, to include inactive user names.
Checkboxes are odd in their behavior. This is because you, the user sees them as unchecked and checked. However, the underlying databases upon which ExtraView relies also sees them as not being set at all. To the databases, this is different value, termed a "null" value. Therefore checkboxes have three potential values, null, unchecked and checked. As far as possible, ExtraView treats the null value as being the same as unchecked. However, once a checkbox is checked, and then subsequently unchecked, its value remains as unchecked and never goes back to null. Querying for a value of unchecked within a checkbox, will also include looking for the null value.
Popup field lists are typically large lists. There is a button beside each popup field on the screens that present popup fields to the user. When this is clicked, there is a window that allows you to search in a variety of ways to select the value you are interested in using as a filter:
Within the entry field on the screen with the filters, you can directly enter the value you want to use as a filter. You may also use the asterisk wildcard within this entry field. However, note that if the field is a child within an allowed value relationship, you cannot use the Use Allowed Values in Filters checkbox option at the same time.
This feature is especially valuable for reports that are embedded within add and edit layouts. This allows you to set up a filter on a field which uses the value of a different field as the input. For example, a report that you embed may want to retrieve an issue where the ID of the current issue is equal to the value of the field named PARENT_ID within the current issue:
$$PARENT_ID$$ refers to the field name which contains the issue ID of the parent issue you want to retrieve.
Note that this only works with fields with a display types of text or number. However, if you would like this feature to operate with other field types such as list field display types, you can create a hidden text field on the layout in question and use a business rule to assign the value to that hidden field.
Both Container Reports and Dashboard Reports provide the opportunity to run multiple reports that give a single, combined output. Each individual report within Container Reports and Dashboards may, and probably will, have their own filters. However there are times where it can be useful to apply a single set of runtime filters to all the reports. Container Reports and Dashboard Reports give the opportunity to provide runtime replacement filters that are applied to all the reports on their output.
For example, you might want to apply a runtime replacement filter that applies the same date range to all the output, or apply a filter that provides only results from a specific product within the output.
When you specify runtime replacement filters within a Dashboard Report or Container Report, they will replace filters for the same field within the individual reports that they contain. If a runtime replacement filter at the Dashboard or Container report level does not appear within the contained report, it will have no effect. Further, it is not possible to specify conjunctions for the runtime replacement filters - they simply replace filters for the same field within the contained reports. The runtime replacement filters are always displayed in expanded mode, allowing you to make multiple selections within a field.
Tip: A typical use case might be to create a dashboard that contains several reports about all your products, but you want to filter on the report output by a single product. You cannot select a runtime replacement filter with a value similar to Product = Any, but you can select all the product values in the list and store that as your filter. Then, after viewing the dashboard, you can select a new filter value that is either just one, or a selection of products.