SAC hierarchy is not working with an input parameter

Question

I’m seeking your support with SAC hierarchy. It seems not working well when defining an input parameter in calculation view (CV) in hana studio.

Once added input parameter for years to enable users to select a specific year once they open the dashboard.

All hierarchies became not working & showing error as in screenshot. SAC hierarchy error pic

Errors Server Error Caught exception : exception 52731: Hierarchy cache error for hierarchy SBU_H : Hierarchy create error for "_SYS_BIC"."FANAR_REPORTING_PROJECT.SCM.SAC_DATA_MODELS/CV_SCM_DELIVERY_AND_COST_MODEL/SBU_H/hier/SBU_H" error: SQL: column store error: search table error: [34023] Instantiation of calculation model failed;exception 306106: Undefined variable: $$year_scope$$. Variable is marked as required but not set in the query

I use YEAR_SCOPE input parameter in where statement to reduce the number of fetched records. As you can see bellow this is the created YEAR_SCOPE. SQL where statement with YEAR_SCOPE pic

This is how YEAR_SCOPE created created YEAR_SCOPE in CV hana studio

Here from frontend side (SAC) by default 2019 is the selected value & user can change it as well SAC frontend with year selection

At the end I’m wondering if there is any way to solve this error...

Thanks in advance!

Answer 1

In order to use input parameters with HANA information views it is required to use the WITH PARAMETERS() syntax.

When the view parameters have been defined as variables then the reporting UI will simply create WHERE conditions with the selection parameters chosen by the user.
Based on the screenshots this is what is happening here.

For SAC to instead use the WITH PARAMETERS syntax the view parameters have to be defined as input parameters and the parameters have to be mapped in SAC.