Help & DocumentationCreating Basic Metrics and Reports Bind VariablesHow do I use the :measurement_time bind variable?

How do I use the :measurement_time bind variable?

The :measurement_time bind variable can be used to create multiple versions of a Report for different date values. This article will show you how to use it.

These settings are specified at the Report Editor > Data Collection tab

1. Static Reports

Let's say you have a report whose fetch statement is the following SQL query:

Select Product, Sum(Sales) From Sales_Data

That would get you aggregate data for all time. If you want to see results for a specific date, say, January 1, 2013, you would modify the query to look like this:

Select Product, Sum(Sales) From Sales_Data Where Sale_Date = '2013-01-01'

2. Introducing the 'bind variable'

What if you want to see results forĀ an arbitrary date but don't want to specify the date right now? In that case, you would modify the query to look like this:

Select Store, Product, Sum(Sales) From Sales_Data Where Sale_Date = :measurement_time

The :measurement_time bind variable is a substitutable parameter whose value will be determined at run time.

3. Setting the value of the bind variable

Setting the value of the bind variable

When it's time to collect data, Metric Insights will substitute one or more values for the ':measurement_time' bind variable added to a SQL query. The Report designer determines what values will be substituted by making a selection from the Set :measurement_time value drop-down list. The range of choices depends on the Measurement Interval of a given Report as well as a pair of key settings in the Report Editor. (see next step).

For each distinct value of the bind variable, Metric Insights will execute the fetch command and create a new report instance.

NOTE: You can set a custom substitute value by clicking Add New Time Command at the bottom of the drop-down list.

4. Key settings in the Report Editor

When combined with the measurement interval (refresh rate), two key settings determine Report behavior:

  • Is it a Snapshot Report?
  • Can historical instances be backfilled?

4.1. Is it a Snapshot Report?

Is it a Snapshot Report?

Snapshot Reports are associated with keeping Report history and having the ability to compare data sets over time.

  • If this Report is not defined as a Snapshot Report (this field is set to 'no'), then only the most recent instance of the report will be retained
  • If it is a Snapshot Report (Report history is going to be kept), then an additional setting will be exposed below, namely, Can historical instances be backfilled? (see below)

4.2. Can historical instances be backfilled?

Can historical instances be backfilled?

This field is shown for Snapshot Reports only.

  • If the Can historical instances be backfilled? field is set to 'no', then only one instance of the Report will be computed at run time and it is required to set the value for the ':measurement_time' variable in the field below. It's important to note that while only one instance of the report will be computed at run time, a new instance of the report will be computed at each succeeding refresh interval. Since history is kept (the Snapshot Report? field is set to 'yes'), all instances will be retained. This technique can be used to create 'snapshots' of your underlying data at fixed time intervals.
  • If this field is set to 'yes', then multiple instances of the report can be computed at run time and the ':measurement_time' variable is defined automatically be the system.

0 Comments

Add your comment

E-Mail me when someone replies to this comment