Hi,
I am a very new Cognos user.
I have a pie chart based on top 10 best customers report.
I have both 10 top total quantity and all customers total quantity on the report.
The pie chart is based on the top 10 summary and uses
"topCount ([All Customers (base)],10,tuple([Sales Quantity],[YTD]))"
Is it possible to use total (all customers) report quantity for % calculations?
Thank you!
Hello again,
The problem was resolved in part by adding calculated Total for all customers. However, now drill down % is calculated incorrectly.
I need help to define total for customer that is being drilled down.
Anybody?
Use Aggregate instead of Total
Thanks!
I am very new at this - I can't figure out the expression.
I have list of 10 customers where aggregate is used to calculate total of top 10. Then when one customer gets drilled down, I don't know how to use aggregate for this customer.
You said you used 'Total' , not 'Agrregate' for all customers.
Try to understand it in technical terms, not by the language. By the language, aggregate and Total might mean the same thing but in Cognos, they are different.
You don't have to do anything at the lower level of drill down.
Delete the item that is doing the Total and create it again, this time choose 'Aggregate' from the menu instead of 'Total'.
I know I'm not doing a good job explaining:)
The report itself is not a problem, is has defined aggregate functions. It's the pie chart. It is not calculated % the way we need it. By defining query item "total-for-all-customers", we got pie chart %% calculated properly, except drill downs - because the total(aggregate) is for this one customer, not for all, but % is still calculated based on total-for-all-customers.
Cust1 100
Cust2 200
.....
Cust10 50
Total of 10 - 1000
Total of All - 5000
The % on the pie chart is calculated as % of 5000.
For Cust1 it is 2%
On the drill down, there are children of Cust 1
Cust1_1 20
Cust1_2 80
Total 100
Pie chart has it as 2%
Cust1_1 0.4%
Cust1_2 1.6%
I need it to be 20% and 80%
Thank you for your help.
Instead of creating a data item to calculate percentage, use the Quantity as the measure in the Pie Chart. There is a property called 'Value Representation' where you can specify to show values as %. That does it pretty well , for drill downs too because it calculates % AFTER drill down. I think this is what you are trying to achieve ? This property is a property of Pie, not of the measure so click anywhere on the Pie and you
will see this property on the bottom left.
By the way, I am not sure if it will calculate based on 5000 instead of 1000 (total of top 10). Just try it.