[[{“value”:”
Introduction
In SAP Analytics Cloud particularly in financial and treasury reporting often we have a most common requirement to display opening and closing balance based on selected period.
Mostly the requirement will be done by manually adjusting the reporting period or by creating multiple static filters , but in this blog we are going to tackle the requirement by using SAC scripting and input controls. By Filtering the periods before and up to the selected period , SAC can automatically calculate the opening and closing balance and display it in the dashboard.
This blog explains how SAC scripting is used to dynamically show the opening and closing balances based on selected period.
Business Scenario
The requirement is that the users should view treasury balances based on the selected period. The exact scenario described as following:
When a user chooses a period from dropdown
- It should display opening balance ie., it should consider all periods before the user selected period
- It should display closing balance ie., it should consider all periods up to the selected period and
- All other charts should display values only for that selected period.
Approach Used:
SAC scripting is used in OnInitialization event to retrieve selected period and apply necessary changes on the charts to display opening and closing balances based on selected period when the dashboard is loading.
Implementation
The code written inside the OnInitialization event ensures that filtering happens automatically in the backend.
Step 1 – Dropdown Selection and OnInitialization logic
When the user selects a period from the dropdown, the script retrieves the selected value and applies filters to the charts accordingly.The selected key gets stored in a variable and applied as a filter.
Scriptvariable_Getkey=Dropdown_1.getSelectedKey();
Chart_2.getDataSource().setDimensionFilter(“POSTING_YEAR”,Scriptvariable_Getkey);
The script then retrieves all members available in the POSTING_YEAR dimension and converts string to numbers to achieve proper comparison.
Scriptvariable_Memberinfo=Chart_2.getDataSource().getMembers(“POSTING_YEAR”);
Scriptvariable_Number=ConvertUtils.stringToNumber(Scriptvariable_Getkey);
In this step the script identifies all the periods before the selected period to and applies the filtered periods on Chart_2 to retrieve Opening balance.
for (var i = 0; i < Scriptvariable_Memberinfo.length; i++) {
var year = ConvertUtils.stringToNumber(Scriptvariable_Memberinfo[i].id);
if (year < Scriptvariable_Number) { Scriptvariable_Filter.push(Scriptvariable_Memberinfo[i].id);
}
}
Chart_2.getDataSource().setDimensionFilter(“POSTING_YEAR”,Scriptvariable_Filter);
To calculate closing balance in this step the script retrieves all the period up to the selected period and applies the filtered period on Chart_3.
for (var l = 0; l < Scriptvariable_Memberinfo.length; l++) {
var year1 = ConvertUtils.stringToNumber(Scriptvariable_Memberinfo[l].id);
if (year1 <= Scriptvariable_Number) { Scriptvariable_Filter1.push(Scriptvariable_Memberinfo[l].id);
}
}
Chart_3.getDataSource().setDimensionFilter(“POSTING_YEAR”,Scriptvariable_Filter1);
All other charts are filtered only for the selected period.
Chart_9.getDataSource().setDimensionFilter(“POSTING_YEAR”,Scriptvariable_Getkey);
Chart_10.getDataSource().setDimensionFilter(“POSTING_YEAR”,Scriptvariable_Getkey);
Step 2 – Default Behaviour on Page Load
Initially when the dashboard gets loaded in dropdown it shows the current year and month will be in the dropdown to enhance usability.The below script retrieves current month and year and formats accordingly.
Scriptvariable_CurrentYear = (new Date()).getFullYear().toString(); Scriptvariable_Currentmonth=(new Date()).getMonth().toString();
ScriptVariable_CurrentmonthNum=ConvertUtils.stringToInteger(Scriptvariable_Currentmonth); ScriptVariable_CurrentmonthNum1=ScriptVariable_CurrentmonthNum+1; ScriptVariable_Currentmonthstr=ConvertUtils.numberToString(ScriptVariable_CurrentmonthNum1);
ScriptVariable_Currentmonthstr1=(‘0’+ScriptVariable_Currentmonthstr).slice(-2);
Scriptvariable_Combine=Scriptvariable_CurrentYear+ScriptVariable_Currentmonthstr1;
The below script ensures that users always see the recent financial data immediately after opening the dashboard.
Dropdown_1.setSelectedKey(Scriptvariable_Combine);
Conclusion:
Dynamic reporting is necessary especially for a financial dashboard where the requirement is to automatically show opening and closing balances without any manual workload . SAC scripting approach simplifies the process and improves efficiency and enhances the user experience.
Output :
Dynamic Dropdown for Period-Based Filtering
Dynamic Balance Calculation Based on Selected Period
“}]]
#abap