Hi,
I have a matrix report that calculates data and provides a subtotal at the end. The calculated value is displayed to one decimal place.
My problem is that when the subtotal appears, it appears to one decimal place also, and although it is the correct value, it is not actually a sum of the values above it.
Here's what I mean:
The formatted data row is what I have on my matrix report - the Users are looking at this and complaining that the Total row = 3.9, but the sum of the values above it is 4.0. If you look at the raw data I have included (this is not shown on the report), 3.9 is actually the correct value, but you can understand where they are coming from when they can't see this.
It seems that the Subtotal is aggregated before any formatting applies, so its not actually a subtotal of the visible data in the cells in its group, but a subtotal of the raw data in the cells in its group.
Does anyone know how to solve this?
Thanks !
hi
if u used any formating for the fields like format(a,0),format(b,0) the same you need to use when u calculate total like format(a,0)+format(b,0) etc .i hope if u use this you will get the correct total.
|||I think your problem is that you're doing the formatting after Aggregation.. for example you're probably using something like Format(Sum(<FieldName>)). Your problem in that case will be resolved if you do it before Aggregation that is, something like Sum(Format(<FieldName>))
-Aayush
|||If I use Sum(Format(<FieldName>)) I get an error saying that "the expression uses an aggregate function on data that is not numeric", even if I format it as a number.|||That is because the Format functions convert data to String type. So you can probably use a conversion function after Formatting, that is something like
Sum(CDbl(Format(<FieldName>))). I know its a very crude kind of fix but I guess it doesn't matter as long as it works. There might be a better way but at least I'm not aware of one.
-Aayush
|||This worked, thanks !