A couple of months ago, I posted about a technique I have used for developing complicated dashboard reports: store the code in a template file, and take advantage of the developer tools and GitHub sync that feature provides. (That writeup includes more details about the advantages this provides.)
More recently, I wondered… “well, why can’t we leverage that same mechanism for query reports?” And it turns out that the answer is… we can.
Example
As a starting point, let’s imagine a simple SQL query report that matches some of the users who live in the New York City area:
This example is very simple, but given the many town names found in Queens, the query is going to grow quickly, and may require careful editing over time.
If two people edit the list simultaneously, they might overwrite each other’s work, and there’s no easy way to look back and see when particular values are added or removed from the list.
Creating a Template File
In the ActionKit admin, click in to Appearance > Templatesets, and choose an appropriate templateset; for this example we’ll assume that you”re working with the templateset created in the earlier post, named “Admin”.
Create a new template file; for this example we’ll call that sql_users_from_boros.html. (It would be nice if we could give this file a .sql extension, but that currently isn’t allowed.)
Then copy the SQL for your query report into that new file and publish it:
Invoking a Template File
Finally, we can replace the contents of our query report with an invocation of this template:
When we run the query, we’ll get the same results as we did originally — but our SQL code now has a revision history:
Furthermore, if we’re storing our templates in a GitHub repository, these SQL query report changes can be staged, deployed, tracked, and rolled back in the same way we’re already managing the user-facing template files.
Passing Parameters
If code in a regular query report needs to prompt the user to enter parameters, it can just use the curly-bracket syntax, fieldname = {{ param_name }} to create implicit parameters.
However, ActionKit doesn’t look for those implicit parameter references in the code being loaded from our template file, so we’ll need to explicitly define the parameter with the {% required_parameter "param_name" %} tag.
The example below shows a template file and a query report that work together and support a parameter.
Admin/sql_users_for_statecode.html Template:
Associated Query Report:
When we run the query report, we’re prompted for a parameter value:
Conclusion
This technique lets us move the SQL for some of query reports into template files, where they can have a detailed revision history and can be managed in the same way as our user-facing template files, including using desktop code editors and Git synchronization.
Leave a Reply