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 defining specific comparisons that must be true in order met for a data item items to be returned. These comparisons are typically between field names and their corresponding values. In QMetry, we have provided a lot of QMetry offers several system-defined filters available to that enable users to generate a custom report with the required output. As a best practicereports with precise outputs. For efficiency, 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 recordsusing filters to create queries, as this helps quickly retrieve only the required records, avoiding unnecessary data retrieval.
What are the benefits of using system filters?
It reduces the manual effort on of 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 maintain 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 give 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.
...
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@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. | |
@requirementFolder | Filter report based on requirement folder | |
@testcaseFolder | Filter report based on testcase folder | |
@testsuiteFolder | Filter report based on test suite folder | |
@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
| Info |
|---|
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.
...
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
Code Block language sql 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).
...
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.
...