I have a report where I show values of this YTD, previous YTD, current month and same month previous year. To show those values together I do a series of joins. First I join the 2 YTD values, I do the same with the 2 month values and then I join those 2 joins again. The context are, among others, 4 levels in a hierarchy (it's financial). I use this beasty to make 3 lists that have new queries based on this multi join with an extra filter. In the various lists I use different hierarchy levels next to the numbers.
The list that has the lowest level hierarchy is acting normally. Those that use higher levels as context for the numbers do weird things. Either they show only 1 of the values they have to total, or if I explicitely tell the query to total, the amounts are way too high. It looks like it's either summarizing, or that it's doing some carthesian product. Another possibility is that Cognos shows me multiple rows of the same level combinations with different numbers, which it has to total in one row.
This part of how Cognos handles aggregations and totals in more complex settings baffle me completely. Will somebody tell me what I keep missing and which settings I have to use to get the correct result?
This is dimensional rather than relational, right?
For relational, I've learned: As long as your rolling up into totals (and multiple levels of that), only mess with the aggregate functions in those queries and for those data itmes where you are actually calculating / grabbing from data source, and only do so if you do not use manual aggregate functions (*inside* the data item definitions). Everywhere else, specifically in joined queries or query references, leave aggregate functions at "Automatic". I have no clue if this observation is also applicable to olap reporting, but it may be worth a try.
This is relational, not dimensional. So I will reread your tip a few times to fully understand it and then look where it's messing it up regardless of me having things mostly at Automatic. Thanks!
I think I found the source of the high numbers. I had some extra grouping level that was not really used in the lists, but was lurking in the query and in the grouping and sorting. And that can apparently cause extra totalling... I found out when they asked me to do multi select on a prompt, which was this grouping level.