Query Filters

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.

Filter Options

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.

  • Filter Repeating Row Values - If present, this setting controls the results returned by the query when the query filters contain one or more repeating row fields.  If checked, only the rows that match the filter criteria will be returned.  If not checked, all repeating rows are returned by the query, where at least one row matches the filter criteria
  • Filter Multi-Valued Field Values - When this option is checked, only the selected values in multi-valued list fields will be returned and displayed by the query, with all other values being suppressed
  • Use Allowed Values in Filters - When this is checked, and you select a filter of a parent field in an allowed valued relationship and then subequently select a filter of the child field in the allowed value relationship, only the valid child values will be displayed.  This does not apply if you choose the child value before you choose the parent value.  Also note this is not operable for the Business Area and Project fields, where all values will always be displayed
  • Allow Additional Keyword Filters - If you check this option, then a keyword search box is displayed on the query output.  You may enter additional search keywords within the search box on the output and these will be applied to the query and the query rerun with this keyword in addition to other filters.

Saving Filters for Future Use

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:

  • You may save either Standard or Advanced query filters, and on the Query screen, they will be recalled in the same manner they were saved
  • Saved filters may only be recalled within a report editor, when you are in Advanced query mode.  However, if you recall a set of Standard filters when you are in the Advanced mode, they are converted to Advanced filters
  • When in a report editor, and you are in Advanced query mode, you may save your filters.  If you are using a report hierarchy, the filters will be saved and can be used in any report editor, but will not be visible on the Query screen where reporting hierarchies are not supported
  • Within a report editor, you can either recall a set of saved filters to replace all the current filters, or can recall a set of saved filters to be inserted at any point within the filters currently chosen.  To replace all the filters, simple choose from the * Saved Filter List *, to insert a filter choose the option within the field list titled * Insert Saved Filters *:


    Saved Filters within a Hierarchical Report Editor screen
     

  • You are warned if you are about to overwrite current filters with saved filters and given the opportunity to cancel.

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.

Standard Query Mode

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.

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.

Composing Complex Queries

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)

Advanced Query Filter Operators

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.

Advanced Query Filter Conjunctions

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 and Advanced Queries

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.

Querying for Inactive Values

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.

Querying with Date Ranges and the Changed Operator

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.

Querying with Date / Time Functions

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:

  • For all filters, the user's time zone is utilized
  • For the equals, greater than, greater than or equals operators, the end of the day is used as the precise point in time
  • For the less than, less than or equals operators, the beginning of the day is used as the precise point in time.
  • The between opeator is inclusive of the 2 dates provided in 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.
Title Data dictionary field name Expression resolves to
Last 4 weeks EVDTF_LAST_4_WEEKS October 1st 2017 - October 28th 2017
Last 7 days EVDTF_LAST_7_DAYS October 27th 2017- November 2nd 2017
Last fiscal quarter EVDTF_LAST_FISCAL_QUARTER July 1st 2017 - September 30th 2017
Last fiscal year EVDTF_LAST_FISCAL_YEAR January 1st 2016 - December 31st 2016
Last fiscal quarter to date * EVDTF_LAST_FQ_TO_DATE July 1st 2017 -  November 3rd 2017
Last fiscal year to date * EVDTF_LAST_FY_TO_DATE January 1st 2017 -  November 3rd 2017
Last month EVDTF_LAST_MONTH October 1st 2017 - October 31st 2017
Last month to date EVDTF_LAST_MONTH_TO_DATE October 1st 2017 - November 3rd 2017
Last quarter EVDTF_LAST_QUARTER July 1st 2017 - September 30th 2017
Last quarter to date EVDTF_LAST_QUARTER_TO_DATE July 1st 2017 - November 3rd 2017
Last week EVDTF_LAST_WEEK October 22nd 2017 - October 28th 2017
Last week to date * EVDTF_LAST_WEEK_TO_DATE October 22nd 2017 - November 3rd 2017
Last year EVDTF_LAST_YEAR January 1st 2016 - December 31st 2016
Last year to date EVDTF_LAST_YEAR_TO_DATE January 1st 2016 - November 3rd 2017
Next 4 weeks EVDTF_NEXT_4_WEEKS November 5th 2017 - December 2nd 2017
Next 7 days EVDTF_NEXT_7_DAYS November 4th 2017 - November 10th 2017
Next fiscal quarter EVDTF_NEXT_FISCAL_QUARTER January 1st 2018 - March 31st 2018
Next fiscal year EVDTF_NEXT_FISCAL_YEAR January 1st 2018 - December 31st 2018
Next month EVDTF_NEXT_MONTH December 1st 2017 - December 31st 2017
Next quarter EVDTF_NEXT_QUARTER January 1st 2018 - March 31st 2018
Next week EVDTF_NEXT_WEEK November 5th 2017 - November 11th 2017
Next year EVDTF_NEXT_YEAR January 1st 2018 - December 31st 2018
This week to date EVDTF_THIS_WEEK_TO_DATE October 29th 2017 - November 3rd 2017
This fiscal quarter EVDTF_THIS_FISCAL_QUARTER October 1st 2017 - December 31st 2017
This fiscal year EVDTF_THIS_FISCAL_YEAR January 1st 2017 - December 31st 2017
This fiscal quarter to date * EVDTF_THIS_FQ_TO_DATE October 1st 2017 - November 3rd 2017
This fiscal year to date * EVDTF_THIS_FY_TO_DATE January 1st 2017 - November 3rd 2017
This month EVDTF_THIS_MONTH November 1st 2017 - November 30th 2017
This month to date * EVDTF_THIS_MONTH_TO_DATE November 1st 2017 - November 3rd 2017
This quarter EVDTF_THIS_QUARTER October 1st 2017 - December 31st 2017
This quarter to date EVDTF_THIS_QUARTER_TO_DATE October 1st 2017 - November 3rd 2017
This week EVDTF_THIS_WEEK October 29th 2017 - November 4th 2017
This week to date * EVDTF_THIS_WEEK_TO_DATE October 29th 2017 - November 3rd 2017
This year EVDTF_THIS_YEAR January 1st 2017 - December 31st 2017
This year to date EVDTF_THIS_YEAR_TO_DATE January 1st 2017 - November 3rd 2017
Today EVDTF_TODAY November 3rd 2017
Yesterday EVDTF_YESTERDAY November 2nd 2017

* 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.

Querying for Multiple Issue Numbers (ID's)

On the Search/Report screen, you can type multiple issue ID’s into the Issue ID field. Separate these by a semicolon ‘;’.

Querying for Null or No Values

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.

Querying with User Name Fields

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.

Querying with Checkbox Fields

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.

Querying with Popup Field Lists

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:

  • You can type part of the value, and use an asterisk as a wildcard to search for all values that match.  You can then select the value from those returned
  • There is an option to show disabled entries in the list and to then allow you to search for disabled values that exist in your issues
  • A string of letters and numbers are presented that represent the first charachter of the possible values you can select.  Click on any letter or number to see all the values that begin with that character
  • You can select the value * Any *
  • You can select the value * None *

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.

Queries that Use Values of Different Fields within Filters

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.

Runtime Replacement Filters

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.