Creating your own calculations In SAP BO
So far, we learned about the basic functionality of SAP BusinessObjects Analysis, edition for OLAP and we learned already some of the advanced capabilities. In this section, we will take a look, on how we can create our own custom calculations as part of our workspace.
Let us start with the very basic workspace showing all the key figures in the columns and the characteristics Country and Region in the Rows.
- Open the BI Launchpad via Start > All Programs > SAP BusinessObjects BI Platform 4.0 > SAP BusinessObjects BI Platform > SAP BusinessObjects BI Platform Java BI Launchpad.
- Logon with your SAP credentials and the SAP authentication for your SAP NetWeaver BW system.
- Select the menu Applications.
- Select Analysis, Edition for OLAP. In the next step, you are presented with the list of available OLAP Connections.
- Select the connection we created previously pointing to your SAP NetWeaver BW system.
- Click Next. You are now receiving the list of available BEx queries. You can also use the tab Find to search for specific queries.
- Select the BEx Query we created previously and click OK.
- Remove all characteristics from the Rows.
- Add characteristic Country to the Rows.
- Add characteristic Region to the Rows.
- Use a right click on the Key Figures in the Column area.
- Select the menu Filter by Member (see Figure 5.21).
Figure 5.21 Menu Filter by Member
13. Ensure that the key figures Net Value, Product Costs, and Transport Costs are selected.
14. Click OK (see Figure 5.22).
Figure 5.22 Analysis Workspace
There are multiple ways to add calculations:
You can select those key figures that are part of the calculation in the cross tab and then use the Calculations menu from the tab Analyze.
You can select a single key figure and then use the option to create a dynamic calculation using the menu Calculations from the tab Analyze.
You can use the option to create a custom calculation available in the menu Calculations on the tab Analyze.
In the next few steps, we will use our cross tab, that we just created, to explore these three options.
15. In the cross tab, select the column header for the key figure Transport Costs.
16. Press (and keep pressed) the CTRL button on the keyboard.
17. In the cross tab, select the column header for the key figure Product Costs (see Figure 5.23).
Figure 5.23 Creating Calculations
18. Navigate to the tab Analyze.
19.Select the menu Calculations > Add (see Figure 5.24).
Figure 5.24 Adding Calculations
Based on the order of selecting the key figures, SAP BusinessObjects Analysis, edition for OLAP creates a new column with the calculation: Transport Costs + Product Costs (see Figure 5.25).
Figure 5.25 Workspace with calculation
You can now change or delete the calculation using the menu Calculations on the tab Analyze or using the symbol in the column header for the new calculation.
20. Navigate to the tab Analyze.
21. Select the menu Calculations.
22. Navigate to the menu Calculations > Transport Costs + Product Costs.
23. Select the option Edit (see Figure 5.26).
Figure 5.26 Edit Calculations
24. The calculation is shown on the left hand side and you can now change and customize the new column (see Figure 5.27).
Figure 5.27 Calculation Details
25. Enter “Total Costs” for the Name.
26. For the option Place After use the symbol and place the calculation after Net Value.
27. Click Validate. The Status will display if your calculation is valid or not.
28. Click OK.
29. Use a right click on the Key Figures in the Columns area.
30. Select the menu Filter by Member.
31. Remove the key figures Transport Costs and Product Costs from the cross tab.
32. Click OK.
33. In the cross tab, select the column header for the key figure Total Costs.
34. Press (and keep pressed) the CTRL button on the keyboard.
35. In the cross tab, select the column header for the key figure Net Value.
36. Navigate to the tab Analyze.
37. Select the menu Calculations > Percentage Share.
Based on the order, we used to select the columns, the newly added calculation will show the percentage share of the Total Costs in relation to the Net Value.
38. In the cross tab, select the column header for the key figure Net Value.
39. Navigate to the tab Analyze.
40. Select the menu Calculations > Dynamic Calculation (see Figure 5.28).
Figure 5.28 Dynamic Calculations
Because now we only selected a single key figure, the option available are different and we cannot use the standard calculation such as Add or Percentage share, but we can still leverage the Dynamic Calculation.
41. Use the option Rank Number (see Figure 5.29).
Figure 5.29 Workspace with Dynamic Calculation
A new key figure displaying the Rank of the key figure Net Value is being added to your cross tab. You can use the Dynamic Calculation option to add columns such as a Rank or an Average to your cross tab.
42. Navigate to the tab Analyze.
43. Select the menu Calculations > Custom Calculation.
Figure 5.30 Calculation Details
Using the custom calculation option (see Figure 5.30), you can create a calculation using a large list of formulas.
In this section, we reviewed the different options to add a calculation to your cross tab. In the next section, we will use BEx variables in our workspace with SAP BusinessObjects Analysis, edition for OLAP.