The customer has the requirement to divide the current month in a time series to be evaluated into "to date" (historical view) and "from today" (view into the future).
Among other things, stocks and demand forecasts are evaluated. The user also has the option of freely selecting the respective time periods in the past and future.
Conversion using a BW query should be carried out using a fixed structure that shows which structural element belongs to the past and which to the future.
Table of contents
1.1 Customer exit variable for showing and hiding the structure objects
1.2 Creating text variables ready for input
Step 2: Creating a time structure
2.1 0Calmonth is used as a structural element and restricts
2.2 Properties of the structure
Step 3: Characterization of the CustomerExit
In a query, elements of a structure are to be shown and hidden depending on a date selection. In the application example described, a characteristic structure is created that evaluates 12 months from the current month, both in the future and in the past. A fixed time structure had to be created, as calculations have to be made at runtime in cell references(SAP link) depending on whether the month under review is in the past or the future. The report user decides in the selection screen which months are to be evaluated in the report. In the definition of the structure, a variable can be used to control the display or hiding of structural elements.
In general, structures allow variable-controlled structural elements to be visible or hidden.
Research has shown that there is little information/best practices to be found on the internet for this issue. In this blog article, the implementation of this question is described step by step.
Step 1: Creating variables
1.1 Customer exit variable for showing and hiding the structure objects
Variables are always created in SAP BW on the basis of an info object. Through trial and error and debugging, it was determined that a variable that controls the visibility of structure elements must be created on the 1MEMBER object.
A new variable of the type characteristic value and processing via customer exit is created. The reference characteristic is 1MEMEBER
When creating the variables, please note that the object 1MEMBER cannot be inserted via the search, but only by direct input:
1.2 Creating text variables ready for input
The CustomerExit variable described under 1.1 must determine values based on user input. The user entries are therefore made in text variables that record the number of months in the past and in the future.
To do this, 2 text variables must be created that are ready for input.
The text variables are then included in display-suppressed structure elements so that they can be used as filters in the query.
The following entries can then be made when calling up the report:
The text variables are then included in display-suppressed structure elements so that they can be used as filters in the query.
The following entries can then be made when calling up the report:
Step 2: Creating a time structure
Caution The procedure described can only be implemented using local structures; it is not possible to set a variable for the visibility of structure elements on global structures.
Structural elements must be created for the entire period, which look 12 months into the past and 12 months into the future from today. It should be noted that the current month is also divided into "up to today" and "from today".
2.1 0Calmonth is used and restricted as a structural element
2.1.1 The individual structural elements follow a fixed naming convention, which can later be used to determine the correct element for display
To ensure that the structure elements can be clearly identified in the customer exit, the individual elements must be given a technical name that clearly defines which time element is involved. In our example, a maximum of 12 months into the past and 12 months into the future should be reported. The date variable itself is restricted by an offset variable. The structural element "012" is therefore restricted to the month of the previous year, the structural element "112" to the month of the following year, "001" is the previous month, "101" is the following month and so on.
2.1.2 Das Strukturelement „bis heute“ wird eingeschränkt auf den aktuellen Kalenderjahr/monat (über die SAP-Exitvariable 0CMONTH), zusätzlich wird der Kalendertag auf <= aktueller Kalendertag (über die Sap-Exitvariable 0DAT) begrenzt
2.1.3 The element "from today" is restricted to the current calendar year/month (via the sap exit variable 0CMONTH) and the calendar day is also restricted to > current calendar day (via the sap exit variable 0DAT).
2.1.4 The previous and subsequent months are then generated by variable offset: In the figure shown an example for the previous month
2.1.5 The text replacement variable is inserted into the description of the structure element so that the respective calendar year/month is displayed when the report is called up.
2.2 Properties of the structure
In the general properties of the structure, the visibility of structure elements must be set to "Can be changed via variable". The customer exit variable created under 1.1 is included in the variable. It ensures that only the elements selected by the user are displayed in the report result.
Step 3: Characterization of the CustomerExit
The customer exit for the 1Member variable then determines the objects of the structure that are displayed from the time windows entered (the text variables ready for input). For example, if the user selects to evaluate 5 months into the past and 7 months into the future, the structure elements from 001 to 005 (for the past and elements 101 to 107 for the future) are selected and thus displayed in the report result.
Step 4: Defining the query and calling up the report
The query was then defined in such a way that the time structure is displayed in the columns and the key figures in the rows. Cell references can then be used to output both a historical stock key figure and a future-related forecast based on order quantities in a row. The definition of the query is very individual and is not the focus of this step-by-step guide.
The structural elements that the user has selected via their entries are then displayed in the report result.
Further elements of the time structure can be selected or deselected in the report result via the report filter of the structure.
Summary
By setting the visibility in the structure, elements can not only be shown and hidden by direct user input, but also by controlling a variable. The variable must be defined as a customer exit variable and on the 1MEMBER object. In the customer exit, the visibility can be controlled in the coding reference to other inputs/restrictions.
If no variable is selected in the structure settings, the user is offered all structure elements in the selection screen, which must then be selected.
However, the solution shown using the 1Member variable can only be implemented in local structures. In a global structure, it is not possible to enter a variable.
For different queries that use the same structure, the references to the global structure must be removed. This can lead to considerable additional work if the structure is changed.
The implemented solution has established itself in the described customer requirement for evaluation and is a simple way for the user to analyze different time periods. The fixed structure defines whether past or future values are involved.
Author: Wiebke Schulz
Your contact person
Kay Rohweder
Senior Manager
SAP Information Management
kay.rohweder@isr.de
+49 (0) 151 422 05 242
