Applies to:
FICO Report Painter reports being developed in SAP ECC 6.0.
Summary:
This document will help in creating a quarterly rolling report (both cumulative and non-cumulative) in Report Painter. The quarters for which values are to be displayed being dynamically decided based on the inputs provided in the selection screen.
Introduction
In standard SAP, the parameters ‘Period’ and ‘Fiscal Year’ are available for use in reports. However the parameter ‘Quarter’ is not pre-defined to be readily available for use in report painter reports. Business would like quarter on quarter report for financial analysis
Objective
The document is a step by step guide in setting up all the required variables, report element definition, element name definition and selection screen for creating a quarterly rolling (cumulative and non-cumulative) report.
Pre-requisite
The document requires the reader to have a basic understanding of creating reports in report painter and knowledge of creating variables.
Scope
The document does not cover how to create a report painter report from scratch or does not explain how to define variables. The scope of the document is restricted to enabling the reader to create a report with values for multiple quarters for respective years, which are both dynamically determined based on the quarter and year entered in the selection screen.
Business Requirement
Finance department would require not only a year on year comparison but also multiple period evaluations and also a multiple quarter evaluation. While the rolling year/period report can be created using the predefined variables available in SAP, the multiple quarter report requires us to create our own variables for identifying the quarters and the years related to the quarters. In this document, the business requirement for creating an 8 quarter rolling report for Balance Sheet (Cumulative) and Profit & Loss Account (non-cumulative) is being considered.
Solution
Follow below steps to achieve the business requirement.
Note: For detailed documentation on ‘How to create Variables to be used in Report Painter reports’, please see “Related Contents” at the end of this document where another document detailing how to create the variables is referenced.
Step 1: Create the 8 ‘Quarter’ Variables and the independent variable &QUARTER
Notes:
- The first variable &QUARTER is a value variable and it gets its value from the input given in the selection screen. All other variables are formula variables.
- This is an independent variable which is not directly used in the report but used indirectly via the other formula variables.
- When you execute the report’s report group, Report Writer creates the input field ‘Quarter’ for the independent variable &QUARTER – values 1, 2, 3 or 4 can be entered in the selection screen depending on the output required.
Variable | Variable Type | Description | Table | Field Name | Internal Variable | Formula | Default Value | Comments |
QUARTER | Value | Quarter | FAGLFLEXT | RPMAX | No | NA | 1 | 'QUARTER' Value is from Selection screen |
Q1 | Formula | Quarter 1 | FAGLFLEXT | RPMAX | Yes | 'Quarter' | NA | |
Q2 | Formula | Quarter 2 | FAGLFLEXT | RPMAX | Yes | IF 'Q1' > 1 THEN 'Q1' - 1 ELSE 4 | NA | |
Q3 | Formula | Quarter 3 | FAGLFLEXT | RPMAX | Yes | IF 'Q2' > 1 THEN 'Q2' - 1 ELSE 4 | NA | |
Q4 | Formula | Quarter 4 | FAGLFLEXT | RPMAX | Yes | IF 'Q3' > 1 THEN 'Q3' - 1 ELSE 4 | NA | |
Q5 | Formula | Quarter 5 | FAGLFLEXT | RPMAX | Yes | IF 'Q4' > 1 THEN 'Q4' - 1 ELSE 4 | NA | |
Q6 | Formula | Quarter 6 | FAGLFLEXT | RPMAX | Yes | IF 'Q5' > 1 THEN 'Q5' - 1 ELSE 4 | NA | |
Q7 | Formula | Quarter 7 | FAGLFLEXT | RPMAX | Yes | IF 'Q6' > 1 THEN 'Q6' - 1 ELSE 4 | NA | |
Q8 | Formula | Quarter 8 | FAGLFLEXT | RPMAX | Yes | IF 'Q7' > 1 THEN 'Q7' - 1 ELSE 4 | NA |
Step 2: Create the 16 Period Variables with respect to each quarter
Variable | Variable Type | Description | Table | Field Name | Internal Variable | Formula | Default Value | Comments |
Q1P1 | Formula | Period 1 for Quarter 1 | FAGLFLEXT | RPMAX | Yes | 'Q1' * 3 - 2 | NA | |
Q1P3 | Formula | Period 3 for Quarter 1 | FAGLFLEXT | RPMAX | Yes | 'Q1' * 3 | NA | |
Q2P1 | Formula | Period 1 for Quarter 2 | FAGLFLEXT | RPMAX | Yes | 'Q2' * 3 - 2 | NA | |
Q2P3 | Formula | Period 3 for Quarter 2 | FAGLFLEXT | RPMAX | Yes | 'Q2' * 3 | NA | |
Q3P1 | Formula | Period 1 for Quarter 3 | FAGLFLEXT | RPMAX | Yes | 'Q3' * 3 - 2 | NA | |
Q3P3 | Formula | Period 3 for Quarter 3 | FAGLFLEXT | RPMAX | Yes | 'Q3' * 3 | NA | |
Q4P1 | Formula | Period 1 for Quarter 4 | FAGLFLEXT | RPMAX | Yes | 'Q4' * 3 - 2 | NA | |
Q4P3 | Formula | Period 3 for Quarter 4 | FAGLFLEXT | RPMAX | Yes | 'Q4' * 3 | NA | |
Q5P1 | Formula | Period 1 for Quarter 5 | FAGLFLEXT | RPMAX | Yes | 'Q5' * 3 - 2 | NA | |
Q5P3 | Formula | Period 3 for Quarter 5 | FAGLFLEXT | RPMAX | Yes | 'Q5' * 3 | NA | |
Q6P1 | Formula | Period 1 for Quarter 6 | FAGLFLEXT | RPMAX | Yes | 'Q6' * 3 - 2 | NA | |
Q6P3 | Formula | Period 3 for Quarter 6 | FAGLFLEXT | RPMAX | Yes | 'Q6' * 3 | NA | |
Q7P1 | Formula | Period 1 for Quarter 7 | FAGLFLEXT | RPMAX | Yes | 'Q7' * 3 - 2 | NA | |
Q7P3 | Formula | Period 3 for Quarter 7 | FAGLFLEXT | RPMAX | Yes | 'Q7' * 3 | NA | |
Q8P1 | Formula | Period 1 for Quarter 8 | FAGLFLEXT | RPMAX | Yes | 'Q8' * 3 - 2 | NA | |
Q8P3 | Formula | Period 3 for Quarter 8 | FAGLFLEXT | RPMAX | Yes | 'Q8' * 3 | NA |
Notes:
- To defiine the From period and To period in the Report Painter report, we need to define the formulae for the From and To periods for each of the Quarters defined in Step 1
- The From period for each quarter created as “Period 1 for Quarter N” and the To period for each quarter defined as “Period 3 for Quarter N”
Step 3: Create the 8 Fiscal Year Variables with respect to each quarter
Variable | Variable Type | Description | Table | Field Name | Internal Variable | Formula | Comments |
Q1Y1 | Formula | Fiscal Year for Quarter Q1 | FAGLFLEXT | RYEAR | Yes | '0F-RY00' | '0F-RY00' Value is from Selection Screen |
Q2Y2 | Formula | Fiscal Year for Quarter Q2 | FAGLFLEXT | RYEAR | Yes | IF 'Q2' < 'Q1' THEN 'Q1Y1' ELSE 'Q1Y1' - 1 |
|
Q3Y3 | Formula | Fiscal Year for Quarter Q3 | FAGLFLEXT | RYEAR | Yes | IF 'Q3' < 'Q2' THEN 'Q2Y2' ELSE 'Q2Y2' - 1 |
|
Q4Y4 | Formula | Fiscal Year for Quarter Q4 | FAGLFLEXT | RYEAR | Yes | IF 'Q4' < 'Q3' THEN 'Q3Y3' ELSE 'Q3Y3' - 1 |
|
Q5Y5 | Formula | Fiscal Year for Quarter Q5 | FAGLFLEXT | RYEAR | Yes | IF 'Q5' < 'Q4' THEN 'Q4Y4' ELSE 'Q4Y4' - 1 |
|
Q6Y6 | Formula | Fiscal Year for Quarter Q6 | FAGLFLEXT | RYEAR | Yes | IF 'Q6' < 'Q5' THEN 'Q5Y5' ELSE 'Q5Y5' - 1 |
|
Q7Y7 | Formula | Fiscal Year for Quarter Q7 | FAGLFLEXT | RYEAR | Yes | IF 'Q7' < 'Q6' THEN 'Q6Y6' ELSE 'Q6Y6' - 1 |
|
Q8Y8 | Formula | Fiscal Year for Quarter Q8 | FAGLFLEXT | RYEAR | Yes | IF 'Q8' < 'Q7' THEN 'Q7Y7' ELSE 'Q7Y7' - 1 |
|
Notes:
- The Fiscal year for which the report is executed is fetched from the selection screen
- The Fiscal year for the other 7 columns depends on the Fiscal year and the Quarter entered in the selection screen
- Standard SAP variable &0F-RY00 captures the value of Fiscal year entered in the selection screen
Step 4: Call the variables defined in Steps 1, 2 and 3 above in the element definition of the report
Step 4(A): Cumulative Balance Sheet Report
For the Balance Sheet report, the From period is always ‘0’ as the requirement is a cumulative report. The balance sheet report has to display the balances of GL accounts as of the end of the respective quarter and that balance should already include the opening balances at the beginning of the respective year.
Define the elements of the report as below
Report Format
Element Definition for Column 1
Note that the fiscal year is the variant &Q1Y1 which is the year pertaining to the quarter &Q1 and the From Period is ‘0’ and the To Period is variant &Q1P3 which is the 3rd period in quarter &Q1
Text Maintenance for the Element
Define the text for first column as below. This carries the values entered in the selection screen for the Quarter and Fiscal year. All columns after this carry values for the quarter earlier respectively.
Element Definition for Column 2
Note that the fiscal year is the variant &Q2Y2 which is the year pertaining to the quarter &Q2 and the From Period is ‘0’ and the To Period is variant &Q2P3 which is the 3rd period in quarter &Q2
Note that the From period here is not checked as a variable. It is a constant value.
Text Maintenance for the Element
Define the text for second column as below. This carries the values for the Quarter prior to the quarter entered in the selection screen.
Follow the pattern and define the elements and the texts for all the 8 quarters in 8 columns in the Balance Sheet report.
Step 4(B): Non-Cumulative Profit and Loss Report
For the P&L report, the From period is also variable unlike the Balance Sheet report. This is because of the business requirement being to assess the performance of the organization for a particular quarter and compare the performance on a quarter on quarter basis.
Define the elements of the P&L report as below
Report Format
Element Definition for Column 1
Note that the fiscal year is the variant &Q1Y1 which is the year pertaining to the quarter &Q1 and the From Period is variant &Q1P1 which is the 1st period in quarter &Q1 (unlike the static ‘0’ in the Balance Sheet report. This will ensure that the figures displayed are not cumulative but pertaining to only one quarter) and the To Period is variant &Q1P3 which is the 3rd period in quarter &Q1
Text Maintenance for the Element
Define the text for first column as below. This carries the values entered in the selection screen for the Quarter and Fiscal year. All columns after this carry values for the quarter earlier respectively.
Element Definition for Column 2
Note that the fiscal year is the variant &Q2Y2 which is the year pertaining to the quarter &Q2 and the From Period is &Q2P1 which is the 1st period in quarter &Q2 and the To Period is variant &Q2P3 which is the 3rd period in quarter &Q2. The financials results that would be displayed in this column pertain to the quarter prior to the quarter entered in the selection screen
Text Maintenance for the Element
Define the text for second column as below.
Follow the pattern and define the elements and the texts for all the 8 quarters in 8 columns in the P&L report.
Step 5: Selection Screen – Field Quarter
After creating the reports as above, include both reports into a new report group.
Mention the library, Table, Description in the Report Group Definition
Assign both the Balance Sheet and the P&L reports to the report group
The Report Writer will ensure that the field “Quarter” is displayed as an input field in the selection screen of the report group when executed (report code will be regenerated and the field would be included) based on the fact that the independent variable &QUARTER is indirectly used in the reports and the those reports are assigned to the report group
Step 6: Execute Report
Related Content
For information on how to create Variables to be used in Report Painter reports refer http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/805176e8-8a53-2d10-cbbc-9cfc81117...
For information on other variables that can be used in Report Painter Reports refer http://help.sap.com/saphelp_470/helpdata/en/5b/d22ea043c611d182b30000e829fbfe/content.htm
- SAP Managed Tags:
5 Comments
