Dashboard Selection Made Easy – Part 1: Time Selec...

uladzislau_pralat · ‎2018 Jan 13

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 (Fiscal Years, Fiscal to Last Month, Fiscal Quarter and Month selections for last two fiscal years). 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 (Fiscal Year, Fiscal to Last Month, Fiscal Quarter and Month);

Times Selections can be easily customized on front-end side (Dashboard can use a subset of selections provided by back-end);

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.

Note: selections might not come in the right order, but it will be corrected in dashboard using sequence field

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

Note: if you scroll up and down the list you will see the rest of selections (Fiscal Year and Months)

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

Create CPL_MN_FR InfoObject (selection range low limit);

Create CPL_MN_TO InfoObject (selection range high limit);

Create CPL_MN_SQ InfoObject (selection sequence for sorting in dashboard);

Create CPL_MN_TX InfoObject (compounded by CPL_MN_FR, CPL_MN_TO and CPL_MN_SQ);

Create Z_CPL_MN_TX InfoSet (this where ABAP code that generates selections for 2 rolling fiscal years);

Create CPL_MN_TX Master Data DataSource (based on CPL_MN_TX InfoSet);

Create Transfer Rules from MONTH_TO_ATTR DataSource to MONTH_TO InfoObject Master Data;

Create DTP from CPL_MN_TX DataSource to CPL_MN_TX InfoObject Master Data;

Add CPL_MN_TX InfoObject as DataSource to Design Studio Dashboard;

Read CPL_MN_TX Direct Access InfoObject Master Data and populate Time Selection control (DROPDOWN_MONTH drop down in my example).

Create CPL_MN_FR InfoObject

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

Create CPL_MN_TO InfoObject

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

Create CPL_MN_SQ InfoObject

Create CPL_MN_TX InfoObject

Note: CPL_MN_TX InfoObject is case sensitive to provide nice looking Design Studio Time Selections

Note: CPL_MN_TX InfoObject defined as InfoProvider with Direct Access Master Data

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

Create Z_CPL_MN_TX InfoSet

Note: Z_CPL_MN_TX InfoSet is defined as with Data Retrieval by Integrated Program and based on /BIC/PCPL_MN_TX Data Structure (CPL_MN_TX InfoObject master data table)

Below is Data Retrieval by Integrated Program:

REPORT  RSAQDVP_TEMPLATE .

*

*---------------------------------------------------------------------*

*   declarations

*   (insert your declarations in this section)

*---------------------------------------------------------------------*

data:

  /BIC/PCPL_MN_TX                type /BIC/PCPL_MN_TX               ,

  it_data type standard table of /BIC/PCPL_MN_TX               .





field-symbols: <struc> type /BIC/PCPL_MN_TX               .



*-------------------------------------------------------------------*

*   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_date) =

    COND dats( WHEN sy-sysid = 'DEV' OR sy-sysid = 'TST'

               THEN ( CONV dats( |{ sy-datum+0(4) - 2 }{ sy-datum+4(2) }01| ) - 1 )

               WHEN sy-sysid = 'PRD' THEN CONV dats( |{ sy-datum+0(6) }01| ) - 1 ).

* Fiscal Year

  APPEND VALUE #(

    /bic/cpl_mn_fr = |{ COND numc4( WHEN w_date+4(2) BETWEEN '01' AND '03'

                                    THEN w_date+0(4) - 2

                                    ELSE w_date+0(4) - 1 ) }04|

    /bic/cpl_mn_to = |{ COND numc4( WHEN w_date+4(2) BETWEEN '01' AND '03'

                                    THEN w_date+0(4) - 1

                                    ELSE w_date+0(4) ) }03|

    /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) ) "00

    /bic/cpl_mn_tx = |FY {  COND numc4( WHEN w_date+4(2) BETWEEN '01' AND '03'

                                        THEN w_date+0(4) - 2

                                        ELSE w_date+0(4) - 1 ) } | ) TO it_data.

  TRY.

    APPEND VALUE #(

      /bic/cpl_mn_fr = |{ it_data[ 1 ]-/bic/cpl_mn_fr+0(4) - 1 }{ it_data[ 1 ]-/bic/cpl_mn_fr+4(2) }|

      /bic/cpl_mn_to = |{ it_data[ 1 ]-/bic/cpl_mn_to+0(4) - 1 }{ it_data[ 1 ]-/bic/cpl_mn_to+4(2) }|

      /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) ) "01

      /bic/cpl_mn_tx = |FY { it_data[ 1 ]-/bic/cpl_mn_tx+3(4) - 1 } | ) TO it_data.

  CATCH cx_sy_itab_line_not_found.

  ENDTRY.

* FTLM

  APPEND VALUE #(

    /bic/cpl_mn_fr = |{ COND numc4( WHEN w_date+4(2) BETWEEN '01' AND '03'

                                    THEN w_date+0(4) - 1

                                    ELSE w_date+0(4) ) }04|

    /bic/cpl_mn_to = |{ w_date+0(6) }|

    /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) ) "02

    /bic/cpl_mn_tx = |FTLM {  COND numc4( WHEN w_date+4(2) BETWEEN '01' AND '03'

                                          THEN w_date+0(4) - 1

                                          ELSE w_date+0(4) ) } | ) TO it_data.

  TRY.

    APPEND VALUE #(

      /bic/cpl_mn_fr = |{ it_data[ 3 ]-/bic/cpl_mn_fr+0(4) - 1 }{ it_data[ 3 ]-/bic/cpl_mn_fr+4(2) }|

      /bic/cpl_mn_to = |{ it_data[ 3 ]-/bic/cpl_mn_to+0(4) - 1 }{ it_data[ 3 ]-/bic/cpl_mn_to+4(2) }|

      /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) ) "03

      /bic/cpl_mn_tx = |FTLM { it_data[ 3 ]-/bic/cpl_mn_tx+5(4) - 1 } | ) TO it_data.

  CATCH cx_sy_itab_line_not_found.

  ENDTRY.

* Quarter

  DATA(w_month_fr) = CONV /bi0/oicalmonth( it_data[ 2 ]-/bic/cpl_mn_fr ).

  DATA(w_month_to) = COND /bi0/oicalmonth( WHEN w_date+4(2) BETWEEN '01' AND '02'

                                           THEN |{ w_date+0(4) - 1 }12|

                                           WHEN w_date+4(2) BETWEEN '04' AND '05'

                                           THEN |{ w_date+0(4) }03|

                                           WHEN w_date+4(2) BETWEEN '07' AND '08'

                                           THEN |{ w_date+0(4) }06|

                                           WHEN w_date+4(2) BETWEEN '10' AND '11'

                                           THEN |{ w_date+0(4) }09|

                                           ELSE w_date+4(2) ).

  TRY.

    WHILE w_month_fr < w_month_to.

    APPEND VALUE #(

      /bic/cpl_mn_fr = CONV /bi0/oicalmonth( |{ w_month_to+0(4) }{ CONV numc2( w_month_to+4(2) - 2 ) }| )

      /bic/cpl_mn_to = w_month_to

      /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) )

      /bic/cpl_mn_tx = SWITCH #( w_month_to+4(2)

                         WHEN '03'  THEN |FQ4 { w_month_to+0(4) - 1 }|

                         WHEN '12'  THEN |FQ3 { w_month_to+0(4) }|

                         WHEN '09'  THEN |FQ2 { w_month_to+0(4) }|

                         WHEN '06'  THEN |FQ1 { w_month_to+0(4) }| ) ) TO it_data.

      w_month_to = COND #( WHEN w_month_to+4(2) = '03'

                           THEN |{ w_month_to+0(4) - 1 }12|

                           ELSE |{ w_month_to+0(4) }{ CONV num2( w_month_to+4(2) - 3 ) }| ).

    ENDWHILE.

  CATCH cx_sy_itab_line_not_found.

  ENDTRY.

* Month

  w_month_to = CONV /bi0/oicalmonth( w_date+0(6) ).

  TRY.

    WHILE w_month_fr =< w_month_to.

    APPEND VALUE #(

      /bic/cpl_mn_fr = w_month_to

      /bic/cpl_mn_to = w_month_to

      /bic/cpl_mn_sq = CONV numc2( LINES( it_data ) )

      /bic/cpl_mn_tx = |{ SWITCH #( w_month_to+4(2)

                            WHEN '01' THEN 'Jan'

                            WHEN '02' THEN 'Feb'

                            WHEN '03' THEN 'Mar'

                            WHEN '04' THEN 'Apr'

                            WHEN '05' THEN 'May'

                            WHEN '06' THEN 'Jun'

                            WHEN '07' THEN 'Jul'

                            WHEN '08' THEN 'Aug'

                            WHEN '09' THEN 'Sep'

                            WHEN '10' THEN 'Oct'

                            WHEN '11' THEN 'Nov'

                            WHEN '12' THEN 'Dec' ) } { w_month_to+0(4) }| ) TO it_data.

      w_month_to = COND #( WHEN w_month_to+4(2) = '01'

                           THEN |{ w_month_to+0(4) - 1 }12|

                           ELSE |{ w_month_to+0(4) }{ CONV num2( w_month_to+4(2) - 1 ) }| ).

    ENDWHILE.

  CATCH cx_sy_itab_line_not_found.

  ENDTRY.



*------------------------------------------------------------*

*   output of the data

*   (this section can be left unchanged)

*------------------------------------------------------------*

loop at it_data assigning <struc>.

  move-corresponding <struc> to /BIC/PCPL_MN_TX               .

* !! the following comment MUST NOT BE CHANGED !!

*<QUERY_BODY>

endloop.

Create CPL_MN_TX Master Data DataSource

Create Transfer Rules from CPL_MN_TX DataSource to CPL_MN_TX InfoObject Master Data

Create DTP from CPL_MN_TX DataSource to CPL_MN_TX InfoObject Master Data

Add CPL_MN_TX InfoObject as DataSource to Design Studio Dashboard

Read MONTH_TO Direct Access InfoObject Master Data and populate Time Selection Drop Down

//Month

var value = "";

var value_selected = "";

DS_MONTH.getMembers("CPL_MN_TX",999).forEach(function(element1, index1) {

	DS_MONTH.getMembers("CPL_MN_TX",999).forEach(function(element2, index2) {

		

		if ( index1 == Convert.stringToInt(element2.externalKey.substring(16,18)) ) {

			if ( index1 == 0 || index1 == 1 ) {

				value = element2.externalKey.substring(0,7) + " - " + element2.externalKey.substring(8,15);    					

				DROPDOWN_MONTH.addItem(value, element2.internalKey.substring(14), index1 );	    					

			} else {

				if ( index1 == 2 || index1 == 3 ) {

					value = Convert.replaceAll(Convert.replaceAll(Convert.replaceAll(element2.externalKey.substring(0,7), "-", ""),".",""),"/","") + " - " + element2.externalKey.substring(8,15);    										

					DROPDOWN_MONTH.addItem(value, element2.internalKey.substring(14), index1 );	    											

					if ( index1 == 2 ) {

						value_selected = value;

					}

				} else {

					value = element2.externalKey.substring(0,7) + " - " + Convert.replaceAll(Convert.replaceAll(Convert.replaceAll(element2.externalKey.substring(8,15), "-", ""),".",""),"/","");    					

					DROPDOWN_MONTH.addItem(value, element2.internalKey.substring(14), index1 );	    																	

				}

			}

		}

	});

});

g_month = value_selected;

DROPDOWN_MONTH.setSelectedValue(value_selected);

Note: CPL_MN_TX InfoObject master data is loop twice to sort Time Selection according to SQ order

Note: Fiscal Year, Fiscal to Last Month and Month selections are formatted differently (with or without separators) to keep all entries in drop down (for example, Fiscal to Last Month might be the same key value as Month in May e.g. 04-2018 - 04-2018

P.S. I noticed that even though CPL_MN_TX InfoObject is defined as Direct Access system still might cache data and use it. This creates issues with Time Selections when time goes across the months.

To resolve the issue change Cache Mode property of CPL_MN_TX/!!ACPL_MN_TX in Query Monitor (trx. RSRT) to Cache is inactive

In second part of the blog I will explain how effectively provide selections for dimensions with large number of members

Dashboard Selection Made Easy – Part 1: Time Selection

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z