Back to QMetry All Products Help Page
Auto-Generate SQL with QI
Introduction
QI Query Assistant allows users to generate custom SQL reports directly from QMetry Insight using QMetry Intelligence (QI). The Query Assistant simplifies reporting, enabling users to create, modify, or understand SQL queries without requiring extensive SQL knowledge.
The Query Assistant supports the following three use cases:
Generate a New Query - The QI Query generator creates SQL queries based on user-defined reporting needs expressed in simple English.
Updating an Existing Query - The QI Query generator can help users modify existing SQL queries by adding fields and filters or implementing new logic and operators.
Explaining Query - The Query Assistant empowers users to understand SQL queries by providing explanations of their logic, tables and structure.
Best Practices
All features operate according to the QMetry reports schema available in QMetry Insights.
As a best practice, users must clearly describe their reporting needs in detail, specifying exact field names and conditions or operations. The Query generator currently does not support the user-defined fields and works only with system fields.
Permission, Configuration and Settings
To activate the Auto-generate SQL queries, the super admin needs to enable it for the QMetry instance from AI Configuration under Customization.
Only super admin can access and configure QMetry Intelligence Configuration.
The default setting for QI feature is disabled.
Auto-Generate SQL with QI
Once the super-admin enables the Generate SQL Reports, the Classic View and QI view buttons becomes available on the advanced query reports screen.
How it Works?
Upon activating, the "QI View" becomes available under the Advanced Query Reports. The QI View provides instructions and best practices for generating queries. Users can switch between the Classic SQL generation view and the QI View as needed.
Auto-Generate SQL with QI
To auto-generate SQL with QI, perform the following steps:
Go to Advance Query Reports Screen.
Click QI view to access the Generate SQL Using QI.
Provide a concise description of the reports to generate in plain English.
Add the filters as prompted. A list of predefined filters can be found in the Filters section on the left side of the screen.
After adding the filters click Generate Query.
Once the query is generated, click Fetch Filters and Verify to confirm.
Select the project and click Run Query.
Review and validate the results.
Generic Prompts Examples
Simple Prompts
List out all the requirements with releasename = 'Production 1.0' and cyclename = 'Alpha 1.0'
Fetch all the requirements that are linked with test cases
List out all the test cases with releasename = 'Production 1.0' and cyclename = 'Alpha 1.0'
Fetch all the test cases that are linked with test suites
Fetch all the test executions which are having approved status
List out all the 'failed' or 'blocked' test executions having releasename= 'Production 1.0' and cyclename ='Alpha 1.0' that have 'open' bugs of 'critical' priority
Fetch all the issues having 'blocker' priority and 'open' status
List all the Issues which are linked with test cases
Fetch details like user alias and last login time for all the active users
Complex Prompts
Need a report of testexecutions of releasename='xyz' and cyclename ='abc'. Also include issue details like status, the count of impacted test cases, and a list of the impacted test cases per issue for the test executions.
Provide entity key and summary of defects found during the execution of test cases, categorized by their status, priority. Issues should have requirement linked.
Report that shows total count of executions executed grouped by execution status.
Generate a report having cumulative totals for executions executed daily and grouped by execution status.
Update QI Generated Query
Request further modifications and provide specific instructions for the desired changes to the query.
Examples:
Modify the query to showcase "Blocker" issues as "Blocker, Critical, and Medium."
Change a query displaying the Test Case Key and Summary to include Test Case Labels, Priority, and Status.
Update a query to show test cases linked to test executions having issue linked.
Add a filter for 'Execution Status' and 'Platform' alongside existing filters for Project, Release, and Cycle.
Group results by Execution Status or Platform.
Sort issues by ‘created date’ in descending order.
Provide Feedback
To provide feedback on QI generated SQL query, click Thumbs Up or Thumbs Down. A pop-up appears with predefined options, allowing you to choose or submit custom feedback. This helps improve the model's accuracy and performance.
To submit feedback beyond the predefined options, select "Other" and enter your feedback in the provided pop-up.
Reset session
Users can reset the SQL query generation page by clicking on the reset button.
Prompt History
Users can access the last three prompts for quick reference by clicking the Prompt History.
Explain Query
The QI Explain Query feature empowers users to understand SQL queries by providing explanations of their logic, tables and structure. This enables users to comprehend the purpose and functionality of the query, aiding in debugging and troubleshooting.
The explain query feature enlists detailed explanations of the -
Purpose of Query: A clear and concise explanation or description of what the query does.
Original Tables Involved: Enlists all the tables that are referred to or used in the query.
Columns Affected: Enlists all the columns that are being referred to in the query.
Filters Applied: System filters that are part of the query are enlisted.
QI Statistics
System administrators can monitor the usage of the SQL generator feature for all users.
Statistics for creating and updating reports using SQL queries generated by QI are accessible in AI Configurations.
Users can hover over the QI Statistics button to view detailed information on the usage of the QI query generator.
Identify QI-Generated Reports
Reports created using SQL queries generated using QI will display the QI logo when added to dashboards. The 'Custom Gadget' list view includes a 'QI generated' column to identify reports generated with SQL queries from QI.
Back to QMetry All Products Help Page