Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: Manu0521 on 24 Mar 2014 12:22:55 PM

Title: Show value as % or row total -Dimensional Data
Post by: Manu0521 on 24 Mar 2014 12:22:55 PM
Hi,

This is what I have in my cross tab and required to show % of row total on customer with respect to the products.
Sales                     Product1   Product2               Total
                    A   B   C     D   
Customer1    100   200   300   400   1000
Customer2   500   100   450   345   1395
Customer3    100   250   500   120   970


Required :
Sales                       Product1   Product2                Total
                   A   B   C   D   
Customer1    10%   20%   30%   40%   100%
Customer2   36%   7%   32%   25%   100%
Customer3    10%   26%   52%   12%   100%


I am using customers as a member set and product and product line are seperate member sets from same dimension levels.

How can I achieve this. I have to divide the customer products total by overall total , how this should be done.

Thanks,
Title: Re: Show value as % or row total -Dimensional Data
Post by: Manu0521 on 25 Mar 2014 08:34:50 AM
Any help on this, I am stuck and not able to proceed.

I should choose a data Item and add an expression as [Sales]/Total(Product)
Since product is a level it shows an error the value chosen should be a value.

This percentage calculated should be for each customer. So should i add something like within set Customer.

Thanks,
Title: Re: Show value as % or row total -Dimensional Data
Post by: CognosPaul on 25 Mar 2014 08:59:27 AM
There are a few ways to get the percentage.

Replace the default measure in your crosstab with one of the following, and see what happens:

1. percentage([Sales] within set [Product])
2. [Sales]/total([Sales] within set [Product])
3. [Sales]/tuple([Sales],[Product All Member])

Title: Re: Show value as % or row total -Dimensional Data
Post by: Manu0521 on 25 Mar 2014 09:15:51 AM
I used this one and it works [Full Revenue]/total([Full Revenue] within set [Level 4])
Thanks Paul for your reply .
But for tuple I have to give the the member right, but I have the member set and its dynamic right. so how would that work

Thanks
Title: Re: Show value as % or row total -Dimensional Data
Post by: CognosPaul on 27 Mar 2014 02:24:06 AM
A tuple does need a member to work, which is why I suggested several different solutions. Different needs call for different solutions. If you always needed to show the ratio against everything, even if the set was dynamic, then you would want to use the tuple against the all member.
Title: Re: Show value as % or row total -Dimensional Data
Post by: Manu0521 on 28 Mar 2014 10:50:43 AM
Sales                 A   B   C   D    P1(A+B+C+D)   A1   B1   C1   D1   P2(A1+B1+C1+D1)
Customer1   25   25   25   25   100                  50   50   50   50   200
Customer2   25   25   25   25   100                  50   50   50   50   200
Customer3   25   25   25   25   100                  50   50   50   50   200
                              
                              
I wanted to show % of total for each product                              
Iam using a calculated measure and my expression would be Sales/total(Sales with in set)                              
Here I have two sets one for product 1 -P1 and other for product2 -P2.                              
How will my expression be.                              
I have children(p1) as a mener set and children(p2) as amember set in my query

Thanks,
                              
Title: Re: Show value as % or row total -Dimensional Data
Post by: Manu0521 on 28 Mar 2014 12:32:20 PM
In my columns I have different member sets from same hierarchy . So If I have to calulate the percentage total for each members set for the specific customers .How can I achieve this.

Thanks,
Text only | Text with Images