Hi Folks,
Note: This blog is creating during earlier versions of SAP HANA.
If you are on SP09,You have an supported option of multiple values parameters.
Refer http://help.sap.com/hana/SAP_HANA_Modeling_Guide_for_SAP_HANA_Studio_en.pdf Page no: 106
In the previous blog SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter have shown on how to select multiple values for filtration using "Replace" function in procedure.
In that document, we went to procedure approach, since the user needs the dynamic output based on input conditions.
But if the output is static column list, you wouldn't want to use procedure, so in this blog will be explaining on how to achieve the same functionality of handling multiple values filter condition on a graphical calculation view using "Projection".
Problem Description:
Giving the flexibility of choosing the values he wishes to see in the report and then pushing the filtration logic to the lowest level possible.
Case 1: To select single value or multiple values for filter based on the input from the user
Case 2: To select "All" values if he doesn't want to apply any filter
**Note: If using "HTM5 Dashboards" , you can present the dropdown for filter as shown below:
This approach is very useful, if your dropdown has more values in the selection like more than 100 selections.
Now, Let us create an analytic view for our testing as shown below:
Now after adding the required fields to the "Output".
Let us create a "Input Parameter" to hold values of selection for "Region" from the user as shown below:
Now Let us create the filter using "Expression" as shown below:
in("REGION",'$$In_Region$$') or match ("REGION",'*$$In_Region$$*')
Data Preview:
Case 1 : ( Single or Multiple Values) :
(a) Single Value:
Sql Statement:
SELECT "REGION","EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", sum("SALARY") AS "SALARY" FROM "_SYS_BIC"."_SYS_BIC"."projects/CV_EMPLOYEE" ('PLACEHOLDER' = ('$$In_Region$$', 'AMER')) GROUP BY "EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", "REGION"
In the above screenshot we can see 1 value i.e AMER coming in output.
(b) Multiple Value:
You have to input the value like this example: AMER'',''APAC as shown below:
Sql Statement:
SELECT "REGION","EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", sum("SALARY") AS "SALARY" FROM "_SYS_BIC"."projects/CV_EMPLOYEE" ('PLACEHOLDER' = ('$$In_Region$$', 'AMER'',''APAC')) GROUP BY "EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", "REGION"
In the above screenshot we can see two values i.e AMER & APAC coming in output.
Case 2 : ( All Values -- No Filtering) :
We need to pass * as shown below :
Sql Statement:
SELECT "REGION","EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", sum("SALARY") AS "SALARY" FROM "_SYS_BIC"."projects/CV_EMPLOYEE" ('PLACEHOLDER' = ('$$In_Region$$', '*')) GROUP BY "EMP_NO", "EMPLOYEE_NAME", "EMPLOYEE_TYPE", "GENDER", "AGE", "REGION"
In the above screenshot we can see all the 3 regions coming in output.
**Note: This approach is useful when your reporting solution is HTML5 dashboards and you cannot use Variables (multiple values) for filtering.
Hoping that blog is helpful for you , do let me know your feedback on this.
Your's
Krishna Tangudu :smile:
Bluesky
