Totals for an NONE aggregate function not showing right

[Amber12]

Hello. I'm very new to Cognos - Report studio and I still have lots to learn. For now I'm trying to convert some reports from Impromptu to Cognos Report Studio and my totals are not right. I have a field, Actual Values - Raw Materials that has None under Aggregate Function, in properties. I get the same numbers like in the old reports. But when I run the total, the numbers are not showing correct. I don't know what else to do.

Can anybody please help me?

Thank you   

[bdbits]

Your totals are multiples of the values in the rows.

What expression are you using for the total?

[Amber12]

I just selected the total option after I grouped (the sum). Indeed, it totals the amount as it would be for each line. I only need one amount for the whole MO and that amount to total in the overall footer. But I don't know how to do this.

[Corrigon]

Hi - a potentially quick solution would be to use the 'Maximum' aggregate option instead of Total. This will take the highest number of the row values. In your example, the incorrect Totals indicate that they are all replications of the same value, so this should give you the correct result. This might not cater for all results though - you would need to check the full result set.

Alternatively, you can unlock the page formatting (it is the small padlock icon in the tool bar in RS). This will allow you to click on the 'Raw Matl$' Total and cut it from the page while still leaving the total row itself intact (highlight just the text, NOT the whole node). Then, drag in a data item into the now-empty node, and use an expression similar to the following:

Code Select Expand

total(distinct [Raw Matl$] for [MO#]). You can leave the Aggregate Property as Automatic.

This should result in Cognos calculating the Total based only one instance of each value, thereby bypassing the Replication issue you have been experiencing.

Good luck!

[Amber12]

Thank you VERY much.

What I did I used the maximum for Totals per MO and the Distinct function for overall total and it worked really well.

Thank you SO, SO much!