Home Product Integrations Customers Company Jobs Resources SQL School Playbook Sign In

Dynamic Parameters: Save Time Writing Picklists

When analysts create reports for teams throughout their company, there are inevitable follow-up questions. Engagement is higher on mobile, but can we narrow this to the iPhone 5? Thanks for the Customer Account Health report you sent me last month, but could you add the customers we signed up last week?

With query parameters, Mode makes it easy for analysts to set up interactive reports that enable others to self-serve answers to follow-up questions like these. But, maintaining these forms can become tedious. An account list dropdown, for example, could have many, constantly changing values.

We just made picklist maintenance easier with a new feature we’re calling dynamic parameters. You can now use the results of one SQL query to populate the dropdown menu for a parameter in another query. As the first query’s results update, so too does the parameter dropdown menu. Here’s how to set them up in a flash.

Dynamic Parameter Example Report

How to Build a Dynamic Parameter

Take a look at this example report. It looks at monthly sales performance by account and includes three parameters: Start Date, Period, and Account Name. The Account Name picklist of customers in this example includes tons of accounts—and would be painful to update manually as the sales team closes new accounts. Instead, we’ll use a query to keep the list up-to-date at all times.

Click into the Details tab and you’ll see the report consists of two queries. Click into “1. Ream Sales” and then click “Show parameter template code.” Here you’ll see {{ period }} , {{ start_date }} , and {{ account_name }} . Below the query you’ll see the typical parameter definitions—but you’ll notice {{ account_name }} is missing.

Parameter Example Query Details

Unlike standard parameters, which are written directly into the query you want to customize (as {{ period }} , {{ start_date }} are here), you’ll define the dynamic parameter in a separate query.

Now click into the query named “2. Dynamic Parameter Query” to see the simple query that gets the names of the accounts.

Dynamic Parameter Query Details

Then, just add the typical parameter code below your query. Here you’ll see it’s called {{ account_name }} which matches the parameter name the “1. Ream Sales” query. And, just like that, your report is ready to go!

For a refresher on how to write a form field in Mode, check out the Adding Parameters help doc. You can define both the values that populate your picklist, and also create a label to determine how they will appear in the final report.

Taking Parameters Further

There’s a lot more you can do with Mode’s parameters to make it easy for report viewers to tailor their own reports and quell repetitive requests. Try cloning the example report to experiment with other ways to write a dynamic parameter, or jump into Mode to experiment with these new reporting features.

If you have any questions, reach out to us at hi@modeanalytics.com—we love hearing from you!