New in v1.3
You can use template-variables to dynamically build the statement of your SQL, MDX or DAX queries. Unlike the
Parameters in queries
, the template-variable inputs are treated as portions of executable code.
When possible, we highly recommend to use the parameters and not the template-variables. The template-variables are more flexible to build dynamically your queries but are obfuscating your tests.
When creating a query with template-variables, you identify the them by putting some dollar signs ($) before and after the template-variable's name. For example, $Year$ would be a valid template-variables name.
In your test definition, in addition of the element query
, you'll also need to describe your template-variable by defining its name and value within an element named
select * from Customer where CustomerKey $OperatorVar$ (1,2,3)
The query executed by NBi will be
select * from Customer where CustomerKey not in (1,2,3)
Template-variables defined at the test-suite level
Sometimes you want to define a few template-variables at the test-suite level. It's useful when these template-variables are used in more than one query and their values are constant through the test-suite. This can be achieved by the usage of the element
in the default
of the settings
Within the code snippet here under we're defining a template-variable named
. This Clause
applies a slicer to limit the cube to the years 2010 to 2013.
This template-variable can be used in the following two queries without problems:
$Clause$ on 0,
[Measures].[My Value] on 1
[Measures].[My Value] on 0
If a template-variable is defined at the test-suite level and at the query level for a test, the definition at the query level will be used for this test.
If a template-variable is not used within a query but is provided to this query, this template-variable is simply not used by NBi (so it's not a problem).