Linked analysis based on widgets having different data sources with no common dimension in SAC

In SAP Analytics Cloud, stories often contain widgets such as charts and tables, which can connect to different models. Linked analysis helps to create dynamic interactions between said widgets and can be performed on widgets living within the same model or even across different models that contain at least one common dimension  

But what need to be done when Widgets are based on different data sources with no common dimension.

 In the blog, we are going to discuss to have Linked analysis based on widgets having different data sources with no common dimension in SAC Application using Scripting.  @Vegat

 Let’s see how the application should behave on Page Refresh

• Dropdown 1 should have Company Code based on what user is authorized to and Dropdown 1 need to show default Company Code
• Dropdown 2 should show Division corresponding to Company Code Selected and should be dynamically change with Company Code selection
• Dropdown 3 should have Year/month with default value as”01.2025”
• Dashboard should be refreshed based on the Default Values selected in Dropdowns

 

Snapshot of Dashboard on Page Refresh

 

We will discuss with few more exciting dynamic functionalities in next section.

Models/Popup Required/Created

We have utilized multiple models in the document as below

• Dimension Model
• Fact Data Model
• Company Model

Table and Model naming in SAC Application

Table_1 corresponds to Dimension Model

Table_2 corresponds to Fact Data Model

Table_3 corresponds to Company Mapping Model

Achieving Requirement on data Refresh Page

Below logic need to written on “onInitilization” Function of the application page

Dropdown 1 should have Company Code based on what user is authorized to and Dropdown 1 need to show default Company Code

var resultSetCompany =  Table_3.getDataSource().getResultSet();

 

for(var c=0; c<resultSetCompany.length; c++)

              {

 

                                           if(  resultSetCompany[c][“Company_Code”].id !==”#” )

                             {

                                           if(c===0){

                             var default_value_Company=resultSetCompany[c][“Company_Code”].id;

                                                          }

 

                                       Dropdown_1.addItem(resultSetCompany[c][“Company_Code”].id);

                             }

              }

 

 

//set default value for Company code

Dropdown_1.setSelectedKey(default_value_Company);

 

Dropdown 2 should show Division corresponding to Company Code Selected and should be dynamically change with Company Code selection

//Loading  Division to dropdown_2

var  resultSetDivision = Table_3.getDataSource().getResultSet();

// Get Company Code selected by user

var company = default_value_Company;

var division_value_bool=true;

//Remove all items in the Division dropdown

Dropdown_2.removeAllItems();

// Fill only Division relevant to selected Company

 

for (  d = 0; i< resultSetDivision.length; d++ ){

 

              if ( resultSetDivision[d][“Company_Code”].id === company ){

                             if(division_value_bool){

                                           var default_division = resultSetDivision[d][“Division”].id;

                                           division_value_bool = false;

                             }

 

                                           Dropdown_2.addItem(resultSetDivision[d][“Division”].id);

              }

}

//set default value for Division

Dropdown_2.setSelectedKey(default_division);

 

Dropdown 3 should have Year/month with default value as”01.2024”

Below is the sample Logic but you can change the logic based on the format, you want to see period in Dropdown

var startDateDropdown = new Date(2023, 12); //Period from which the dropdown should start showing the period

var endDateDropdown = new Date();

 

while(startDateDropdown <= endDateDropdown){

  

   var month = startDateDropdown.getMonth();

   var year = startDateDropdown.getFullYear();

   if ( month < 12 ) {

               month =  month + 1;

   } else {

                year = year + 1;

                month = 1;

                startDateDropdown.setFullYear(year);

   }

startDateDropdown.setMonth(month);

 Dropdown_3.addItem(month.concat(“.”).concat(year.toString()));        

}

 

//Set default value for Period

Dropdown_3.setSelectedKey(‘01.2024’);

 

Dashboard should be refreshed based on the Default Values selected in Dropdowns

//Applying Filter on Fact table based on dropdown values selected

var Company_filter = Dropdown_1.getSelectedKey();

var Division_filter = Dropdown_2.getSelectedKey();

var Period_filter = Dropdown_3.getSelectedKey();

 

Appling filter of selected values from Dropdown to Fact Table

Table_2.getDataSource().setDimensionFilter(“CompanyCode”,Company_filter);

Table_2.getDataSource().setDimensionFilter(“Period”,Division_filter);

Table_2.getDataSource().setDimensionFilter(“Period”,Period_filter);