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.
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.
NoteThe 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)
Creating Variables (3)
More than one value can be chosen for a variable when you select the Multiple Entries checkbox.
NoteIn 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