Using BW variable as page Filter in SAP Analytics Cloud (SAC) for Multi-Model Stories

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.

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++){

}
mon = mon – 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 levelNote: 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. Read More Technology Blog Posts by Members articles

#SAP

#SAPTechnologyblog