Reusable Filter & Query Templates, To Save Hours Of Report Creation Time

Update: This feature has been deprecated with the newer version of Holistics

Does your team have to manage numerous data assets, going through the manual labour of modifying each of them one at a time? Holistics’ reusable Filter and Query Templates lets you easily modify hundreds of reports, by just changing a single template.

What Should You Use This For?

Filter and query templates in Holistics let you easily update multiple business intelligence reports and dashboards in one shot, as changes to these underlying templates get passed to all linked reports.

Manage many data assets and the reporting workflow of your company with ease. Both these advanced features are especially useful for data teams that have to manage many data assets for their company, allowing them to easily modify and update them without repeating work that’s already been done.

How It Works

Filter Templates

We will be using a regional dashboard as an example. We are looking to add new options to the dropdown filter in this dashboard. The dashboard dropdown filter currently only has three country options: Singapore, Malaysia and Indonesia.

We want to add new rows of filter options (Thailand, Vietnam and Phillippines) that apply to all our other reports in this regional dashboard.

Create a new filter template: From the Filter Templates menu, add a new filter template. In this dropdown filter example, there are two main ways to update filters. - Update it manually using manual entries. - Get values from your database table automatically, by using SQL or by filling in the Table Name (e.g. rideshare.countries), Value Field (e.g. country_code) and Text Field (e.g. country_name).

Filter Templates main menu.

You can preview how your filter will appear, before saving it. Now all your reports and dashboards using this filter template will get the updated values, like this expanded list of countries in the example, from the original template of three countries.

Call your filter template variable inside your report SQL: Click on + Add Filter in a report, and select your newly created filter template from the dropdown list of filter templates. Keep or modify the filter variable name assigned to the template, which is {{country}} in this example, and call it inside your report’s SQL query:

select * from rideshare.drivers_table where [[ country_code in ( {{country}} ) ]]

Add your Filter Template to your Holistics report.

Query Templates

Save SQL codes as a variable to reuse elsewhere and in your new reports, so you don’t have to retype the same query each time.

Create a new query template: From the Query Templates menu, add a new query template. Fill in the Name, Description and add the SQL query you would like to reuse into the Template Content section, and save the template when you’re ready. More advanced use-cases can include Variables, to let you pass report specific queries or even filter templates as variables, inside your Query Template.

Query Template main menu.

Add the query template to your reports: When editing your reports, click on the Query Template dropdown menu, and select the query template you had saved earlier. In this example the query template is used in the query as {{ @revenue() }}. When you run your report and check the Executed Query tab below, you’ll see that your SQL code had used the query you saved in your template, wherever you inserted the query template variable in your report query.

Query Templates can be easily reused and modified in Holistics reports.

More Creative Use-cases

  • Switch between different customer schemas for multiple reports, by changing a single line of code in Query Templates.
  • Save common table expressions as Query Templates for PostgreSQL databases.
  • Add access permissions to limit Filter Template options for users.

Holistics allows you to work smarter, with reusable Filter & Query Templates!

Sign up for a free trial to start using our Filter & Query Templates! You can read more about Filter Templates and Query Templates in our documentation.

Holistics believes in empowering data-driven teams. With the right tool, every individual can contribute in ways unimaginable to their team. We empower you by making our software flexible, intuitive and user-friendly. And we happen to be able to automate the data processes for you, so you don’t have to do the mundane tasks over and again.