Design a report that gives the percentage of row wise totals with respect to grand total in cross tab with DMR Model
Resolving The Problem
Below are the steps to create the report
1. Open a new Crosstab report in a DMR Package. We have used package: GO Sales (analysis) for below steps.
2. Drag following levels in to rows(these levels are from different dimensions)
a) [Sales target (analysis)].[Products].[Products].[Product line]
b) [Sales target (analysis)].[Retailer type].[Retailer (by type)].[Retailer type]
c) [Sales target (analysis)].[Sales staff].[Sales staff].[Sales region]
3.Drag a level: [Sales target (analysis)].[Time].[Time].[Year] in to columns
4. Drag a measure: [Sales target] in to columns
Now the crosstab should look as below:
5. Now click on Fact cells <#1234#> and select Total from Summarize as shown below.
6. The Report will look as below after applying Total
7. When you run the report, below will be output.
As per the requirement to get the row wise totals Percentage with respect to the grand total, we should get the denominator value, which is $328,721,900.00
8. We use below expression to get the Grand Total.
total([Sales target] within set [Product line],[Sales region],[Retailer type])
9. Drag a Query Calculation from toolbox in to columns and add Expression definition : [Total(Year)]/total([Sales target] within set [Product line],[Sales region],[Retailer type])*100
Below is the screen shot of the same.
10. Below is the screenshot of the report design after adding the data item and renaming it to "% of row total in Grand Total".
11. Now, change the data Format of the Data Item :"% of row total in Grand Total" from default to Percent.
12. Run the report to get the desired output as highlighted below:
Attached the report specification of Cognos 10.2.1 using Go Sales (analysis) package.
15 June 2018