KB484339: SAP HANA static list input parameter support in MicroStrategy

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description 

In a SAP HANA calculation view (CV), there is a type of input parameter (IP) called static list. This type allows you to choose an answer for the IP from a pre-defined list of entries – a static list. Strategy allows you to integrate with this type of IP via attribute element list prompt, where an attribute for a prompt refers to a SAP HANA column that contains the values of static list.
Learn how to support a static list IP in Strategy by going through the following topics:

1. Attribute element list prompt configuration 
2. Static list input parameter in cubes
3. Summary

1. Attribute element list configuration 

To support static list IP in Strategy, following the workflow:

1. Create CV with static list IP.
2. Add CV and table (BIMC_VARIABLE_VALUE) containing static list data into the project in the Warehouse Catalog.
3. Create attributes from NAME column of BIMC_VARIABLE_VALUE table and from CV columns of your choice.
4. Create attribute element list prompt.
5. Create and add a filter to attribute element list prompt.
6. Replace the default prompt with a prompt created in the previous steps in logical table object of calculation view.
7. Create a report or dossier.

Let's take a look at the steps in further details and with screenshots:

• Create a CV is static list IP.
  
• IP definition:
  

• CV behavior:
  Static List Popup
  

• Data returned: CC is a calculated column displaying answers provided for IP - 1, 2, or 3
  

• Add CV and table (BIMC_VARIABLE_VALUE) containing static list data into the project in the Warehouse Catalog.
  
• Add _SYS_BI schema to the Warehouse Catalog by modifying the read catalog SQL:
  

• Add _SYS_BI into the WHERE clauses:
  

• Click OK and Read Warehouse Catalog button.
  

• Views and tables from _SYS_BI schema appear in the pane to the left.
  
• Add BIMC_VARIABLE_VALUE and your calculation view to the right pane:
  

• Import prefix for both objects:
  

• Save and close.
• Create attributes from NAME column BIMC_VARIABLE_VALUE and from CV columns of your choice.
  
• Right-click Developer Canvas > New > Attribute
  

• Choose Name column from BIMC_VARIABLE_VALUE table:
  

• Optionally, to include Description of static list value, double-click OK and the New button to add a new attribute form:
  

• Choose Description column to add Attribute Description form:
  

• Double-click OK and save the attribute – here it is named, “Name.”
• Analogically create attributes for calculation view columns. In this example, C_NATIONKEY and CC columns were chosen for the two attributes used for reporting.
• Create a filter.
  
• Right-click in Developer Canvas > New > Filter.
  

• Use attribute created in the previous step - from BIMC_VARIABLE_VALUE table – here, it’s called NAME. You can choose a different way of filtering the values. Here, the filter qualifies on elements with the In list operator.
  

• Save the filter.
• Create and add a filter to attribute element list prompt.
  
• Right-click in Developer Canvas > New > Filter.
• Choose the attribute element list type.
  

• Click Next and choose attribute containing static list data - “Name” attribute.
  

• Click Next and choose Use a Filter to reduce the number of elements.
• Choose filter object created in the previous step:
  

• Specify additional settings according to your needs – here we’re enforcing the user to choose at least one value for a prompt:
  

• Click Next and finish and save the prompt.
• Replace the default prompt with a prompt created in the previous steps in the logical table object of a calculation view.
  
• Navigate to Schema objects > Tables in the Project tree (on the left pane in Developer):
  

• Double-click logical table object corresponding to a calculation view containing input -parameter and navigate to the Parameters tab:
  

• Notice that the default prompt name corresponds exactly to the input parameter name of calculation view. Right-click the entry and replace the default value prompt with the prompt created in the previous steps:
  

• Notice that Prompt Name and Prompt Type changed:
  

• Create a report or dossier.
  
• Create a report or dossier for reporting on calculation view containing static list input parameter. When executing the reporting object (report or dossier), a prompt appears:
  

• The exact formatting of the prompt depends on the definition of attribute. Here, it’s “ID:DESC”
  
• Report returns the correct data and contains correct SQL syntax:
  

• SQL Statement:
  

• Selecting several answers will result in the syntax passing strings regardless of the attributes definition (in the previous steps, we set it to Text).
  

2. Static list input parameters in cubes

In case you do not want to bother with configuration in Developer, it is possible to create an MTDI cube from calculation view containing static list type input parameter using the default value prompt. However, the following conditions apply:

• It is possible only with the SELECT TABLES option.
• Cube will use the default value prompt which does not show items from the static list and forces you to manually type in the value.

• The following prompt is shown in Strategy Web:
  

• Notice that it is possible to choose different value than what is specified in the static list, which in this example contains values 1, 2 and 3.
  Depending on the definition of the calculation view, the cube may or may not execute correctly:
  

• The SQL behind the visualization:
  

3. Summary

This article described how to achieve integration with SAP HANA’s calculation views static list type input parameter with the Strategy attribute element list prompt. If you want to use Freeform SQL reports for retrieving the static list data, the following SQL can be helpful:

SELECT NAME, DESCRIPTION FROM "_SYS_BI"."BIMC_VARIABLE_VALUE"
WHERE VARIABLE_NAME='YOUR_IP_NAME_HERE'

--AND CUBE_NAME = 'YOUR_CV_NAME_HERE'
--Use filter on CUBE_NAME if name of your input parameter is not unique.