Question & Answer
Question
This article and sample screenshot illustrate how to use a Calculated aggregation function and Solve Order to accomplish the task of showing percentage of totals on the aggregation line in a crosstab report .
Answer
To show the percentage of (Total of Gross profit) / (Total of revenue) on the total line in a crosstab report, the aggregate type needs to be calculated. In addition, the solve order needs to be changed if the calculation is based on the results of other calculations.
Note: Do not use the Report Studio Percentage() function for the calculation. Rather, create a calculation like:
- total(([Gross Profit] for [Order Number], [Order Year]) / total([Gross Profit] for [Order Number]))
Steps:
- In Report Studio, create a new data item Percentage, and change its aggregate function type in Properties pane to Calculated
- Insert data item Percentage as a column to the report, click column title Percentage, go to the Properties pane, and check its solve order (i.e. change its solve order to 2 if the solve order for the total aggregation is 1 to base this calculation on the results of the total.
[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1;8.4.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SUNSET","label":"PRODUCT REMOVED"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21367569