[[{“value”:”
Introduction / Use Case:
In SAC, it is common to use BW variable as a story filter to enable dynamic data filtering.
This approach works well when your story is based on a single BW query or model.
However, Challenges arise when building stories that span multiple pages and use different BW models. In such cases:
- Each page may rely on a distinct BW query.
- Some models might not include BW variables and instead use input controls for filtering.
This mixed-model environment prevents the use of a unified story-level filter. To address this, I will demonstrate how to implement a page level filter using a global variable, dropdown, and script logic to dynamically update BW variable at runtime.
Key Notes:
- The BW query designed with a mandatory variable, which defaults to the current fiscal period (e.g., June 2025). ensuring the query always retrieves data for the latest period.
- This blog shows how to override and control that variable dynamically using a Page-level filter.
Solution Overview:
We will implement a dropdown filter that drives a global variable, which in turn updates BW query variable at the page level. This approach enables consistent filtering across different models.
Step-by-step Implementation:
- Create a Global variable
Name: VAR_PAYEAR_00
Type: String
This variable will serve as the filter carrier and will dynamically update based on the dropdown selection.
2. Add a dropdown :
Name : DRPD_PRYR
Data Source : Manual Input
Dropdown Properties:
Enable write-back at runtime
Assign to global variable VAR_PAYEAR_00
Enable input suggestions
3. Create a Dummy Table
Name: Table_1
Used to bind the dropdown to a data source for scripting purposes.
4. Configure Dropdown Onselect Script
var yyyy = DRPD_PRYR.getSelectedKey().slice(2,6);
var mm = DRPD_PRYR.getSelectedKey().slice(6,8);
//Fiscal year Format
VAR_PAYEAR_00 = yyyy+mm;
Table_1.getDataSource().setVariableValue(“VCALMONTH_MSCC001”, VAR_PAYEAR_00);
(BW query variable name : VCALMONTH_MSCC001)
5. Go to page oninitialization:
To set default current period and populate the dropdown with the past 24 periods.
// Get Current Date to derive current Fiscal Year / Period
var current_date = new Date(Date.now());
var date_millisec = current_date.setDate(current_date.getDate());
var new_date = new Date(date_millisec);
var date_new_format = new_date.toJSON().slice(0,10);
var mm = date_new_format.slice(5,7);
var mon = ConvertUtils.stringToNumber(mm); // Month as Number
var yyyy = date_new_format.slice(0,4);
var year =ConvertUtils.stringToInteger(date_new_format.slice(0,4)); // Year as Number
VAR_PAYEAR_00 = “K4″+yyyy+mm;
//Current Period
console.log(“Current Period is” + VAR_PAYEAR_00);
// Populate Previous 24 periods
// Use a loop to add previous periods to the dropdown and set the default to the most recent period
var DRPRD_count = 1;
for ( var ipryr = 1; ipryr < 24; ipryr++){
if ( mon === 1) //January
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “January ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = 12;
year = year – 1;
}
if( mon === 2) //February
{
VAR_PAYEAR_00 =”K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “February ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 3) //March
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “March ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 4) //April
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “April ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 5) //May
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “May ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 6) //June
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “June ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 7) //July
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “July ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 8) //August
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “August ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 9) //September
{
VAR_PAYEAR_00 = “K4″+year.toString()+”0″+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “September ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if ( mon === 10) //October
{
VAR_PAYEAR_00 = “K4″+year.toString()+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “October ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 11) //November
{
VAR_PAYEAR_00 = “K4″+year.toString()+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “November ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
if( mon === 12) //December
{
VAR_PAYEAR_00 = “K4″+year.toString()+mon.toString();
DRPD_PRYR.addItem(VAR_PAYEAR_00, ( “December ” + year.toString() ) );
console.log(“Derived Previous Period ” + VAR_PAYEAR_00);
if (( ipryr === 1 ) && (DRPRD_count === 1 ) )
{
console.log(“Closed Period(Previous Period) ” + VAR_PAYEAR_00);
DRPD_PRYR.setSelectedKey(VAR_PAYEAR_00);
DRPRD_count = DRPRD_count + 1;
}
mon = mon – 1;
}
}
Outcome:
- The dropdown is auto-populated with the previous 24 periods.
- The most recent period is selected by default.
- On selection, the corresponding BW variable (VCALMONTH_MSCC001) is dynamically updated.
- This enables the filter to work at the page level
Note:
To default the filter to the previous period (i.e., current period-1), Update
“if (( ipryr === 1 ) && (DRPRD_count === 1 ) )” to “if (( ipryr === 2 ) && (DRPRD_count === 1 ) )”
This will select the second most recent period (i.e., current period-1) as the default in the dropdown and apply it to BW variable.
Conclusion:
This solution provides a flexible alternative to using story-level filters in complex multi-model SAC stories. By leveraging global variables, dropdown input controls, and scripting, you can achieve dynamic page-level filtering across diverse BW data sources. This improves usability and enhances the dynamic nature of your SAC dashboards.
“}]]
#abap