You define a variable in a calculation view when you want to pass dynamic values to filter attribute columns.
When a calculation view that contains a variable is called by a front-end reporting tool, the calculation view passes a request to the front-end tool to provide a value (usually using a user prompt) to complete a WHERE clause. The WHERE clause is added to the query that runs on top of the calculation view. For example, if the user was prompted to choose a country and the user chose 'JP', the SQL that runs on the calculation view looks like this:
SELECT <columns> FROM <calculation_view_name> WHERE (("COUNTRY" = 'JP')) GROUP BY <group_by columns>;
Creating Variables
You define variables in the Semantics node of a Calculation View, in the Parameters tab.
A variable definition includes:
- View/Table for Value Help and Attribute: These settings define which view/table and which attribute from this view/table is used as a reference to provide a list of values at runtime
- Selection Type: Whether selections should be based on intervals, ranges or single values.
- Multiple Entries: Whether multiple occurrences of the selection type are allowed.
- You can also define whether specifying the variable at runtime is Mandatory and/or if it should have a Default Value.
- You define which attribute(s) of the current view the variable should be applied to.
The View/Table Value Help setting is used to define which table/view SAP HANA will fetch the data to show in the prompt. The Reference Column defines which (unique) column in this table/view will provide the possible values for the variable.
By default, the View/Table Value Help setting refers to the current calculation view. This means that the values of the specified attribute column, for example CUSTOMER (customer ID), will be scanned in order to propose the various values in the prompt.
A different approach is to define a dedicated source table/view for the variable. For example, you could define the master data table CUSTOMERS as the Value Help source for the variable, and - of course - choose the column from this master data table that matches the column of your calculation view you want to filter on: in our example, the Customer ID.
When the Value Help table and one column are chosen, you assign the APPLY FILTER attribute. That is, you identify which attribute column will be filtered at runtime, based on the variable values chosen by the user.
Note
When the Value Help source table/view is the calculation view itself, the reference column you define is automatically assigned to the APPLY FILTER section as a filter attribute. However, you can define another attribute if needed, provided that it is consistent with the reference column.The behavior of variables at runtime depends on whether an entry is required for the variable or not. These scenarios can be defined as follows:
If a variable is defined as mandatory, the user needs to provide the values, ranges, or intervals at runtime.
For non-mandatory variables, if nothing is specified at runtime, all the data for the corresponding attributes is returned by the view without filtering.
Creating Variables (2)
In the semantics of a view, you can see, and also define, which variable is assigned to which attribute.
Note that one variable can be assigned to multiple attributes.
Creating Variables (3)
When displaying the data of a view that contains a variable or input parameter, the Value Help Dialog helps you or the reporting user to find the possible values.
More than one value can be chosen for a variable when you select the Multiple Entries checkbox.
Note
In the Data Preview, From and To are displayed in the Variable Values dialog even when a variable has not been defined as range.
Value Help for Variables
When a dialog box appears to the user they must make a selection. However, rather than an empty field appearing and the user having to guess valid values or figure out the format for a value (for example, is the country code UK or GB?), we can have a dialog box populated with the run-time values available. To do this, you make a selection in the setting View/Table for value help. The default entry is the calculation view where the variable is being created. This means that you present all possible values from the column that is assigned to the Attribute setting in the variable definition.
While this might seem like a great idea, remember that the list may be huge and would be difficult for the user to navigate. Imagine presenting a list that includes every employee in a very large organization? If the list should be restricted to offer limited values, such as employees in your department, then you should reference an external calculation view or a table that exposes a restricted list. It is also good practice, from a performance perspective, to refer to a restricted list from an external calculation view or table.
Value Help Based on Hierarchies
When creating a variable on an attribute that is associated with one or several hierarchies, you can specify one of the hierarchies in the variable definition. With this option, the user can navigate the hierarchy, rather than a flat list, to select the values in the value help. This makes navigation much easier when there are a lot of values; imagine being able to first select your country, then your department, before the list of employees appears? You can use either parent-child or level hierarchies.
The following are some of the basic rules that apply when implementing value help based on hierarchies:
If you refer to a parent-child hierarchy, the variable attribute column must be defined as the parent attribute and not the child.
If you refer to a level hierarchy, the variable attribute column must be defined at the leaf level; that is, the bottom level.
Note
Variables are not relevant for measures.