Calculating Formula Rows, Columns, and Key Figures
Exercise 10: Working with Dependent Sections and Cells
Exercise Objectives
After completing this exercise, you will be able to:
• Extend reports to calculate your own key figures by referencing data from previous sections of the report, such as calculated key figure block
• Explain the term - dependent section
Business Example
The underlying Reporting Table does not directly offer some of the key figures relevant to your business. However, these key figures can be calculated from the data. Therefore, you will access data cells from previous sections of the report to calculate your own key figures.
Task:
1. The report continues to build where it now calculates the Personnel Costs/Employee in addition to displaying the information account by account. Create the report Z6##-003 (base it on Z6##-002). Mark the cells that you need to perform the calculation in the new sections.
Note: Use the set OAS_PERS for the personnel costs.
Figure 58:
Continued on next page
- Define Additional Row for Personnel Costs (OAS_PERS) - Select Cells (Personnel Costs: Actual and Plan)
- Hide Personnel Costs Row
- Select Cells (Employee: Actual and Plan) - Create a Calculated Key Figure Section - Name the Section
- Define Row Text - Define Column Text - Define Formula Cells - Check and Save the Report
- Assign the Report to the Report Group Z6##
- Execute the Report
Hint: To toggle between reports in the same report group, use the menu path Goto → Other Report or click on Other Report.
Hint: If there is no actual data in the current year try running the report for 2006.
2. Why do you need to Set the Explode level on the Cost Elements to Explode or Do Not Explode?
3. What is meant by Dependent Section?
Solution 10: Working with Dependent Sections and Cells
Task:
1. The report continues to build where it now calculates the Personnel Costs/Employee in addition to displaying the information account by account. Create the report Z6##-003 (base it on Z6##-002). Mark the cells that you need to perform the calculation in the new sections.
Note: Use the set OAS_PERS for the personnel costs.
Figure 59:
- Define Additional Row for Personnel Costs (OAS_PERS) - Select Cells (Personnel Costs: Actual and Plan)
- Hide Personnel Costs Row
- Select Cells (Employee: Actual and Plan) - Create a Calculated Key Figure Section - Name the Section
- Define Row Text - Define Column Text - Define Formula Cells
Continued on next page
- Check and Save the Report
- Assign the Report to the Report Group Z6##
- Execute the Report
Hint: To toggle between reports in the same report group, use the menu path Goto → Other Report or click on Other Report.
Hint: If there is no actual data in the current year try running the report for 2006.
a) Choose Information System → Ad Hoc reports → Report Painter → Report → Createor transaction GRR1.
Library: Z##
Report: Z6##-003
Description: Calculated key figures Copy From
Report: Z6##-002 Select Create.
Define additional row for personnel costs (OAS_PERS))
To use the personnel costs as part of the formula, you have to enter the set OAS_PERS in a separate line to access the top totals levels. Ensure the row is either in expand or do not expand setting. This is required to access the grand total line of the costs. After the explode setting is correctly set, proceed to select the cells.
Double-click the next available row. In the select element type window, choose Characteristics and Confirm.
In the Available Characteristicspane select Cost Element and Move selected to left. Select the hierarchy node and enter OAS_PERS in the From field. Select Confirm and choose the cost element group by double-clicking. Select Confirm again.
Check the explode level by positioning on the new row and selecting Edit → Rows → Explode. Ensure that this is NOT set to single values.
Select Confirm.
Select Cells (Personnel Costs: Actual and Plan)
Double-click the cell defined by the intersection of Personnel Costs
Double-click the cell defined by the intersection of Personnel Costs and Plan.
Hide Personnel Costs Row
Position on the personnel costs row.
Choose Edit → Rows → Hide.
Select Cells (Employee: Actual and Plan) Choose Goto → Section → Next section or .
Double-click the cell defined by the intersection of Employees and Act Stat Qty.
Double-click the cell defined by the intersection of Employees and Plan Stat Qty.
Hint: If you copied the previous report, these cells may already be selected.
Create a Calculated Key Figure Section Choose Edit → Sections → New section.
In response to the New Report Section prompt, select Section with calculated key figures.
Select Confirm. The new section is displayed straight away.
Name the Section
Enter a text description for the section.
Define Row Text
Double-click Row 1 and enter the text for the row (e.g., Personnel Costs/Emp). Select Confirm.
Define Column Text
Double-click Column 1 to define text for the Actual column. Do the same for column 2 (Plan).
Define Formula Cells
Double-click the cell defined by the intersection of Personnel Costs/Emp and Actual and enter the formula, ‘personnel costs actual’/‘employee actual’. Your formula will reference Z001, Z002 etc., depending on the relationship of your Z number to the cell value.
Then Confirm.
Continued on next page
Double-click the cell defined by the intersection of Personnel Costs/Emp and Plan and enter the formula, ‘personnel costs plan’ /
‘employee plan’ . Then Confirm.
Check and Save the Report
1. Check the report for any errors or missing characteristics by choosing Report → Check or use Check or F6.
2. Save the Report via Report → Save or use Save or CTRL + S.
Assign the Report to the Report Group Z6##
1. Environment → Assign Report Group Report Group Z6##
2. Select Continue.
3. Save the report with . Execute the Report
Click Execute .
Use the following table for the Selection values.
Fiscal Year Current Year
Period 1 to 12
Plan Version 0
Actual Valuation 0
Cost Center Group H1010
Click Execute.
Select the Calculated Key Figuresreport.
Hint: If there is no Actual Data for the current year try rerunning the report for 2006.
2. Why do you need to Set the Explode level on the Cost Elements to Explode or Do Not Explode?
a) This is because you need to have access to the grand total of the hierarchy. If the OAS_PRIM set is defined as Single Values, you do not have access to the grand total.
3. What is meant by Dependent Section?
a) A Dependent Section is a section of the report where no new data is extracted from the summary tables. Only existing data from other sections of the report are referenced and used in calculations.
Lesson Summary
You should now be able to:
• Explain the concept of sections
• Use cells in formulas
• Define sections with calculated key figures
Unit Summary
You should now be able to:
• Explain the concept of sections
• Use cells in formulas
• Define sections with calculated key figures
Test Your Knowledge
1. You can use the cells selected in the report to calculate formula columns in the same way as standard columns.
Determine whether this statement is true or false.
□ True
□ False
2. When defining a section with a calculated key figure, what are the steps you must follow to choose cells for a subgroup?
Choose the correct answer(s).
□ A Define an additional report row
□ B Choose the required cell
□ C Select cells in exploded rows
□ D Hide the report row
3. Name the two types of sections in a report.
Answers
1. You can use the cells selected in the report to calculate formula columns in the same way as standard columns.
Answer: True
You can use the cells selected in the report to calculate formula columns in the same way as standard columns.
2. When defining a section with a calculated key figure, what are the steps you must follow to choose cells for a subgroup?
Answer: A, B, D
When defining a section with calculated key figure, if you want to choose cells for a subgroup, you have to define an additional report row for the subgroup, choose the required cell, and hide the report row.
3. Name the two types of sections in a report.
Answer: A report has the following types of sections:
• Sections with characteristics and key figures
• Sections with calculated key figures