- Highlight Selected Text
- Avoiding Query Macros
- Complex Crosstab Calculations
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Complex Crosstab Calculations
Sometimes, the easiest of report requests give report developers the hardest time. The solution is easy; however, it takes time to find the correct property or “discover” the correct button that does the trick.
The final result we are trying to achieve should look similar to Figure 1.15.
)
Figure 1.15 Completed crosstab percentage calculation example
Design
The order of calculations in more complex data containers, such as crosstabs and charts, can sometimes cause report developers to get undesired results when summarizing data. In this example, we will explore the default behavior of summarizations in crosstabs and discover the alternatives and seldom-used properties.
Step-by-Step
We will start with a simple crosstab template.
Step 1: Start the Report
- Launch Report Studio and select the GO Data Warehouse (analysis) package.
- Click on the Create new option.
- Select the Crosstab report template and click OK.
We will be using the Sales (analysis) namespace inside the Sales and Marketing (analysis) folder, same as we did for the previous examples in this chapter.
Step 2: Set Up the Crosstab
- From the Source tab of the Insertable Objects pane, drag the following levels into the Crosstab:
- Product Line from the Products dimension and Products hierarchy to the Rows drop zone
- Year from the Time dimension and Time hierarchy to the Columns drop zone
- Drag Revenue and Planned revenue from the Sales fact measures to the Columns drop zone and nest them under the Year data item.
- Ctrl-click the Revenue and Planned revenue column headings in the Crosstab.
- From the Data menu item, select Calculate and click on the %(Revenue, Planned revenue) option.
This will create a calculation item under each Year column and next to the Revenue and Planned revenue data items. The calculation will represent the percentage of planned revenue achieved. In addition, you will notice when you run the report that the formatting of the data will already be in percentage format.
- Right-click the newly created %(Revenue, Planned revenue) column title in the Crosstab and select the Show Text... option.
The Edit label dialog box opens.
- Change the text to % of Plan and click OK to close the dialog box.
- In the Crosstab, click on the Product Line row title to select it.
- From the toolbar, click on the Summarize button and select the Total option.
- From the Run menu, select Run Report – HTML to view the report. Your results should be similar to Figure 1.16.
Figure 1.16 Initial crosstab view
Notice how the Total line for the percentage calculations is actually adding the percentages. This is not the desired result. We are expecting to see the overall % of Plan for each year.
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
We will showcase two ways of correcting this problem in Steps 3a and 3b.
)
Step 3a: Fix the Crosstab Total Percentage Calculation
- Click on the % of Plan column heading to select it.
- In the Properties pane, change the Solve Order property from a blank value to 2.
By default, all the data item properties do not have this property set.
Solve Order property indicates which values will be calculated first in crosstabs and charts. The items with the lowest sort order values are calculated first; otherwise, the calculations on the detail rows are performed first, and then all the summaries.
The percentage calculation column should be calculated last, and since the summaries of the Revenue and Projected Revenue will already exist, the report server will use them to calculate the overall Year percentage.
- From the Run menu, select Run Report – HTML to view the report. The report results should look similar to Figure 1.17.
Figure 1.17 Final crosstab view
Notice how the Total line for the percentage calculations is now correct.
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
)
Step 3b: Try an Alternative Solution
There is actually an easier and quicker way to achieve the same result. Instead of task 8 in Step 2, do the following simple task:
- From the Toolbar, click on the Summarize button and select the Automatic Summary option.
- From the Run menu, select Run Report – HTML to view the report. The results should resemble Figure 1.18.
Figure 1.18 Final crosstab view
Notice how the Total line for the percentage calculations is also correct using this alternative suggested step.
- Close the IBM Cognos Viewer window to return to IBM Cognos Report Studio.
)
Sometimes the trick is just to find the correct property or a button. With the ever-changing list of features or options that are added to the new releases of IBM Cognos BI, you will keep discovering easier and better ways to solve the same report issues. Over time, you will notice that there are several ways to do the same thing, all correct, and it will come down to personal preference when you need to decide which approach to take.