Skip to Content

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:

  1. 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.
  2. This is an independent variable which is not directly used in the report but used indirectly via the other formula variables.
  3. 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:

  1. 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
  2. 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:

  1. The Fiscal year for which the report is executed is fetched from the selection screen
  2. The Fiscal year for the other 7 columns depends on the Fiscal year and the Quarter entered in the selection screen
  3. 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

/wp-content/uploads/2012/08/1_125321.png

Element Definition for Column 1

/wp-content/uploads/2012/08/2_125322.png

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.

/wp-content/uploads/2012/08/3_125323.png

Element Definition for Column 2

/wp-content/uploads/2012/08/4_125325.png

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.

/wp-content/uploads/2012/08/5_125326.png

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

/wp-content/uploads/2012/08/6_125327.png

Element Definition for Column 1

/wp-content/uploads/2012/08/7_125328.png

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.

/wp-content/uploads/2012/08/8_125329.png

Element Definition for Column 2

/wp-content/uploads/2012/08/9_125330.png

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.

/wp-content/uploads/2012/08/10_125331.png

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.

/wp-content/uploads/2012/08/11_125332.png

Mention the library, Table, Description in the Report Group Definition

/wp-content/uploads/2012/08/12_125333.png

Assign both the Balance Sheet and the P&L reports to the report group

/wp-content/uploads/2012/08/13_125334.png

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

/wp-content/uploads/2012/08/14_125335.png

/wp-content/uploads/2012/08/15_125336.png

/wp-content/uploads/2012/08/16_125337.png

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-9cfc811177c6?QuickLink=index&overridelayout=true&48056389655525

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

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

    1. Shinas Hamsa

      Could you please provide some information on Balance sheet , P&L and cash flow with report painter.

      Or could you sent me some steps or config documents to  shinasrh@gmail.com

      I have never worked on report painter .

      Thanks in advance

      Shinas

      (0) 
      1. Subhadeep Das

        Hi,

        Sorry I am entering into this discussion thread with a different question, actually I am stuck. I have developed a TB profit center wise, though in GR55 when i execute the report,everything is coming fine except the column total.

        It will be great if anybody can help me with some advice.

        best regds

        Subha

        (0) 

Leave a Reply