BW Queries with Variables

The EPM plug-in supports queries without variables, and queries with the following types of variables: dimension member, hierarchy, hierarchy node, formula, text, date.

When you select a query that includes variables, the Set Variables dialog box opens and enables you to specify the values for the variables. Depending on the variable definition in BEx Query Designer, you can have various variable types:

Note The mandatory variables are displayed in bold.

Dimension member. Select one or more members, by clicking the icon and using the Member Selector that opens.
Hierarchy. Select a hierarchy from the dropdown list.
Hierarchy node. Select one or more hierarchy nodes, by clicking the icon and using the Member Selector that opens.
Formula. Enter a numeric value for the formula. The numeric value you enter is used for key figure calculations defined in BEx Query Designer.
Text. Enter a text. You can enter a text to rename a header in the column or row axis; or to change the description of a key figure.
Date. You can select a date in the calendar or enter it directly. The date format is the one defined in the regional settings of your operating system.

The values specified can be then viewed in the Variables tab inside the Report Layout tab of the EPM pane. The Variables tab is displayed only if variables exist.

Variable Read Mode

In the Set Variables dialog box, you can choose the read mode setting for each variable:

Master Data On: To apply this mode, click the cube icon to make it active. When opening the Member Selector, all the members from the master data table are avaible for selection: these are the master data table values.
Master Data Off: To apply this mode, click the cube icon to grey it out. This is the default mode. When opening the Member Selector, only the members that are in the InfoProvider are avaible for selection: these are the booked values.

Note The variable read mode also applies to planning functions and planning sequences.

Selecting Variables

To select query variables, use the Member Selector that opens when you click the button next to the variable in the Set Variables dialog box.

The list of members available for selection depends on the variable read mode applied. See the section below.

Entering Variables Manually - Syntax

Alternatively, you can manually enter any query variable of type dimension member and hierarchy node, using a specific syntax.

Item	Syntax	Example
Display Key	value	DE
key	"value"	"DE"
not compounded key (if the dimension is compounded)	(value) or (value1[]value2)	(BAY) or (BAY[]BAY)
operator less than or equal to	<=	<=DE
operator greater than	>	>DE
operator greater than or equal to	>=	>=DE
inversion (or different) operator	<>	<>DE
between operator	[]	CA[]DE
not between operator	][	CA][DE
several values	;	DE;">CA";"FR[]IT"

The syntax is also directly available from the dialog box in which you set the variables, by clicking the “i” icon.

Note You can also use this syntax:

when defining the parameters variableValue and members for the APIs SetPlanningFunctionVariable and SetPlanningFunctionFilter.
when manually entering variables for planning functions or planning functions filters.

Note that non-compounded keys are not supported in this version.

Note When selecting a variable value using the Member Selector, the key is taken into account.

Query Variable Merge

You can set a variable only once for all the queries you are connected to. To do so, you merge the variable.

Let's say you use a workbook with several queries that have one variable in common: Country. As you always work for France, you want to select the value France for the variable Country only once and for all the queries in the workbook. To do so, you simply perform a merge on the variable Country and select the value France.

Mergeable variables: you can merge variables for all the queries of all the workbooks of the current Microsoft Excel instance used in the workbook, provided that the variables have the same technical name and are of the same type.

Note the technical name of a variable is displayed in the tooltip displayed when mousing over the description of the variable in the Set Variables dialog box.

You merge variables in the Set Variables dialog box.

There are two ways of merging variables in the Set Variables dialog box:

Merge all the variables: if you want to merge all the mergeable variables for all opened connections, check the Merge All option.
Note whenever another mergeable variable is added to BEx Query Designer, the variable is not merged automatically in the EPM plug-in.

All the checkboxes of the variables are automatically checked.

When you uncheck one variable, the Merge All option is automatically unchecked.

When you uncheck the Merge All option, the checkboxes of the variables are unchecked.
Merge variable by variable: You can merge or unmerge one variable at a time.
To do so, check or uncheck a variable in the Merge Selected column.

Once you have merge variables, you only need to select a value once. The value (France) is taken into account for all the merged variables Country.

Note The merge of query variables behaves as follows:

When you save a workbook, the merged variables and the values set for the merged variables are saved along.
When you have merged several variables and you then open a new connection to a query, if its variables correspond to the merged ones, the variable values already set are automatically applied.
When you set a new value for a merged variable, this change is applied to all connections using this variable.
When you unmerge a variable, the unmerged variable keeps the same value, the variable is no more considered as merged and therefore its value is not applied to other connections.
If a variable is merged in a connection and you open a workbook where the same variable is not merged, both variables are considered as merged and the value of the first connection is applied to the second one.
If a variable is not merged in a connection and you open a workbook where the same variable is merged, both variables are not considered as merged and the value of the second connection is not applied to the first one.

Changing Query Variables at Any Time

You can change the selection of query variables at any time.

To do so, use Change Variables in the EPM tab of the Ribbon:

To change the variables for the active query alias, select Change Variables Change Variables for Active Query . This command is available for selection only if the active query alias – that is the query used in the query alias that is currently selected in the second dropdown list of the EPM pane –, contains variables.
To change the variables for all the queries used in the worbooks opened, select Change Variables Change Variables for All Queries .

In both cases, the Set Variables dialog box opens and enables you to select the variables.

Setting Query Variables when Opening a File

You can specify if you want to set the variables or not when you open a file. If you do, select the Set Query Variables when Opening File from the Refresh tab of the Sheet Options. The Set Variables dialog box is automatically displayed when you open a file with queries that contain variables.

Variable History

When setting a value for a variable, the list of the last five values chosen (entered and validated using the OK button in the Set Variables dialog box) is proposed by default.

Only the values of the following types of variables are proposed by default for selection: characteristic value, hierarchy node, hierarchy, formula and text.

Note keep in mind that no control is performed on these values, whether they are valid variable values or not.

Note the variable history is stored in the file EPMXLClientVariableHistory.xml located in \Users\[ID]\AppData\Local\EPMOfficeClient.

Note the variable history also applies to planning functions and planning sequences.