Many executives and managers want to make decisions based on financial trends over 12 history periods, four previous quarters, or some combination of these time frames. Consider a typical period close process. Reports are generated repeatedly to ensure that all required financial impacts have been recorded.

This reporting requirement has prompted a number of expensive and inflexible custom ABAP reports, Report Painter reports with complex and confusing selection screens, and manual processes that involve running a report several times in order to copy and paste data into a spreadsheet. Each of these methods fails to provide trend reporting that is simple to use and maintain.

One methodology allows power users to write and maintain their own trend reports using Report Painter. It is a problem-solving technique that goes beyond what you can find in the Help logic. I'll show you how to produce a single report comparing the period being closed with plan, each of the previous four periods, quarter to date, year to date, and the corresponding time frames from the previous year with the entry of just a single period. These reports require the end user to enter only a single fiscal period and year on the selection screen. This fiscal period is used as the base period in the trend report, and all other time periods are calculated dynamically.

This methodology makes use of Report Painter and a few repetitive formula variables. While this article focuses on Report Painter, users who are experienced with its set-based interface may also use formula variables in place of standard variables within Report Writer.

Formula variables are reusable components of Report Painter that contain simple calculations used to determine characteristic values in a report. They are used in lieu of hard-coding period values into report definitions. Whereas standard variables would force the user to manually enter many fiscal periods prior to running the report, formulas are used to derive the value of these variables. This functionality is available in all current versions of R/3.

Set Up Formula Variables
The following explanation will guide you through the creation of custom formula variables you can use to define your dynamic trend reports. The first building block to put in place when implementing this methodology is the setup of formula variables. Once they are in place, these variables seldom require maintenance. Variables are created in transaction GS11 and are maintained in GS12. Begin by creating base variables for the current fiscal period and year. These variables appear on the selection screen of the report each time it is run, so the users can change their values. They are defined so they default to the previous fiscal period, as it is most likely to contain a full set of meaningful data.

Give each variable a short technical name on the initial GS11 screen. While the name itself bears no significance, it is important to use a naming convention that you can remember and share with team members. For these examples, I'm naming the variable for current fiscal period ZCURPD. This variable will be based upon the field RPMAX in table GLPCT and be of type Formula (Figure 1). Regardless of the table referenced, this variable will be available for use within all Report Painter reports that contain a compatible period characteristic.

Figure 1

Create the current period variab

Once you have entered these details on the first screen, press enter on your keyboard to advance to the detailed variable definition. By clicking on the formula exit button on the left side of this screen, you can see a list of internal values made available in standard R/3. The value of these formula exits is populated automatically by the system without any user intervention. The exits titled In Accordance with CO Fiscal Year Variant are determined by analyzing a combination of the system date and the user's fiscal year variant. The fiscal year variant is taken from the controlling area specified in the user's parameters. Use menu path System>User Profile>Own Data from any menu. Navigate to the Parameters and enter CAC as the parameter ID and the appropriate controlling area as the parameter value. Formula exit S006 serves as the basis for determining the current period.

Keep in mind that this example is seeking the most recently closed fiscal period, which is really one fewer than the fiscal period determined by the system date. Assume that this company has 12 fiscal periods. If the system date period is 1, the variable value should be 12. In all other cases, it is simply one less than the value of S006. Thus, you enter the formula into the formula box as IF ‘#S006'=1 THEN 12 ELSE ‘#S006'-1 (Figure 2).

Figure 2

Definition of the Current Period variable

Note that the box for Internal variable is not selected. This means that the calculated value is suggested when the report is run, but can still be changed by the user on the selection screen. Also, note that the Description of the variable determines what will appear next to its input field on the report selection screen. You can change this description to something meaningful, such as Current Period, Base Period, or Last Closed Period by typing over the proposed text.

ZCURYR becomes the variable for fiscal year that is associated with ZCURPD. Together, they are always used when defining report columns. The initial value of this variable also depends on the system date. If the fiscal year has just changed, the previous fiscal year should be suggested to the user. Otherwise, the fiscal year is set to the current system fiscal year. Therefore, the definition is again dependent upon the value of S006. S001 is the standard formula exit provided to determine the system date fiscal year. This variable is based upon field RYEAR of table GLPCT. The formula you enter is IF ‘#S006'=1 THEN ‘#S001'-1 ELSE ‘#S001' (Figure 3).

Figure 3

Definition of the Fiscal Year variable

Define Previous Periods
Now that the base periods are available for use, you can start working backward to define previous periods. You can use a similar approach to define future periods if plan or forecast numbers are to be included. Corresponding variables for fiscal period and fiscal year will again be defined for each previous period. Rather than referencing the R/3 standard formula exits, you instead reference other custom formula variables. The variable for previous period (ZPD-1) considers the value in ZCURPD. The value in ZCURPD may be the period determined by its formula logic or something totally different if the user makes a different entry on the selection screen. Enter this formula as IF ‘ZCURPD'=1 THEN 12 ELSE ‘ZCURPD'-1 (Figure 4).

Figure 4

Definition of the Prev Period variable

The fiscal year associated with the previous fiscal period, ZYR-1, is calculated as the result of IF ‘ZCURPD' =1 THEN ‘ZCURYR'-1 ELSE ‘ZCURYR' (Figure 5). Again, ZPD-1 and ZYR-1 are always used in conjunction while defining reports.

Figure 5

Definition of the fiscal year for previous period variable

By including reference to other variables in the formula, the system forces selection of the Internal variable checkbox. This removes the variable from the report selection screen so that the user does not need to see these details in action. From this point on, it is a matter of repetition to create the remaining variables for two periods previous, three periods previous, etc. See Figure 6 for the detailed formulas used to determine four previous periods.

ZCURPD IF ‘#S006’ = 1 THEN 12 ELSE ‘#S006’ - 1 ZCURYR IF ‘#S006’ = 1 THEN ‘#S001’ - 1 ELSE ‘#S001’ ZPD-1 IF ‘ZCURPD’ = 1 THEN 12 ELSE ‘ZCURPD’ - 1 ZYR-1 IF ‘ZCURPD’=1 THEN ‘ZCURYR’ - 1 ELSE ‘ZCURYR’ ZPD-2 IF ‘ZPD-1’ = 1 THEN 12 ELSE ‘ZPD-1’ - 1 ZYR-2 IF ‘ZPD-1’ = 1 THEN ‘ZYR-1’ - 1 ELSE ‘ZYR-1’ ZPD-3 IF ‘ZPD-2’ = 1 THEN 12 ELSE ‘ZPD-2’ - 1 ZYR-3 IF ‘ZPD-2’ = 1 THEN ‘ZYR-2’ - 1 ELSE ‘ZYR-2’ ZPD-4 IF ‘ZPD-3’ = 1 THEN 12 ELSE ‘ZPD-3’ - 1 ZYR-4 IF ‘ZPD-3’ = 1 THEN ‘ZYR-3’ - 1 ELSE ‘ZYR-3’

Variables in Reports
Using these variables in reports is straightforward. You can launch Report Painter directly using transaction GRR1. Include the fiscal period and fiscal year as characteristics in each column definition. Check the brown and yellow variables column to indicate that a variable is being used and specify the appropriate variable name (Figure 7).

Figure 7

Use variables in a column definition

It is also useful to dynamically populate the column header with the value of its period and fiscal year. You do this by including an ampersand and the variable name (Figure 8).

Figure 8

Dynamically display the period value in your column header

Other text may accompany the variable values. For example, you can type in Pd &ZCURPD and it will print out as Pd 1 on the report. It may be necessary to specify that medium or even long text should be used in the column headers so the variable names can fit into the text definition box. This setting is found under Formatting>Row/column texts on the menu path within the report definition.

Defining the formula variables for quarters is slightly more complex, as only a single "if...then...else..." statement is allowed in each variable definition. Thus, it is necessary to use a little bit of math and logic to determine the appropriate quarter and its related periods.

The primary example to consider is calculating the quarter that corresponds to ZCURPD. This variable enables quarter-to-date reporting for the quarter in which variable ZCURPD falls. The first step is to create an internal variable (ZQ) that contains the value of the quarter. This variable is never used in the definition of columns because they must contain the corresponding period values. It may still be used in column header text to tell the user which quarter is being displayed.

Because R/3 uses integer math and rounds calculations to the nearest whole number (e.g., 1/3 = 0), this formula contains special logic for the first period of each quarter. It is written as follows: IF ‘ZCURPD' = 1 OR ‘ZCURPD' = 4 OR ‘ZCURPD' = 7 OR ‘ZCURPD' = 10 THEN ‘ZCURPD' / 3 + 1 ELSE ‘ZCURPD' / 3.

To use this calculation in a column definition, you must define two additional variables to calculate the beginning and ending periods for this quarter. ZQF, the "from period," uses this formula: (‘ZQ' - 1) * 3 + 1. The "to period," ZQT, can simply add to the results of ZQF — ‘ZQF' + 2. There is no need to create a new variable for the fiscal year that corresponds to these variables, as it can use the previously defined ZCURYR. Now, you can use these variables to define a quarter-to-date column (Figure 9).

ZQ IF ‘ZCURPD’ = 1 OR ‘ZCURPD’ = 4 OR ‘ZCURPD’ = 7 OR ‘ZCURPD’ = 10 THEN ‘ZCURPD’ / 3 + 1 ELSE ‘ZCURPD’ / 3 ZQF (‘ZQ’ - 1) * 3 + 1 ZQT ‘ZQF’ + 2 ZQ-1 IF ‘ZQ’ = 1 THEN 4 ELSE ‘ZQ’ - 1 ZQY-1 IF ‘ZQ’ = 1 THEN ‘ZCURYR’ - 1 ELSE ‘ZCURYR’ ZQ-2 IF ‘ZQ-1’ = 1 THEN 4 ELSE ‘ZQ-1’ - 1 ZQY-2 IF ‘ZQ-1’ = 1 THEN ‘ZQY-1’ - 1 ELSE ‘ZQY-1’ ZQ-3 IF ‘ZQ-2’ = 1 THEN 4 ELSE ‘ZQ-2’ - 1 ZQY-3 IF ‘ZQ-2’ = 1 THEN ‘ZQY-2’ - 1 ELSE ‘ZQY-2’

You can calculate the previous quarter in a similar fashion to the previous period. ZQ-1 checks to see if ZQ was the first quarter in a fiscal year — IF ‘ZQ' = 1 THEN 4 ELSE ‘ZQ' - 1. Next, you create a variable (ZQY-1) for calculating the corresponding fiscal year. Its formula is IF ‘ZQ' = 1 THEN ‘ZCURYR' - 1 ELSE ‘ZCURYR'. Subsequent variables for the previous quarters again follow suit, as shown in Figure 9. Use these variables as depicted in Figure 10.

Figure 10

Quarter-to-date column definition

Variable Calculations
Keep in mind that these formula variables can be used in conjunction with fixed values. For example, you can define a year-to-date column with a "from" value of period 1 (or 0 for balance sheet) and a "to" value of the variable ZCURPD. Also, you can construct simple formula variables to represent previous fiscal years. Perhaps you want a comparison of the current period or quarter results with the previous fiscal year. You can create a variable that simply subtracts one from the value of ZCURYR to determine the previous fiscal year.

The process I've outlined may provide the potential for variable calculations that you never previously considered. Perhaps stockholder trend reports outlining the previous 16 quarters can become more automated by defining a number of quarterly variables and including them in formatted financial statements.

Another favorite is a 12-period trend report for the current year. It combines actual results with plan data for future periods to forecast the annual results. Define a report that contains two complete sets of column definitions — one for plan and another for actual record types. Create a unique formula variable for each column (period 1 actual, period 1 plan, period 2 actual, etc.) to determine the "version" characteristic. Include logic that considers the ZCURPD value and sets the version characteristic to an invalid value when the column should not be considered.

For example, plan period 1 should only be displayed when the value of ZCURPD is equal to 1, as this is the only time period 1 actuals are not complete. Thus, the formula may read IF ‘ZCURPD' = 1 THEN 0 ELSE 99 (where 99 is an invalid version number). Likewise, actual period 1 should only be displayed when the value of ZCURPD is greater than 1, so its corresponding formula variable may read IF ‘ZCURPD' > 1 THEN 0 ELSE 99. Specify in the report layout that zero columns should be suppressed and suddenly, you have a report that dynamically mixes actual and plan data.

Matt Christensen

Matt Christensen is the director of Enterprise Performance Management at PRAGMATEK Consulting Group in Minneapolis. He has more than seven years of experience implementing a broad set of SAP financial modules, including configuration and development in R/3, Business Information Warehouse, and Strategic Enterprise Management. Matt holds an undergraduate degree in computer science and an MBA in finance.

You may contact the author at matt.christensen@pragmatek.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.