Skip to Content

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:

  1. Time Selection generation logic is keep outside of Design Studio application and can be easily reused;
  2. All Time Selections that user might need are generated (month, quarter, half, year and FTD);
  3. Times Selections can be easily customized (what time selection used in dashboard);
  4. 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

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: <struc> 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 !!
*<QUERY_HEAD>



*-------------------------------------------------------------------*
*   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 <struc>.
  move-corresponding <struc> to /BIC/PMONTH_TO               .
* !! the following comment MUST NOT BE CHANGED !!
*<QUERY_BODY>
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());
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply