Many companies use profitability analysis (CO-PA) to manage and report on their billing, booking, and backlogs (BBB). However, depending on the reporting and uploading process there may be issues with the analysis based on whether the results are positive or negative as well as which types of accounts you are reviewing. We’ll review a solution to this issue and allow business users to see the variances in a BBB report based on the calculation of a better/worse (B/W) report. We’ll start with an explanation of the problems you can encounter with the +/- signs and then get into the following step-by-step procedure:

• Step 1. Create hidden columns and formulas of B/W and cell editors to present the right +/- signs
• Step 2. Use reversed signs for fewer cell editors to be created
• Step 3. Create hidden columns and formulas of B/W % and cell editors to present the right +/- signs
• Step 4. Check and adjust the layout of the columns based on the business scenarios of BBB with multiple currencies
• Step 5. Create InfoObject TYPE CODE to control BBB DSO for period-end closing
• Step 6. Use the timestamp to prevent duplication
• Step 7. Implement the BBB calculation logic
• Step 8. Verify data loading for period-end closing and new-period opening with sample data
• Step 9. Review the sample BBB queries

For this article, we used SAP ERP Central Component (SAP ECC) 6.0 and SAP NetWeaver Business Warehouse (SAP NetWeaver BW) 7.0 systems, though it works for earlier systems. This information is particularly helpful to share with your SAP NetWeaver BW developers.

Problems with the Sign of +/- with Cost and Profit

To better control the cost and profit of a corporation, some companies include B/W columns with quantities in the reports (Figure 1). The quantity in the B/W columns usually indicates an increase or decrease of the cost or profit compared with forecast, actual, commitments, or plan in the time period indicated (e.g., period, month, quarter, or year). It is an intuitive way to see the better or worse performance of a corporation. If users define a positive quantity of Forecast–Mid Quarter Flash for the profit values to indicate that it is better, then a negative means worse. On the other hand, a positive quantity of costs (such as Total Standard Cost of Parts or Burden) indicates worse, and a negative quantity means better (Figure 2).  

Figure 1

An example of CO-PA reports

Figure 2

B/W columns in a CO-PA report

Step 1. Create Hidden Columns and Formulas of B/W and Cell Editors to Present the Right +/- Signs

It is difficult to implement the above B/W profit and cost requirements in the same column. However, you can use additional hidden columns to realize the same purpose. The hidden column can contain the same absolute value, but with reversed math signs. You can assign the values of the hidden columns to the displayed columns via cell editors (Figure 3).

Figure 3

B/W columns in a CO-PA report with hidden column and cell editors

Figure 4 shows a formula of a displayed column called B/W Q1 Forecast/MQF (mid quarter flash). The detailed formula is shown in the detail view.

Figure 4

Positive math sign B/W column formula

On the other hand, you can define the hidden column formula B/W Q1 Forecast/MQF COS as shown in Figure 5. Note the formula is changed from A – B to B – A. It has the same effect as changing the value from A to -A.

Figure 5

Reversed math sign of B/W column formula

You can use the cell editor here to link between the hidden column and the displayed column. Figure 6 illustrates how to bring up the cell editor window and make linkages. The column with formula signs is the hidden column, which contains actual calculation formulas to be used to overwrite the displayed column. The displayed column is next to the hidden column and with the blue ball sign. When cells in the displayed column select the hidden column cells next to them, then the connections are established.    

Figure 6

Cell editor window in a Business Explorer (BEx) Query Designer with both displayed and hidden columns

The cell formula contains nothing but the selected cell (Figure 7).

Figure 7

A formula with displayed column in the cell editor

Step 2. Use Reversed Signs for Fewer Cell Editors to Be Created

Sometimes the number of rows of cost and profit makes a big difference in a query. Making a good choice of which columns should be hidden reduces the number of cell editors to be created. Figure 8 shows an alternative way to implement the same query shown in the Figure 3. In Figure 8, the hidden and displayed properties are swapped among two columns, compared to Figure 3. Therefore the cell editors to be edited reduce from 9 in Figure 3 to 3 in Figure 8. Note that the math signs of amounts in the same column are reversed. For example, Total New Order of B/W Forecast/MQF is -$400,000.00 in Figure 3, but is $400,000.00 in Figure 8. The reason is that the hidden and displayed properties for the same column are reversed. 

Figure 8

Revised Better/Worse column in a CO-PA report

Step 3. Create Hidden Columns and Formulas of B/W % and Cell Editors to Present the Right +/- Signs

Along with the B/W columns, sometimes users want to see the percentage of the B/W. B/W % columns have a similar sign problem as the B/W columns. Figure 8 is an example of a B/W % implementation.

Figure 9 shows a formula of a displayed column with the percentage of the B/W Q1 Forecast/MQF. The detailed formula is shown in the detail view. Note that the A / B % A1 is a workaround for missing signs. It can directly write the formula A % B, if you’ve applied Support Package 16 for SAP NetWeaver 7.0.

Figure 9

An example of the B/W % formula

An example of the column definition with the reversed sign of the percentage values is shown in Figure 10.

Figure 10

Reversed math sign of the B/W % column formula

Step 4. Check and Adjust the Layout of the Columns Based on the Business Scenarios of BBB with Multiple Currencies

The requirements for a corporation to see BBB reports at each period or month end are very common and very important. However, exchange rate changes between periods may cause problems when reading the reports. For example, if the corporation has a global business with multiple currencies, and the previous fiscal period backlog was €100 and the exchange rate was 1.47, then the previous period backlog in USD was $147. If the next period exchange rate is changed to 1.42, then the backlog in USD is changed to $142. If this backlog is fulfilled within the period, the correct difference as a result should be zero. The backlog with the foreign currency key is €0.00. However, without making the proper adjustment, there is a difference of $5. To accurately reflect the situation, the USD backlog difference amount needs to be adjusted with the difference of the exchange rates between the current period and the previous period. The right result in this case is $0.00. This increases the difficulty of the CO-PA design.

Step 5. Create InfoObject TYPE CODE to Control BBB DSO for Period-End Closing

To implement the BBB queries to meet the requirements with multiple currencies, you need to create InfoObject TYPE CODE. Its master data contains three types of BBB data:

• Type D: The data is directly loaded from the CO-PA data source via an optimized DataStore object (DSO). This data is current-period CO-PA transaction data.
• Type S: There are two types of S records. The first type S copies over the last fiscal period CO-PA type D transaction data and assigns it to the new type S. The second type S is to add the last period S value and last period I type values for the same key fields records and to copy the sum value to the current period during the close operation.
• Type I: This data calculates the discrepancy between the last fiscal period S type data and their increment or decrement with the current period exchange rate.

The data loaded with delta mode (i.e., only loading changed or new data) from the CO-PA data source contains actual CO-PA daily transactional data. Using additional types D, S, and I of the characteristics, additional different types can be represented. The D type represents daily delta data. The S type represents summary data from the last period. Type I represents the adjustment data with the difference between the last period and the current period exchange rates.

Here is an example scenario of the period-end closing process (Figure 11):

1. The period-end closing of CO-PA is similar to the FI period-end closing. It is an automatic process by scheduled job in the process chain. A new D type recode is generated by copying the last period’s transaction data type D to the new period. However, the quantity and amount signs are reversed. The adjustment amount is calculated using formula that quantity times difference between the current period exchange rate and last period exchange rate. For example, 924,860 * (1.47 – 1.42) = 46,243 in this scenario.
2. The closing process also copies the last period transaction D type record to the S type. It also copies ending quantity and amount column values to the corresponding beginning columns.
3. The I type record is inserted with the exchange rate-adjusted amount. This amount is copied to the ending backlog column. The purpose is for generating a zero balance for the period-end amount of this transaction record. The BBB cube granularity is in the sales order level for drilling down. 
4. After summation of all D, I, and S type records in a period for each sales document, you can see the summation results are correct. The end period backlog amount and quantity are zeroes, but the amount and quantity of the beginning of the period match the last period-end values (Figure 12).
5. The process rolls over at the end of the current period periodically. 

Figure 11

Sample data view in the BBB staging DSO

Figure 12

Period-end amount in the USD column with the adjusted amount

This design can meet all user requirements:

• Beginning BBB group currency amount values match the ending BBB group currency amount values as shown in the example

• Beginning BBB local currency amount values match the ending BBB local currency amount values as shown in the example

• Sum of the open backlog for the period 200812 is zero, because the backlog amount 1313301.2 was filled up. The leftover from the last period was adjusted to -1313301.2 from -1359544.2 when the rate change from 1.47 to 1.42 was adjusted.

Step 6. Use the Timestamp to Prevent Duplication

Since two S type values are stored in the same period with the same key field values, if there is not an additional key field value to control it, the last record overwrites the record with the same key values. A simple piece of ABAP (as shown in Figure 13) will do the job. Since the record row number is under control and performance is accepted, the data type is kept as CHAR. Otherwise, you can change it to the NUMCc type by using the move statement. The timestamp code is shown in Figure 13.

Figure 13

A simple piece of timestamp ABAP code

Step 7. Implement the BBB Cube with a BBB Data Model

There are several ways to implement the BBB cube. The BBB data needs to be closed for the past period and opened for the new (current) period. The operation usually is done at the end of the period or at the beginning of the period. The new partition data is generated when doing the closing operation. Reading data from one staging transaction data DSO into another BBB DSO is normally done. One of the options is given in Figure 14. All complicated logic is written as ABAP code in the start routines, update rules, and end routines.

Figure 14

A sample BBB data model

Step 8. Verify Data Loading for Period-End Closing and New-Period Opening with Sample Data

One of the challenges on the BBB back-end coding is to implement some restrictions based on the subtree values of a hierarchy, such as 0PROFIT_CTR. One business scenario is to treat all profit center IDs under the node XX SERVCE P&L with different methods in certain update rules.  

To implement it, create an internal table with all subtree values under the node XX SERVCE P&L in the start routine. Then declare the data definitions of the internal table by referencing CL_RSSH_HIERARCHY_BASE in the code as shown in Figure 15.

Figure 15

Subtree search ABAP code data variable declaration

The sample ABAP code to create internal subtree tables and steps to fill up the internal tables are as shown in Figure 16. You can accomplish the following tasks by adding the appropriate piece of code:

• Piece 1: Search conditions based on the internal tables
• Piece 2: Create a hierarchy object with a preselected ID
• Piece 3: Load entire hierarchy data
• Piece 4: Get a subtree under the defined mode value
• Piece 5: Load subtree values into an internal table

Figure 16

ABAP code for subtree search internal table build

You can see the load hierarchy and read subtree methods resulting from this code in the class CL_RSSH_HIERARCHY_BASE by using transaction SE24 (Figure 17). It is a useful class that you can use for other purposes to manipulate the hierarchies.

Figure 17

CL_RSSH_HIERARCHY_BASE class detail

You can generate more than one internal table containing different subtree node values in the start routine. Search conditions are based on those internal tables, and you can apply them in certain update rules as shown in Figure 18:

• Pieces 1 and 2: Search conditions based on the internal tables
• Piece 3: Operations based on the restrictions
• Piece 4: Search another condition

Figure 18

ABAP code for subtree search in an update rule

Step 9. Review the Sample BBB Query

A sample BBB query resulting from all these steps is shown in Figure 19. Note that the previous period’s ending backlog should match the next period’s beginning backlog.

Figur e19

Sample BBB report

Pinckney Ma

Pinckney Ma is an independent SAP NetWeaver Business Warehouse (SAP NetWeaver BW) consultant. Previously, he worked as a senior consultant at BearingPoint and Knightbridge. Pinckney is certified in SAP BW 3.5 and SAP NetWeaver BW 7.0. He earned his Ph.D in computer engineering and has 20 years of IT experience, including over 10 years in data warehouse and SAP NetWeaver BW implementation experience. He has worked on SAP NetWeaver BW implementations in many areas, as well as other SAP modules including FI, CO, profitability analysis (CO-PA), sales and distribution (SD/OTC), Advanced Planning & Optimization (APO), materials management (MM), SAP BusinessObjects Global Trade Services, and Flexible Real Estate Management (RE-FX).

You may contact the author at Pinckney.ma@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Peter Jones

Peter Jones is a business intelligence solution architect in the areas of CO, EC, SAP BW, BI, BusinessObjects, Planning and Consolidation (BPC), and SEM for MI6 Solutions. He is certified in all these areas and is a subject-material expert for SEM, CO, BI, and BPC, with over 14 years of experience in these areas working for SAP and MI6 Solutions. Currently, he is involved in projects incorporating SAP BW 7.4, HANA, BPC 10.0, and EPM 10.0. He has consulted in all those areas for the last nine years. Peter was an SAP instructor for seven years and has written several books about SAP NetWeaver BW, FI/CO, and has recently revised his book about BPC Implementation to version 10.1. He is a contributor to Best Practices for Financial Reporting in SAP, an exclusive anthology of articles that delivers unparalleled guidance on how to optimize financial reporting processes with SAP applications.

You may contact the author at peter.jones@mi6solutions.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.