Dashboard Time Selection Made Easy

By admin On Jan 15, 2021

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)