Hello,
How do I make Cognos BI calculate a data item on the lowest detail level before summing up the result?
Consider the following crosstab.
| Organisation | A | B | A*B |
| Total | 1.2 | 1000 | 1200 |
| CostCenter 1 | 0.4 | 300 | 120 |
| CostCenter 2 | 0.8 | 900 | 720 |
How do I get the A*B on a total level to sum the underlying values (120, 720), instead of calculating its own information? Im trying different combinations of Aggregate(), total() and writing the entire calculation within the A*B instead of picking up separate data items. But for some reason I can't get this to work.
This is probably a really simple thing. But what am I missing? It seems to me that this should be the standard behaviour.
Hope you can help a new guy out.
And the muppet comes to the rescue.....see if this post helps:
http://www.cognoise.com/index.php/topic,21762.msg66365.html#msg66365
Thank you.
I still couldn't get it to work even after trying all sorts of different combinations of solve order, aggregation types and totals. I think there is something wrong with the cube Im getting my data from.
But thanks for the help!
Regards
David
One thing to remember is the aggregate rules. For example, price*Qty looks fine in detail, but when summing up will do total(price) * total(qty), which is what you don't want.
Here are some things to experiment with. In your query, create a column to handle A*B, and set its aggregates to be Total. It looks like it's currently set to automatic or Calculated. If you don't want to try that, find the underlying query item that represents the total row. Try to change it to something like total(A*B). Wrapping it in a total() should tell Cognos to perform the calculation at the detail level, then sum it up to whatever level you're running your summary.