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 Child 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, ony 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. 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.

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 / 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
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 Sunday, February 20th 2015 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 January 25th 2015 - February 14th 2014
Last 7 days EVDTF_LAST_7_DAYS Febraury 13th 2014 - February 19th 2014
Last fiscal quarter EVDTF_LAST_FISCAL_QUARTER October 1st 2014 - December 31st 2014
Last fiscal year EVDTF_LAST_FISCAL_YEAR January 1st 2014 - December 31st 2014
Last fiscal quarter to date * EVDTF_LAST_FQ_TO_DATE October 1st 2014 - November 20th 2014
Last fiscal year to date * EVDTF_LAST_FY_TO_DATE January 1st 2014 - February 20th 2014
Last month EVDTF_LAST_MONTH January 1st 2015 - January 31st 2015
Last month to date EVDTF_LAST_MONTH_TO_DATE January 1st 2015 - January 20th 2015
Last quarter EVDTF_LAST_QUARTER October 1st 2014 - December 31st 2014
Last quarter to date EVDTF_LAST_QUARTER_TO_DATE October 1st 2014 - November 20th 2014
Last week EVDTF_LAST_WEEK February 8th 2015 - February 14th 2015
Last week to date * EVDTF_LAST_WEEK_TO_DATE February 8th 2015 - February 13th 2015
Last year EVDTF_LAST_YEAR January 1st 2014 - December 31st 2014
Last year to date EVDTF_LAST_YEAR_TO_DATE January 1st 2014 - February 20th 2014
Next 4 weeks EVDTF_NEXT_4_WEEKS February 22nd 2015 - March 21st 2015
Next 7 days EVDTF_NEXT_7_DAYS February 21st 2015 - February 27th 2015
Next fiscal quarter EVDTF_NEXT_FISCAL_QUARTER April 1st 2015 - June 30th 2015
Next fiscal year EVDTF_NEXT_FISCAL_YEAR January 1st 2016 - December 31st 2016
Next month EVDTF_NEXT_MONTH March 1st 2015 - March 31st 2015
Next quarter EVDTF_NEXT_QUARTER April 1st 2015 - June 30th 2015
Next week EVDTF_NEXT_WEEK February 22nd 2015 - February 28th 2015
Next year EVDTF_NEXT_YEAR January 1st 2015 - December 31st 2015
This week to date EVDTF_THIS_WEEK_TO_DATE February 15th 2015 - February 20th 2015
This fiscal quarter EVDTF_THIS_FISCAL_QUARTER January 1st 2015 - March 31st 2015
This fiscal year EVDTF_THIS_FISCAL_YEAR January 1st 2015 - December 31st 2015
This fiscal quarter to date * EVDTF_THIS_FQ_TO_DATE January 1st 2015 - February 20th 2015
This fiscal year to date * EVDTF_THIS_FY_TO_DATE January 1st 2015 - February 20th 2015
This month EVDTF_THIS_MONTH February 1st 2015 - February 28th 2015
This month to date * EVDTF_THIS_MONTH_TO_DATE February 1st 2015 - February 20th 2015
This quarter EVDTF_THIS_QUARTER January 1st 2015 - March 31st 2015
This quarter to date EVDTF_THIS_QUARTER_TO_DATE January 1st 2015 - February 20th 2015
This week EVDTF_THIS_WEEK February 15th 2015 - February 21st 2015
This week to date * EVDTF_THIS_WEEK_TO_DATE February 15th 2015 - February 20th 2015
This year EVDTF_THIS_YEAR January 1st 2015 - December 31st 2015
This year to date EVDTF_THIS_YEAR_TO_DATE January 1st 2015 - February 20th 2015
Today EVDTF_TODAY February 20th 2015
Yesterday EVDTF_YESTERDAY February 19th 2015

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

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