In order to use SQL Reports, your site administrator must have set up a read-only database user, and configured QATrack+ to use this read-only user in it’s DATABASES setting.
You must also set USE_SQL_REPORTS = True in your local_settings.py file. If you are using SQL Server your readonly database options may also need to set AUTOCOMMIT = False.
As of version 3.1.0 QATrack+ has a tool for creating, running, and storing SQL database queries/reports. To access the tool, select the Query Tool option from the Trends & Analysis menu.
From this main query tool page, you can select an existing query to run, or create a new one using the New Query or Playground links in the sidebar.
Currently, to access this tool a user must have the “Can Run SQL Reports” or “Can Create SQL Reports” permission set on their account.
Creating a new Query¶
If you want to run ad-hoc queries or develop a new query, you can use the Playground area to run queries without saving them to the database for later use.
Give your query a title and description and then write your query in the SQL text area:
Once you have written your query you can click Save & Run to generate your query. This will save the query for later use and run it now placing the results at the bottom of the page:
To add a configurable parameter to use in the query add a symbol like $$your_param_name$$ to your query. For each parameter an input box will be added to configure the query. You can use $$your_param_name:default_value$$ to have a default value filled in:
Exporting the query¶
If you would like to download your data, simply use the drop down on the Save & Run button to export your data to CSV, Excel, or JSON format.
Viewing the DB Schema¶
You can view the database tables and columns available by clicking on the Show Schema button. A full database diagram is also available in the developer documents: Database Schema.
After running a query you can click on the Pivot link at the top of the results table to generate a pivot table for aggregating and summarizing the data. Note the link at the top right can be shared with others to link directly to this pivot table.
Deleting a Query¶
In order to delete a query, navigate to the Saved Queries page and click the trash can icon next to the query.