February 9, 2018 – Page 10

Dashboard time selection is a common requirement. Creation of time selections using BIAL Script is a challenging task. BIAL Script is a small subset of JavaScript which makes scripting complicated. That is why it make sense to leave heavy lifting to underlying BW system.

In my blog I will explain how to derive dashboard time selections from Direct Access InfoObject Master Data (rolling 2 years of time selections are dynamically generated at the time of dashboard execution). This approach has a number of advantages compare to generating time selections in Design Studio application itself:

* Time Selection generation logic is keep outside of Design Studio application and can be easily reused;
* All Time Selections that user might need are generated (month, quarter, half, year and FTD);
* Times Selections can be easily customized (what time selection used in dashboard);
* Time Selection ranges are generated in external format e.g. suitable for Design Studio BW variables;

Below is an example of Direct Access InfoObject Master Data that will be used to feed Design Studio Dashboard Time Selection.

Direct Access InfoObject Master Data is mapped into Design Studio dashboard Time Time drop down looks like this:

If you scroll down the list you will see the rest of selections

Lets pick, for example, H2 2017 and see it in action.

Here is a list of steps needed to implement dashboard time selections based on Direct Access InfoObject Master Data:

* Create MONTH_FR InfoObject (selection range upper limit);
* Create MONTH_TX InfoObject (selection range description);
* Create MONTH_TO InfoObject (selection range lower limit, compounded by MONTH_FR and MONTH_TX InfoObjects);
* Create Z_MONTH_TO_ATTR InfoSet (this where ABAP code generates 2 rolling years of time selections);
* Create MONTH_TO_ATTR Master Data DataSource (based on Z_MONTH_TO_ATTR InfoSet);
* Create Transfer Rules from MONTH_TO_ATTR DataSource to MONTH_TO InfoObject Master Data;
* Create DTP from MONTH_TO_ATTR DataSource to MONTH_TO InfoObject Master Data;
* Add MONTH_TO InfoObject as DataSource to Design Studio Dashboard;
* Read MONTH_TO Direct Access InfoObject Master Data and populate Time Selection control (DROPDOWN_MONTH drop down in my example).

Create MONTH_FR InfoObject

Note: MONTH_FR InfoObject has PERI6 conversion routine to provide external values for BW variables in Design Studio application

Create MONTH_TX InfoObject

Note: MONTH_TX InfoObject is defined as lowe case allowed in order to display nicely looking time selection ranges name

Create MONTH_TO InfoObject

Note: MONTH_TO InfoObject has PERI6 conversion routine to provide external values for BW variables in Design Studio application

Note: MONTH_TO InfoObject has master data and defined as InfoProvider in order to be able to consume it in Design Studio as DataSource

Note: MONTH_TO InfoObject is compounded by MONTH_FR and MONTH_TX InfoObjects in order to Design Studio application be able to read this information as a part of master data

Create Z_MONTH_TO_ATTR InfoSet

Note: Z_MONTH_TO_ATTR InfoSet is defined as Data retrieval by integrated program and /BIC/PMONTH_TO Data Structure (MONTH_TO InfoObject master data table)

Define Integrated Program as follows: REPORT RSAQDVP_TEMPLATE . * *———————————————————————* * declarations * (insert your declarations in this section) *———————————————————————* data: /BIC/PMONTH_TO type /BIC/PMONTH_TO , it_data type standard table of /BIC/PMONTH_TO . field-symbols: type /BIC/PMONTH_TO . *——————————————————————-* * month *——————————————————————-* DEFINE month. w_month_from = &1. w_month_to = &1. DO 12 TIMES. CASE w_month_from+4(2). WHEN ‘01’. w_text = |Jan { w_month_from+0(4) }|. WHEN ’02’. w_text = |Feb { w_month_from+0(4) }|. WHEN ’03’. w_text = |Mar { w_month_from+0(4) }|. WHEN ’04’. w_text = |Apr { w_month_from+0(4) }|. WHEN ’05’. w_text = |May { w_month_from+0(4) }|. WHEN ’06’. w_text = |Jun { w_month_from+0(4) }|. WHEN ’07’. w_text = |Jul { w_month_from+0(4) }|. WHEN ’08’. w_text = |Aug { w_month_from+0(4) }|. WHEN ’09’. w_text = |Sep { w_month_from+0(4) }|. WHEN ’10’. w_text = |Oct { w_month_from+0(4) }|. WHEN ’11’. w_text = |Nov { w_month_from+0(4) }|. WHEN ’12’. w_text = |Dec { w_month_from+0(4) }|. ENDCASE. APPEND VALUE #( /bic/month_to = w_month_to /bic/month_fr = w_month_from /bic/month_tx = w_text ) TO it_data. CASE w_month_to+4(2). WHEN ’01’. w_month_to = w_month_from = |{ w_month_to+0(4) – 1 }12|. WHEN OTHERS. w_month_to = w_month_from = |{ w_month_to+0(4) }{ conv num2( w_month_to+4(2) – 1 ) }|. ENDCASE. ENDDO. END-OF-DEFINITION. *——————————————————————-* * quarter *——————————————————————-* DEFINE quarter. CASE &1+4(2). WHEN ’01’ OR ’02’ OR ’03’. “Q1 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }01| /bic/month_to = |{ &1+0(4) – 1 }03| /bic/month_tx = |Q1 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }04| /bic/month_to = |{ &1+0(4) – 1 }06| /bic/month_tx = |Q2 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }07| /bic/month_to = |{ &1+0(4) – 1 }09| /bic/month_tx = |Q3 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }10| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |Q4 { &1+0(4) – 1 }| ) TO it_data. WHEN ’04’ OR ’05’ OR ’06’. "Q2 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }04| /bic/month_to = |{ &1+0(4) – 1 }06| /bic/month_tx = |Q2 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }07| /bic/month_to = |{ &1+0(4) – 1 }09| /bic/month_tx = |Q3 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }10| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |Q4 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }01| /bic/month_to = |{ &1+0(4) }03| /bic/month_tx = |Q1 { &1+0(4) }| ) TO it_data. WHEN ’07’ OR ’08’ OR ’09’. "Q3 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }07| /bic/month_to = |{ &1+0(4) – 1 }09| /bic/month_tx = |Q3 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }10| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |Q4 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }01| /bic/month_to = |{ &1+0(4) }03| /bic/month_tx = |Q1 { &1+0(4) }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }04| /bic/month_to = |{ &1+0(4) }06| /bic/month_tx = |Q2 { &1+0(4) }| ) TO it_data. WHEN ’10’ OR ’11’ OR ’12’. "Q4 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }10| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |Q4 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }01| /bic/month_to = |{ &1+0(4) }03| /bic/month_tx = |Q1 { &1+0(4) }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }04| /bic/month_to = |{ &1+0(4) }06| /bic/month_tx = |Q2 { &1+0(4) }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }07| /bic/month_to = |{ &1+0(4) }09| /bic/month_tx = |Q3 { &1+0(4) }| ) TO it_data. ENDCASE. END-OF-DEFINITION. *——————————————————————-* * half *——————————————————————-* DEFINE half. CASE &1+4(2). WHEN ’01’ OR ’02’ OR ’03’ OR ’04’ OR ’05’ OR ’06’. "H1 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }01| /bic/month_to = |{ &1+0(4) – 1 }06| /bic/month_tx = |H1 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }07| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |H2 { &1+0(4) – 1 }| ) TO it_data. WHEN ’07’ OR ’08’ OR ’09’ OR ’10’ OR ’11’ OR ’12’. "H2 APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }07| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |H2 { &1+0(4) – 1 }| ) TO it_data. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) }01| /bic/month_to = |{ &1+0(4) }06| /bic/month_tx = |H1 { &1+0(4) }| ) TO it_data. ENDCASE. END-OF-DEFINITION. *——————————————————————-* * ftd *——————————————————————-* DEFINE ftd. CASE &1+4(2). WHEN ’01’ OR ’02’ OR ’03’. &1_from = |{ &1+0(4) – 1 }04|. &1_to = &1. w_text = |FTD { &1+0(4) – 1 }|. WHEN OTHERS. &1_from = |{ &1+0(4) }04|. &1_to = &1. w_text = |FTD { &1+0(4) }|. ENDCASE. APPEND VALUE #( /bic/month_to = &1_to /bic/month_fr = &1_from /bic/month_tx = w_text ) TO it_data. END-OF-DEFINITION. *——————————————————————-* * year *——————————————————————-* DEFINE year. APPEND VALUE #( /bic/month_fr = |{ &1+0(4) – 1 }01| /bic/month_to = |{ &1+0(4) – 1 }12| /bic/month_tx = |{ &1+0(4) – 1 }| ) TO it_data. END-OF-DEFINITION. *——————————————————————-* * selection screen statements *——————————————————————-* * (define your selection-screen here) * !! the following comment MUST NOT BE CHANGED !! * *——————————————————————-* * read data into IT_DATA *——————————————————————-* * (select your data here into internal table IT_DATA) DATA: w_month_from TYPE num6, w_month_to TYPE num6. DATA: w_text(20) TYPE c. DATA(w_month) = sy-datum+0(6). * Month month w_month. * Quarter quarter w_month. * Half half w_month. * FTD ftd w_month. * Year year w_month. w_month+0(4) = w_month+0(4) – 1. * Month Yr-1 month w_month. * Quarter Yr-1 quarter w_month. * Half Yr-1 half w_month. * FTD Yr-1 ftd w_month. * Year Yr-1 year w_month. *————————————————————* * output of the data * (this section can be left unchanged) *————————————————————* loop at it_data assigning . move-corresponding to /BIC/PMONTH_TO . * !! the following comment MUST NOT BE CHANGED !! * endloop.

Program Logic: month, quarter, half, ftd, year macros are defined to generate monthly, quartely, half year, ftd and yearly selection respectively based on current month. The macros are called twice first to generate selections for current year and the for previous year (e.g. 2 rolling years of time selections)

Create MONTH_TO_ATTR Master Data DataSource

Create Transfer Rules from MONTH_TO_ATTR DataSource to MONTH_TO InfoObject Master Data

Create DTP from MONTH_TO_ATTR DataSource to MONTH_TO InfoObject Master Data

Add MONTH_TO InfoObject as DataSource to Design Studio Dashboard

Read MONTH_TO Direct Access InfoObject Master Data and populate Time Selection control // Month var value = ”“; var selected_month = ”“; //Year DS_MONTH.getMembers("MONTH_TO”,999).forEach(function(element, index) { if (element.internalKey.substring(4,8) == ’ ’) { value = element.externalKey.substring(5,12) + “ – ” + element.externalKey.substring(13); DROPDOWN_MONTH.addItem(value, element.internalKey.substring(0,4),0); } }); ////FTD is not needed in this example //DS_MONTH.getMembers(“MONTH_TO”,999).forEach(function(element, index) { // if (element.internalKey.substring(0,3) == ‘FTD’) { // value = element.externalKey.substring(9,16) + “ – ” + element.externalKey.substring(17); // DROPDOWN_MONTH.addItem(value, element.internalKey.substring(0,8),0); // } //}); //Half DS_MONTH.getMembers(“MONTH_TO”,999).forEach(function(element, index) { if (element.internalKey.substring(0,1) == ‘H’) { value = element.externalKey.substring(8,15) + “ – ” + element.externalKey.substring(16); DROPDOWN_MONTH.addItem(value, element.internalKey.substring(0,7),0); } }); //Quarter DS_MONTH.getMembers(“MONTH_TO”,999).forEach(function(element, index) { if (element.internalKey.substring(0,1) == ‘Q’) { value = element.externalKey.substring(8,15) + “ – ” + element.externalKey.substring(16); DROPDOWN_MONTH.addItem(value, element.internalKey.substring(0,7),0); } }); //Month DS_MONTH.getMembers(“MONTH_TO”,999).forEach(function(element, index) { if (element.internalKey.substring(0,3) != ‘FTD’ && element.internalKey.substring(3,4) == ’ ’ ) { value = element.externalKey.substring(9,16) + “ – ” + element.externalKey.substring(17); DROPDOWN_MONTH.addItem(value, element.internalKey.substring(0,8),0); selected_month = value; } }); DROPDOWN_MONTH.setSelectedValue(selected_month); DS_COUNTRY.setVariableValueExt(“ZCALMONO”, DROPDOWN_MONTH.getSelectedValue()); http://bit.ly/2Bj0fjY #SAP #SAPCloud #AI

Day: February 9, 2018

Use Winter Olympics to Light Up Employee Engagement

Dashboard Time Selection Made Easy

AR & AP Regrouping in SAP (S/4 HANA Finance)

Subscribe To Newsletter