Loading parameters can be done in three different ways:
1) Loading parameters via JSON
Static parameters are entered manually in JSON format. Every object (separated with a comma) will result in one report message being sent where each parameter is replaced with the corresponding value. You may create as many JSON entires, containing different parameter:value pairs as you like.
Sample JSON:
[
{
"client_name": "John Doe",
"email_recipients": "john.doe@pushmetrics.io",
"slack_recipients": "John Doe (U4FPP5UU8)",
"filter_name": "Car_Brand",
"filter_value": "Honda"
},
{
"client_name": "Dave Doe",
"email_recipients": "dave.doe@pushmetrics.io",
"slack_recipients": "general (C4FPP5UU8)",
"filter_name": "Car_Brand",
"filter_value": "Toyota"
}
]
2) Loading dynamic parameters via SQL Query
To load your parameters from an SQL database you may write your query here.
Every row returned by the query is going to result in one report message.
And every column will be turned into a parameter that may be used in anywhere in your report.
Consider the following query:
The Parameters created from your database are:
email_recipient -> from the field costumer_email
first_name -> from the field costumer_first_name
order_growth -> calculated from orders_last_week and orders_this_week
Thus, using in the recipient field, will result in a report sent to each of your recipients returned in the query.
This has a few powerful implications:
- You can pull a distribution list dynamically — at runtime — from your database.
- If no rows are returned, nothing is sent.
- Only returned rows produce a message You can turn a normal report into an alert rule just by adding a WHERE or HAVING clause to the query. This way you can implement any alerting logic that can be put into a SQL statement
- Parameters can serve as meta data to filter charts & dashboards You can pull filter values via SQL and then apply them to charts, tables & dashboards to send out personalized data visualizations .
- The message can be tweaked depending on the data
You can bring in conditional formatting and even change entire sentences depending on KPI values. This enables a high degree of personalization and relevance. You can do this by using CASE statements in the query or
{% if %}functions in the message template.
Prerequisites:
To load parameters via SQL query you need to have
a) an SQL database connection and
b) a saved query that returns the parameters
Once you select a query from the list of saved queries, the query is executed with LIMIT 1 applied to fetch the column names.
If the query returns data, the column names are made available as auto-complete parameters throughout the report.
If no data is returned at this point, you can still use the parameters but you will need to type them yourself.
Go to Message & Decorators to see how these parameters are applied in your Message.
3) Import parameters from Tableau View
To pull parameters from a Tableau View you simply create a Tableau visualisation of the type ‘Text Table’. This Table view should contain all information you would like to use in your reports. Keep in mind that for every row returned one report will be created by PushMetrics.
Once you have that view published in your Tableau Online or Server, please refresh connection in the Pushmetrics application on the Data Connections page: https://app.pushmetrics.io/administration/data-connections (green button).
You can now load your Tableau View in the Parameters section:
To see a preview of your data you may click the View Data button:
Every column can be used as a parameter, every row creates a report.
You can use column names as parameter by surrounding it with {{ }} ; as explained in detail here.
