Architecture: SAC using Live Connection to a HANA Database.
Many times in SAC we want to dynamically display a text based on some underlying values. In this scenario, we want to display the current Fiscal Year, Period and Quarter.
The underlying calculation view doesn’t have any optional or mandatory prompts, since the view itself was filtered to only return data for the current Fiscal Year, and the end users didn’t want any pop ups or filter bars to be displayed.
In this blog I’ll outline the simple steps in order to achieve that dynamic selection.
- Create a Stored Procedure to derive the required values (Fiscal Year, Period, Quarter, Week, etc…)
- In the calculation view, create an input parameter with parameter type Derived From Procedure / Scalar Function
- In the SAC story, ensure the parameter does not open every time the story is opened
1. Create Stored Procedure
In the web IDE, create a simple stored procedure that returns a distinct value for what is being required, such as the current Fiscal Year
2. In the calculation view, create an input parameter of Parameter Type Derived From Procedure/Scalar Function
Add the procedure created in step 1 in the Procedure/Scalar Function section, and ensure the check is selected for Input Enabled. This will force the prompt to appear.
Activate the view and do a data preview. You should see your input parameters with the values pre-populated per the logic in your stored procedure
3. Edit the Prompts in SAC. Open the story, and select Edit Prompts, and select the model that is linked to the calculation view which has the input parameters:
Notice how the values are being populated. Most importantly, ensure the check box for Automatically open prompt when story opens is deselected.
Hit set and you’re all set.
Now when creating a dynamic text, you have the option of selecting Model Variables (which are the input parameters from the calculation view)
And there you have it:
Hope you found this helpful.
All the best and happy story telling with SAC!