The post describes a hierarchy on a monthly base. You can of course adapt it for daily, weekly or yearly hierarchies.
Challenge
For some reports, it’s necessary that analysts can decide for themselves which time slices they want in a query view.
For example, one analyst might want to see the data for the next three months, another one might want to compare the data for the current month with the same month last year while a third analyst needs the last six months‘ worth of data for a trend analysis.
As you can see, there are thousands of possibilities and combinations, depending on the number of users as well as the specific reporting requirements.
Example
Cube data:
0CALMONTH | Sales |
… | … |
06.2012 | 70 |
07.2012 | 90 |
… | … |
03.2013 | 80 |
04.2013 | 120 |
05.2013 | 130 |
06.2013 | 60 |
07.2013 | 100 |
Monthly reporting requirement I
Show me sales for the last three month:
reporting month: 06.2013 | reporting month: 07.2013 | |||||||||||||||||||
| --> next month reporting |
|
Monthly reporting requirement II
Compare actual month sales with the same month last year:
reporting month: 06.2013 | reporting month: 07.2013 | |||||||||||||
| --> next month reporting |
|
Normally, there are two ways to meet such reporting requirements:
- Set the filter for all relevant time slices and save the filter selection as a variant. A variant, however, contains only fixed values and must be changed each month
- Create new queries for each request with pre-defined variables with Customer-Exit and Offset on 0CALMONTHThat could, however, mean that you have to implement a lot of reports, one report for each specific reporting requirement
Would it not be better if the IT department implements only one query and the user could control which time slices are needed? It would be great and the user would love it. Trust me on this one.
Idea
The idea behind all this is a generic date hierarchy which is updated each month:
0CALMONTH node assignment for reporting month June and July 2013 | |||
Nodes | June 2013 | July 2013 | |
… | … | … | |
M-12 | 06.2012 | 07.2012 | |
M-11 | 07.2012 | 08.2012 | |
… | … | … | |
M-3 | 03.2013 | 04.2013 | |
M-2 | 04.2013 | 05.2013 | |
M-1 | 05.2013 | 06.2013 | |
M0 | 06.2013 | 07.2013 | |
M+1 | 07.2013 | 08.2013 | |
M+2 | 08.2013 | 09.2013 | |
| ... | ... | ... | |
M+12 | 06.2014 | 07.2014 | |
… | … | … | |
For the requirements above:
- Monthly reporting requirement I: select hierarchy nodes M-1, M-2 & M-3
- Monthly reporting requirement II: select hierarchy nodes M0 & M-12
The hierarchy nodes selection has to be done only once and can be saved as a workbook or query view. If the user refreshes the report next month, he will get the correct data in regard to the current month and the selected time slices.
Concept
New classes based on class ZCL_GENERIC_HIERARCHY are needed (described in post generic-hierarchies-in-sap-bw😞
- Class ZCL_GENERIC_HIERARCHY_DATE extends the super class by two new attributes: low and high range/border: set how many nodes will be generated in the past and in the future
- Class ZCL_GEN_HIER_CALMONTH implements the custom coding in the redefined BUILD_HIERARCHY() method
Implementation
The result should be like this:
Hierarchy example generated at the beginning of July 2013
At first, we need to redefine the inherited method BUILD_HIERARCHY() with our own coding. As an example, I will only describe the coding for node M0:
Coding example
**********************************************************************
* generate node for current month
**********************************************************************
lv_current_month = sy-datum(6).
CALL METHOD me->_create_node
EXPORTING
i_iobjnm = '0HIER_NODE'
i_nodename = 'M0'
RECEIVING
r_node = node1.
lv_nodename = lv_current_month.
CALL METHOD me->_create_node
EXPORTING
i_iobjnm = '0CALMONTH'
i_nodename = lv_nodename
RECEIVING
r_node = node2.
* add node1 to hierarchy and node2 to node1 as child:
me->_add_node( node1 ).
node1->add_child( node2 ).
Now we have to instantiate a new hierarchy object on InfoObject 0CALMONTH:
Coding example
**********************************************************************
* generate monthly hierarchy on 0CALMONTH
* class ZCL_GEN_HIER_CALMONTH implements the BUILD_HIERARCHY() method
* from ZCL_GENERIC_HIERARCHY with custom coding
**********************************************************************
DATA: gh_calmonth TYPE REF TO ZCL_GEN_HIER_CALMONTH,
CREATE OBJECT gh_calmonth
EXPORTING
i_tech_name = 'ZHIER_GEN_CALMONTH'
i_stxt = 'generic month hier'
i_mtxt = 'generic month hierarchy'
i_iobj = '0CALMONTH'.
gh_calmonth->generate( ). "build and activate the SAP hierarchy
If you call the method …->generate( ). a SAP hierarchy will be created based on the custom coding in the redefined BUILD_HIERARCHY() method.
Other examples
Generic hierarchy for different time slices (will be generated each month)
