A formula variable represents a user-defined formula, which determines a value for the variable. In contrast to a value variable, the default value for a formula variable is not fixed. When you execute a report that uses formula variables, the system calculates values for the formula variables automatically (for example, using the system date or other variables).
For example, your report displays data for the current year and the previous year. If you create two independent variables for the current and previous year, you have to enter both years on the selection screen when you output the report. If you define the variable for the previous year as a formula variable, which derives its value from the current year variable, you only have to enter the current year on the selection screen.
For more information on creating formula variables, see Creating Formula Variables.
Using formula variables, you can create several new types of reports. The following examples illustrate how you can use formula variables in your reports.
Example 1: Rolling Periods
See the example report 0R-FOVAR (library 0R1) delivered with the Special Purpose Ledger (FI-SL) application component.
This report uses formula variables to create report columns containing twelve periods. When you enter the reporting period and the reporting year during report execution, the system outputs data for the reporting period and the previous eleven periods.
To define the example report with rolling periods, you need to create twelve formula variables for the twelve periods, and twelve formula variables for the corresponding fiscal year. You should create the variables in the order listed in the table below.
You create a key figure for each pair of variables when you create a Report Writer report. (You must first create basic and multidimension sets that contain the formula variables.) Alternatively, you can enter the variables directly in the report definition if you are creating a Report Painter report.
Formula Variables Used in the Example Rolling Periods Report
Period variable |
Formula |
Year variable |
Formula | |
Column 12 |
&0R-RP00 |
#S002 |
&0R-RY00 |
#S001 |
Column 11 |
&0R-RP01 |
IF 0R-RP00 > 1 THEN 0R-RP00 - 1 ELSE 12 |
&0R-RY01 |
IF 0R-RP01 < 0R-RP00 THEN 0R-RY00 ELSE 0R-RY00 - 1 |
Column 10 |
&0R-RP02 |
IF 0R-RP01 > 1 THEN 0R-RP01 - 1 ELSE 12 |
&0R-RY02 |
IF 0R-RP02 < 0R-RP01 THEN 0R-RY01 ELSE 0R-RY01 - 1 |
Column 9 |
&0R-RP03 |
IF 0R-RP02 > 1 THEN 0R-RP02 - 1 ELSE 12 |
&0R-RY03 |
IF 0R-RP03 < 0R-RP02 THEN 0R-RY02 ELSE 0R-RY02 - 1 |
Column 8 |
&0R-RP04 |
IF 0R-RP03 > 1 THEN 0R-RP03 - 1 ELSE 12 |
&0R-RY04 |
IF 0R-RP04 < 0R-RP03 THEN 0R-RY03 ELSE 0R-RY03 - 1 |
Column 7 |
&0R-RP05 |
IF 0R-RP04 > 1 THEN 0R-RP04 - 1 ELSE 12 |
&0R-RY05 |
IF 0R-RP05 < 0R-RP04 THEN 0R-RY04 ELSE 0R-RY04 - 1 |
Column 6 |
&0R-RP06 |
IF 0R-RP05 > 1 THEN 0R-RP05 - 1 ELSE 12 |
&0R-RY06 |
IF 0R-RP06 < 0R-RP05 THEN 0R-RY05 ELSE 0R-RY05 - 1 |
Column 5 |
&0R-RP07 |
IF 0R-RP06 > 1 THEN 0R-RP06 - 1 ELSE 12 |
&0R-RY07 |
IF 0R-RP07 < 0R-RP06 THEN 0R-RY06 ELSE 0R-RY06 - 1 |
Column 4 |
&0R-RP08 |
IF 0R-RP07 > 1 THEN 0R-RP07 - 1 ELSE 12 |
&0R-RY08 |
IF 0R-RP08 < 0R-RP07 THEN 0R-RY07 ELSE 0R-RY07 - 1 |
Column 3 |
&0R-RP09 |
IF 0R-RP08 > 1 THEN 0R-RP08 - 1 ELSE 12 |
&0R-RY09 |
IF 0R-RP09 < 0R-RP08 THEN 0R-RY08 ELSE 0R-RY08 - 1 |
Column 2 |
&0R-RP10 |
IF 0R-RP09 > 1 THEN 0R-RP09 - 1 ELSE 12 |
&0R-RY10 |
IF 0R-RP10 < 0R-RP09 THEN 0R-RY09 ELSE 0R-RY09 - 1 |
Column 1 |
&0R-RP11 |
IF 0R-RP10 > 1 THEN 0R-RP10 - 1 ELSE 12 |
&0R-RY11 |
IF 0R-RP11 < 0R-RP10 THEN 0R-RY10 ELSE 0R-RY10 - 1 |
The formula variables &0R-RP00 (period of the last column) and &0R-RY00 (fiscal year of the last column) are the only independent variables in the report columns. Each pair of variables calculates the period and fiscal year of the preceding period. The year changes when the system calculates the preceding periods based on period one.
Example 2: Displaying Periods of Quarter q
This example report displays three columns for the periods of quarter q.
To define the report columns, you must first create the value variable &QUARTER for the characteristic Period. You enter Quarter as the variable description. This variable is the independent variable. You then need to create three formula variables (listed in the table below) for the three periods in the quarter.
Formula Variables Used to Calculate the Periods in Quarter q
Period variable |
Formula | |
Column 1 |
&QPER1 |
QUARTER * 3 - 2 |
Column 2 |
&QPER2 |
QUARTER * 3 - 1 |
Column 3 |
&QPER3 |
QUARTER * 3 |
The above example assumes that the first quarter consists of periods one, two, and three. You enter the formula variables &QPER1, &QPER2 and &QPER3 in key figures if you are creating a Report Writer report. Alternatively, you can enter the variables directly in the column definition if you are creating a Report Painter report.
The independent variable &QUARTER is not used directly in the report; it is used indirectly via the formula variables. When you execute the reports report group, the Report Writer creates the input field Quarter for the independent variable &QUARTER. You can then enter quarter one, two, three, or four to output your report for the respective quarter.
Example 3: Displaying Periods of Quarter q Based on Period p
This example report displays three columns for the periods of quarter q. Unlike the report in example two, you enter one period in the quarter and not the quarter itself on the report selection screen to determine the three period columns.
To define the report columns, you must first create the value variable &PERIOD for the characteristic Period. Enter Period as the variable name. This variable is the independent variable. You then need to create three formula variables (listed in the table below), which determine the three periods in the quarter.
Formula Variables Used to Calculate the Periods of Quarter q Based on Period p
Period variable |
Formula | |
Column 1 |
&QPER1 |
((PERIOD - 1) DIV 3 + 1) * 3 - 2 |
Column 2 |
&QPER2 |
((PERIOD - 1) DIV 3 + 1) * 3 - 1 |
Column 3 |
&QPER3 |
((PERIOD - 1) DIV 3 + 1) * 3 |
The above example assumes that the first quarter consists of periods one, two, and three. The independent variable &PERIOD is not used directly in the report; it is used indirectly via the formula variables. When you execute the reports report group, the Report Writer creates the input field Period for the independent variable &PERIOD. You can then enter period one, two or three to output your report for the periods in the first quarter.
Example 4: Displaying a Variable Number of Columns for Periods p1 to p12
This example report allows you to create a report containing a variable number of columns for periods one to twelve. It is not possible to define a variable number of columns directly in the report definition; you can do so, however, by creating a column block consisting of twelve columns, each represented by a formula variable.
The formula variables map the unwanted periods up to period 16. If data has been posted for period 16 in your system, you should use a different period that does not contain data (for example, period 17).
To define the report columns, you must first create the value variables &PER_FROM and &PER_TO for the characteristic Period. These variables are the independent variables. You must then create the formula variables (listed in the table below), which all refer to the independent period variables. Once you have created the formula variables, you must enter them in a basic set (created for the characteristic Period) if you are creating a Report Writer report. Alternatively, you can enter them directly in the column definition if you are creating a Report Painter report.
Formula Variables Used to Display a Variable Number of Report Columns
Period variable |
Formula | |
Column 1 |
&PER01 |
IF 1 >= PER_FROM AND 1 <= PER_TO THEN 1 ELSE 16 |
Column 2 |
&PER02 |
IF 2 >= PER_FROM AND 2 <= PER_TO THEN 2 ELSE 16 |
Column 3 |
&PER03 |
IF 3 >= PER_FROM AND 3 <= PER_TO THEN 3 ELSE 16 |
Column 4 |
&PER04 |
IF 4 >= PER_FROM AND 4 <= PER_TO THEN 4 ELSE 16 |
Column 5 |
&PER05 |
IF 5 >= PER_FROM AND 5 <= PER_TO THEN 5 ELSE 16 |
Column 6 |
&PER06 |
IF 6 >= PER_FROM AND 6 <= PER_TO THEN 6 ELSE 16 |
Column 7 |
&PER07 |
IF 7 >= PER_FROM AND 7 <= PER_TO THEN 7 ELSE 16 |
Column 8 |
&PER08 |
IF 8 >= PER_FROM AND 8 <= PER_TO THEN 8 ELSE 16 |
Column 9 |
&PER09 |
IF 9 >= PER_FROM AND 9 <= PER_TO THEN 9 ELSE 16 |
Column 10 |
&PER10 |
IF 10 >= PER_FROM AND 10 <= PER_TO THEN 10 ELSE 16 |
Column 11 |
&PER11 |
IF 11 >= PER_FROM AND 11 <= PER_TO THEN 11 ELSE 16 |
Column 12 |
&PER12 |
IF 12 >= PER_FROM AND 12 <= PER_TO THEN 12 ELSE 16 |
The Suppress zero columns indicator must be set in the standard layout assigned to the report, so that columns that do not contain any values are not displayed in the report output.
When you execute the reports report group, the Report Writer creates input fields for the independent variables &PER_FROM and &PER_TO, which determine the period range.
Example 5: Calculating Defaults for a Non-Calendar Fiscal Year
You can create formula variables that default to the current period and fiscal year taken from the system date. If you work with a non-calendar fiscal year, you can create formula variables that calculate the correct period and fiscal year using the current system date and period.
Example Formula Variables to Calculate Defaults for a Non-Calendar Fiscal Year
Description |
Formula |
Calendar year from the system date |
#S001 |
Calendar month from the system date |
#S002 |
Non-calendar fiscal year calculated from the system date (the non-calendar fiscal year starts in July) |
IF #S002 > 6 THEN #S001 + 1 ELSE #S001 |
Period calculated from the system date (the non-calendar fiscal year starts in July) |
IF #S002 >6 THEN #S002 - 6 ELSE #002 + 6 |