Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: Chetan on 26 Aug 2013 05:59:51 AM

Title: Cross tab , Summarize value % of Column
Post by: Chetan on 26 Aug 2013 05:59:51 AM
Hi All

I am new to cognos Report studio, I am trying to prepare a Sales channel/Product/Month Level summary i.e % of Sales Coloum to total sales for a channel type using Repeater Table and Cross tab.
For sales % to total , i have created a data item using report defination fron query studio for same reuirement ,but whenever a am running the same , the % value are coming same all channel type

Can somebody help me.

Query in Data item: from Query studio ,added to data item
((total([PresentationLayer].[Premium].[ANP])) / total((total([PresentationLayer].[Premium].[ANP])) for report))/total(((total([PresentationLayer].[Premium].[ANP])) / total((total([PresentationLayer].[Premium].[ANP])) for report)) for ([PresentationLayer].[CHANNEL].[CHANNEL_TYPE].[SC_CHANNEL_FINAL]),([PresentationLayer].[DIM_DATE].[CALENDAR_YEAR].[CALENDAR_YEAR]),([PresentationLayer].[DIM_DATE].[CALENDAR_YEAR].[CALENDAR_MONTH_NAME].[CALENDAR_MONTH_NAME]))
Table values : Sample
Sales Channel 1                           
Product   April   June   May   August   July   February   January   March   Total
Product no 1   0%   0%   0%   0%   0%   0%   0%   0%   1%
Product no 2   0%   0%   0   0   0%   0   0   0   0%
Product no 3   7%   7%   8%   8%   6%   9%   10%   7%   62%
Product no 4   0%   0   0%   0   0%   0   0   0   0%
Product no 5   0%   0   0   0%   0%   0%   0   0%   0%
Product no 6   36%   22%   23%   13%   16%   22%   20%   25%   178%
Product no 54   100%   100%   100%   100%   100%   100%   100%   100%   871%
Product no 55   100%   100%   100%   100%   100%   100%   100%   100%   800%
                           
                           
Sales Channel 2                           
Product   April   June   May   August   July   February   January   March   Total
Product no 1   0%   0%   0%   0%   0%   0%   0%   0%   1%
Product no 2   0%   0%   0   0   0%   0   0   0   0%
Product no 3   7%   7%   8%   8%   6%   9%   10%   7%   62%
Product no 4   0%   0   0%   0   0%   0   0   0   0%
Product no 5   0%   0   0   0%   0%   0%   0   0%   0%
Product no 6   36%   22%   23%   13%   16%   22%   20%   25%   178%
Product no 54   100%   100%   100%   100%   100%   100%   100%   100%   871%
Product no 55   100%   100%   100%   100%   100%   100%   100%   100%   800%

Regards
Chetan
Title: Re: Cross tab , Summarize value % of Column
Post by: blom0344 on 26 Aug 2013 06:40:56 AM
Adding 'for Report' within calculation contexts will do exactly that. It will compute data over the entire report, not by taking into account the various channel types.
Title: Re: Cross tab , Summarize value % of Column
Post by: Chetan on 26 Aug 2013 06:53:02 AM
So is there any way out of this problem
Title: Re: Cross tab , Summarize value % of Column
Post by: blom0344 on 26 Aug 2013 09:11:41 AM
All percentages - for a given channel type - would add up to 100%?  Then for the denominator I would expect something like  total([ANP] for [Channeltype])
For the numerator I would expect total([ANP] for [Channeltype],[Product],[year],[monthname])
Text only | Text with Images