System Filters Available For Query

System filters are text strings that you use to specify a subset of the data items in an internal or SQL database data type. The filter in a query is an SQL WHERE clause that provides a set of comparisons that must be true in order for a data item to be returned. These comparisons are typically between field names and their corresponding values. In QMetry, we have provided a lot of system-defined filters available to users to generate a custom report with the required output. As a best practice, QMetry recommends creating queries using filters so that you can fetch the records easily which also saves your time in fetching the required records instead of all the unwanted records.

What are the benefits of using system filters?

  • It reduces the manual effort on specifying the query filter values/options while creating the SQL statements to generate a custom report; so that your queries are not complex and maintains good readability.

  • You must always include a filter (i.e. Project filter specified as: `@filter.project`) in your custom SQL queries. This will prevent the recipients of the shared report gadgets from inadvertently viewing data from other projects that they do not have access to.

  • A gadget created from a custom query can be easily modified and reused.

  • The system filters gives more power to end-user to select their own filter criteria against which they may want to view the report data.

  • The records are easily fetched when the filters are used in SQL queries; which gives a faster performance retrieving the data from DB schema.

The following are system supported dynamic filter parameters that can be used in the query. With the inserted parameter in the report query, you will be asked to select the filter option before the report is generated.

For example, @FILTER.PROJECT is used in the report query. The user will be asked to select a specific Project to generate the report accordingly. Similarly to be able to filter by date use a @Filter.date

IMPORTANT :

  • Project filters must be specified in the format : `@Filter.project` ( ` is not a single quote; it's an acute/back quote ).

  • Project filter @FILTER.PROJECT is mandatory if you want to use any other system-defined filter except Date filter @Filter.date

  • Project filter @FILTER.PROJECT and Release filter @FILTER.RELEASE are mandatory if you want to use Cycle filter @FILTER.CYCLE .

Filter

Description

Filters work on

@FILTER.PROJECT

Filter report on a specific Project.

ID



@FILTER.RELEASE

Filter report on a specific Release.

@FILTER.CYCLE

Filter report on a specific Cycle.

@FILTER.USER

Filter report on a specific User.

@FILTER.BUILD

Filter report on a specific Build.



@FILTER.EXTERNAL PROJECT

This is the integrated Jira project with QMetry.

value



@FILTER.ISSUE PRIORITY

Filter report on Issue Priority e.g., Blocker, Critical, Major, etc.

@FILTER.ISSUE TYPE

Filter report on a specific Issue Type e.g., Bug, Enhancement, New Feature, etc.

@FILTER.ISSUE STATUS

Filter report on a specific Issue Status e.g., Open, Reopened, Resolved, Closed, etc.

@FILTER.ISSUE OWNER

Filter report on the owner of the issue.

@FILTER.TESTCASE PRIORITY

Filter report on Test Case Priority e.g., Blocker, Critical, Major, etc.

@FILTER.TESTCASE STATUS

Filter report on Test Case Status e.g., New, Open, On Hold, Rejected, In Progress, etc.

@FILTER.TESTCASE LABEL

Filter report on Test Case Label e.g., Sprint1, Sprint2, etc.

@FILTER.TESTCASE TYPE

Filter report on Test Case Type e.g., Functional, Performance, Regression, etc.

@FILTER.REQUIREMENT PRIORITY

Filter report on Requirement Priority e.g., Blocker, Critical, Major, etc.

@FILTER.REQUIREMENT STATUS

Filter report on Test Case Status e.g., New, Open, On Hold, Rejected, In Progress, etc.

@FILTER.REQUIREMENT OWNER

Filter report on the owner of the Requirement.

@FILTER.TESTING TYPE

Filter report on the Testing Type of the Test Case e.g., Manual, Automation.

@FILTER.PLATFORM

Filter report on the Platform linked to the test suite.

@FILTER.JIRA ASSIGNEE

Filter report on the Assignee of Jira issue.

@FILTER.JIRA REPORTER

Filter report on the Reporter of Jira issue.

@FILTER.APPROVAL STATUS

Filter report on Test Case Status when the eSignature feature is On.

@FILTER.JIRA RESOLUTION

Filter report on the Resolution marked in Jira.

@FILTER.TESTSUITE STATUS

Filter report on the Test Suite Status after the execution.

@Filter.execution status

Filter report on the Test case execution status.

@Filter.date

Filter report on date. 

e.g. to specify execution start date : testexecutions.tcexecutionEndTime >= (`@filter.date Execution Start` )

 

SQL Query

Test Case Execution Count By Status Over Execution Date

  • If any of the above filters are marked in the report query, then the user will be asked to select the filter option/values to apply it to the report.

  • Depending on how the user has selected or retrieved the data - a single select or multi-select dropdown is generated.

Apply the Filter

When filters are inserted in a query and you run the query, the filters get added to the Filter panel to filter the report data.

The Filters have "Select All" and "Unselect All" options (on the fields containing a list) to deal with all the filter options at a time.

The values/criteria for filters provided during query execution will be saved and will be applied as default to the gadget added on the dashboard. It is to be noted that when you share this report with an end-user, then the filters will work based on the project access of the user.

Once the filter is applied to the report, users can modify the same from the dashboard. 

Filter On `text` Field(s)

The filters can also be added to the `text` field to get the required output. here’s how you can do it.

  • Considering the above example, if you want to give end-user an ability to generate the report by filtering on the specific test case execution status, the same can be added in a `WHERE` clause as described below :

    • Syntax : <tablename>.<columnname> IN (@`filter.<tablename>.<columnname>`)

    • Example : testexecutions.tcExecutionStatusName IN (`@filter.testexecutions.tcExecutionStatusName`)

    • SQL Query

      SELECT DATE_FORMAT(testexecutions.tcExecutionEndTime, "%m/%d/%Y") "ExecutionDate", testexecutions.tcExecutionStatusName "Execution Staus", COUNT(1) as "Test Case Count" FROM testexecutions WHERE testexecutions.projectID in (`@filter.project`) AND testexecutions.releaseID in (`@filter.release`) AND testexecutions.cycleID in (`@filter.cycle`) AND testexecutions.tcExecutionEndTime >= (`@filter.date Execution Start`) AND testexecutions.tcExecutionStatusName IN (`@filter.testexecutions.tcExecutionStatusName`) GROUP BY DATE_FORMAT(testexecutions.tcExecutionEndTime, '%m/%d/%Y'), testexecutions.tcExecutionStatusName

       

    • Apply the Filter


      The Filter added on a text field in a query allows you to specify multiple values in a filter option. The multiple values can be specified by ‘pressing enter after typing’ (i.e. add first value ⟶ press enter ⟶ add next value ⟶ press enter and so on).

Note : If a filter is not applied, then the report shows data of the current project and of last 1 month.

Query Output

 

Click Apply to apply the filters. Else, click Reset to clear the applied filters.