Creating Reports Using Report Painter
Unit Overview
This unit explains how to structure, generate, and execute a Report Painter report. The unit begins with the description of the structure of a Report Painter report. Next, the unit explains how to define rows, columns, and general data selections in a Report Painter report. Further, this unit explains how report groups are used to generate and execute reports. This involves exploding rows and sorting hierarchies in reports. Finally, the unit describes how variables can be used for report definition and how text can be entered in reports.
Unit Objectives
After completing this unit, you will be able to: • Define the structure of a Report Painter report • Define rows in a Report Painter report • Define columns in a Report Painter report
• Define general data selections in a Report Painter report
• Explain how report groups are used to generate and execute reports • Use variables for report definition
• Enter text in reports
Unit Contents
Lesson: Structuring Reports... 27 Exercise 2: Components of a Report Painter Report ... 37 Lesson: Generating and Executing Reports ... 40 Exercise 3: Building and Executing a Report With a Total Row ... 47 Exercise 4: Using Hierarchies: Explode function ... 55 Exercise 5: Using Hierarchies: Sequence (Sort)... 63
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Lesson: Structuring Reports
Lesson Overview
This lesson explains how to structure a Report Painter report. First, the lesson describes the structure of a Report Painter report. Next, it explains how to define rows and columns in a Report Painter report. Finally, the lesson explains how to define general data selections in a Report Painter report.
Lesson Objectives
After completing this lesson, you will be able to: • Define the structure of a Report Painter report • Define rows in a Report Painter report • Define columns in a Report Painter report
• Define general data selections in a Report Painter report
Business Example
You work as a financial consultant for XYZ Cement Company. You are a member of the project team responsible for creating reports using Report Painter and Report Writer. The management of your company wants to know how much each cost center is spending toward each account. You need to create a report using Report Painter, which shows these details.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Structure of Report Painter Reports
Figure 15: Basic Report Structure
To define a report, you must determine the row and column structure and the general data selection criteria.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 16: Report Painter Report Structures
With the format presented by the Report Painter, you can easily enter rows, columns, and general selection criteria.
The rows constitute characteristic values or groups.
The columns contain basic key figures, such as costs and quantities with characteristic values for key figures, which are combinations of predefined basic key figures/characteristic values.
General data selections are carried out using characteristic values, which are valid throughout the entire report.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Defining Rows and Columns
Figure 17: Row and Column Definition
You can use either a combination of characteristic values or a formula to define a row. Columns contain a combination of a key figure and optional restricting characteristic values. You can also use predefined columns to display business-relevant
combinations of key figures and characteristics, such as, actual costs in the current period and scheduled activity.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 18: Row Definition
To define rows, you select the characteristics you want to display in these rows and enter the appropriate values.
You can enter specific values, intervals of values, or a group.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 19: Formula Rows
You can use the formula editor to define formula rows. The formulas used can refer to other rows in the same section and to cells selected in the report.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
SAP delivers a number of predefined columns for the libraries in Overhead Cost Management. You can copy these predefined columns directly into your reports and modify them as required.
Figure 21: Defining a Single Column
One way to define a column is to combine a key figure with several characteristics. To restrict the characteristics, you can enter intervals or groups as in the case of defining single values for the report rows.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 22: Formula Columns
Using the formula editor, you can calculate additional columns within a section. The formulas used can refer to other columns in the same section or to cells selected in the report.
Restricting Data in Reports
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
After you have defined the rows and columns, you can define additional criteria that are valid for the entire report. The additional criteria you select will restrict the data processed in the report. These restrictions are stored in so-called general data selections.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Exercise 2: Components of a Report Painter
Report
Exercise Objectives
After completing this exercise, you will be able to:
• Explain the following key terms in the Report Painter: Characteristic, basic key figure, and key figure
Business Example
Management requires the summary values for specific cost centers that were derived from certain cost element accounts.
You know that this report is not included in the Management Accounting reporting tree. This means that you have to create it using the Report Painter.
Task:
Using your own words, define the key terms associated with Report Painter. 1. What is a characteristic?
2. What is a basic key figure? 3. What is a key figure?
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Solution 2: Components of a Report Painter
Report
Task:
Using your own words, define the key terms associated with Report Painter. 1. What is a characteristic?
a) A characteristic, also called ’field’ or ’dimension’ in previous releases, is a non-numeric field. Examples of characteristics include “accounts”, “cost centers”, and “business areas”. The characteristics, when combined, form a unique key that identifies your data records for database data retrieval. Your organizational elements can be regarded as characteristics.
2. What is a basic key figure?
a) A basic key figure is a value field, such as posted amounts or quantities. Basic key figures are used to determine which value fields are displayed in a report, such as transaction currency, group currency, and quantities. 3. What is a key figure?
a) A key figure contains a basic key figure, which is combined with restricting characteristics. These characteristics, such as the value type actual and plan, are used as additions to the basic key figure. For data fields, which are represented frequently in reports, key figures (which are predefined columns) can be used to define reports more quickly.
4. What is the purpose of the General Data Selections?
a) In addition to the explicit data in the rows and columns, you can also define criteria that are valid throughout the entire report, which can be characteristic values or characteristic groups, to further restrict the data processed in the report. These restrictions are specified in the general data selections. In the report 1SIP-001 shipped with the system, the general data selections contain the characteristics ’Company code’, ’Fiscal year’, ’Period’, and ’Cost center’.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Lesson Summary
You should now be able to:• Define the structure of a Report Painter report • Define rows in a Report Painter report • Define columns in a Report Painter report
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Lesson: Generating and Executing Reports
Lesson Overview
This lesson explains how report groups are used to generate and execute reports. The lesson also describes how variables can be used for report definition and how text can be entered in reports.
Lesson Objectives
After completing this lesson, you will be able to:
• Explain how report groups are used to generate and execute reports • Use variables for report definition
• Enter text in reports
Business Example
You work as a financial consultant for XYZ Cement Company. The management of the company wants a report with the details of how much each cost center is spending toward each account. You have created this report using the Report Painter. The company wants you to group this report to a report group. Reports that read the same data should be grouped together in report groups to improve processing times. Assign this report to a report group and generate and execute the report group.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Using Report Groups
Figure 24: Report Generation and Execution
Before a report can be output, it must be included in a report group.
A report group is a collection of reports in a library that are executed in one run. Combining several reports in a report group can be useful if the reports are intended to evaluate the same dataset. In this case, the data is read once and then distributed among different reports.
You cannot process reports from different libraries in the same report group.
When a report group is generated, the system creates ABAP reports that you can run. In the first ABAP, inputs are proposed and verified with regard to output parameters, the data source, and extract parameters. In addition, values or sets are proposed for selection if the report definition contains variables.
In the second ABAP, data is selected from the database. The last ABAP formats data so that it can be output.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 25: Exploding Report Rows
When using groups or several single values to define rows, you can define whether only a totals row is to be displayed, or the complete group hierarchy with subtotals is to be exploded, or every single value in a row is to be displayed. The system is set up in such a way that only those rows are displayed for which corresponding data exists.
Sorting Hierarchies
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
If you use a combination of several characteristics in a row block and this row block is exploded, you can choose the hierarchical sequence in which the characteristics are displayed.
Using Variables in Reports
Figure 27: Flexible Reports with Variables
Instead of using fixed values in the report definition, you can also use variables. When you execute a report group, an input field appears on the report selection screen for each variable used.
There are three types of variables: value variables, variables for groups (set variables), and formula variables.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Defining Text
Figure 28: Texts
You can define texts at the following points within a report: • Title page
• Header • Footer • Final page
When the report is exported, say, to Excel, the worksheet header is taken from the exported text. The header and footer in the report are intended for lists and, therefore, do not appear in the worksheet. This also applies to the title page and final page.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Figure 29: Entering Text
You can enter text directly for the title page, header, footer, and final page. You can also use the following types of text variables:
• General variables • Selection parameters • Characteristics • Special variables
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Exercise 3: Building and Executing a Report
With a Total Row
Exercise Objectives
After completing this exercise, you will be able to: • Create a Report Painter report
• Assign reports to other report groups • Execute a report
Business Example
The management wants you to create a report that displays cost center-wise actual costs vis-à-vis planned costs and the resulting variances for the current year. You need to use the Report Painter to create this report.
Task:
You have been asked to create a report that contains the following information:
Figure 30:
The general selection criteria throughout the report are the controlling area 1000, the cost center group H1010, the current fiscal year, and the periods 1 - 12.
1. Use the Report Painter to build this report. Name the report Z1##-001 (description: “Rep. w. totals lines”) and assign it to the report group Z1##. Create your report for your own library Z##.
Note: Define column 1 using a key figure. Define column 1 using ’Basic key figure with characteristics’.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Use the following cost element groups:
Cost element group Description
OAS_WAGES Wages
OAS_SALAR Salaries
OAS_P_IMP Inc. costs
Instructions for defining the report Z1##-001. - Define Rows
- Define Formula (Total) Row - Define Column 1 - Actual Costs - Define Column 2 - Plan Costs - Define Formula (Variance) Column - Define General Data Selections - Check and Save the Report
- Assign the Report to a Report Group - Execute the Report
2. How do you access the row information? 3. How do you access the column information?
4. What is the difference between the two element types that can be used to define a column: a) a key figure and b) a basic key figure with characteristics?
5. What is the difference between generating and executing a report group? 6. Which menu path did you use to link your report to a report group?
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Solution 3: Building and Executing a Report
With a Total Row
Task:
You have been asked to create a report that contains the following information:
Figure 31:
The general selection criteria throughout the report are the controlling area 1000, the cost center group H1010, the current fiscal year, and the periods 1 - 12.
1. Use the Report Painter to build this report. Name the report Z1##-001 (description: “Rep. w. totals lines”) and assign it to the report group Z1##. Create your report for your own library Z##.
Note: Define column 1 using a key figure. Define column 1 using ’Basic key figure with characteristics’.
Use the following cost element groups:
Cost element group Description
OAS_WAGES Wages
OAS_SALAR Salaries
OAS_P_IMP Inc. costs
Instructions for defining the report Z1##-001. - Define Rows
- Define Formula (Total) Row
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
- Define Column 1 - Actual Costs - Define Column 2 - Plan Costs - Define Formula (Variance) Column - Define General Data Selections - Check and Save the Report
- Assign the Report to a Report Group - Execute the Report
a) Instructions for defining the report Z1##-001. From the main R/3 menu, choose
1. Information Systems → Ad-hoc Reports → Report Painter
2. Report → Create
Library: Z## Report: Z1##-001
Description: ’Rep. w. totals lines’.
Click Create to access the report definition interface.
Define Rows Double-click row 1.
1. Select the cost element in the Available Characteristics dialog box. Click Move selected to left.
2. In the Selected characteristics pane, click the Group field and enter the cost element group OAS_WAGES. Click Confirm or Enter. You have defined row 1. Now define the remaining three rows.
When you define Rows 2 and 3, an additional prompt is displayed in which you can choose the element type. Select Characteristics. Click Confirm or
Enter.
Define Formula (Total) Row
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Double-click row 4.
1. In the Select Element Type screen, choose Formula. Click Confirm or Enter.
2. In the Enter Formula screen, use the pushbuttons at the bottom of the screen to define the Total Row as Y001 + Y002 + Y003. Click
Confirm or Enter.
3. In the Enter Texts screen, enter texts for the total row. Copy the text to the Medium and Long fields. Click Confirm or Enter.
Define Column 1 - Actual Costs Double-click column 1.
1. In the Select Element Type screen, choose Predefined key figure. Click Confirm or Enter.
2. In the Choose Predefined Column screen, choose Actual costs. Click
Confirm or Enter.
3. Click Confirm or Enter to review the ensuing screens. These screens show how the key figure is defined. That is, these screens display the basic key figures and characteristics and their values that were used to define the key figure.
You have now defined column 1.
Define Column 2 - Plan Costs Double-click column 2.
1. In the Select Element Type screen, choose Key figure with
characteristics. Click Confirm or Enter.
2. Under ’Basic key figure’, choose Costs.
3. Select Value type and Version in the Available Characteristics pane. Click Move selected to left.
4. Enter the value 01 for the Value Type to denote plan values. Enter the value 0 for the version to denote the current version and enter a suitable text. Click Confirm.
You have now defined column 2.
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Define Formula (Variance) Column Double-click column 3.
1. In the Select Element Type screen, choose Formula. Click Confirm or Enter.
2. In the Enter Formula screen, use the pushbuttons to define the Variance Column as X001 – X002. Click Confirm or Enter.
3. In the Enter Texts screen, enter texts for the variance column. Copy the text to the Medium and Long fields. Click Confirm or Enter.
Define General Data Selections
1. Choose Edit → Gen. data selection.
2. In the Available Characteristics pane, choose the characteristics that are to be included in the general parameters for the report (see table below). Click Move selected to left.
3. In the Selected Characteristics pane, enter the parameter values for the report. Click Confirm or Enter.
Use the following table for the characteristic and the associated values.
Controlling area 1000 in the From field
Fiscal year Current fiscal year in the From field Period 1 to 12 in the From and To fields
respectively
Cost center H1010 in the Group field
You have now defined the general data selections.
Check and Save the Report
Check the report for any errors, or missing characteristics, by choosing
Report → Check or use Check or F6.
Save the Report via Report → Save or use Save or CTRL+S.
Assign the Report to a Report Group
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
You can assign the report to a report group by choosing: Environment →
Assign report group.
1. Enter the value Z1##.
This creates the report group which is assigned to the library Z##.
Execute the Report Choose Report → Execute.
2. How do you access the row information?
a) Double-click the row to access the detail screens. 3. How do you access the column information?
a) Double-click the column to access the detail screen.
4. What is the difference between the two element types that can be used to define a column: a) a key figure and b) a basic key figure with characteristics?
a) A key figure has already been defined in mySAP ERP by SAP or by another member of your reporting team. A basic key figure column with restricting characteristics is a column in which you can define your column information “on-the-fly”. That is, you can dynamically select your basic key figures and characteristics.
5. What is the difference between generating and executing a report group? a) Generating a report group creates the ABAP code for the report.
Executing the report group executes the ABAP code.
6. Which menu path did you use to link your report to a report group? a) From the report definition, choose:
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Exercise 4: Using Hierarchies: Explode
function
Exercise Objectives
After completing this exercise, you will be able to:
• Analyze an existing hierarchy and use different explode options for your report • Use the explode function
Business Example
The management was delighted to see how quickly you generated the report. Now, they want you to create the same report for the entire company’s cost element hierarchy.
Task 1:
Use the diagram below to answer the following questions:
Figure 32:
1. If the hierarchy was used as a report row, how would the report results screen appear when the explode level was set to ’explode’?
2. If the hierarchy was used as a report row, how would the report results screen appear when the explode level was set to ’Do Not Explode’?
3. If set to single values?
I n t e r n a l U s e S A P P a r t n e r O n l y
I n t e r n a l U s e S A P P a r t n e r O n l y
Task 2:
Create another report Z2##-001 with the following structure:
Figure 33:
The general selection criteria throughout the entire report are the controlling area 1000, the cost center group H1010, the current fiscal year, and the periods 1 - 12. 1. The cost element group CA705_COST contains all the accounts, including the