Matrix Counting and Totals

I have a report in matrix format. The weeks are across the top and the
calculated field is in the rows. I need to be able to count accounts that
have ordered for that week which I was able to do. I also need to count if
this is the first time the customer has ordered "ever" and if they are
re-ordering. A re-order is considered if they have ordered twice. Does
anyone have any suggestions on the best way to do this?Are you getting your data from SQL Server? If possible, I'd do the counting
in the query itself, not the report. It's a lot easier to do counts in SQL
than in a matrix.
If you do want to count in your report, you could use the InScope function,
and check if you are inscope for the account group.
A bit of pseudocode to show what I'm thinking
=IIF(InScope("AccountGroup"), runningvalue(fields!Order.Value), 0)
(Please verify syntax...)
Kaisa M. Lindahl Lervik
"Debbie Nelson" <Debbie Nelson@.discussions.microsoft.com> wrote in message
news:0CC9D925-B21D-4E0D-A59C-C1B9821F59FC@.microsoft.com...
>I have a report in matrix format. The weeks are across the top and the
> calculated field is in the rows. I need to be able to count accounts that
> have ordered for that week which I was able to do. I also need to count
> if
> this is the first time the customer has ordered "ever" and if they are
> re-ordering. A re-order is considered if they have ordered twice. Does
> anyone have any suggestions on the best way to do this?

Matrix - Problem with Rounding in Subtotal Column

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:

Client Formatted Data Unformatted Data A 1.6 1.578947368 B 0.8 0.789473684 C 0.8 0.789473684 D -0.8 -0.789473684 E 1.6 1.578947368 Total 3.9 3.947368421

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 !

Matrix - Problem with Rounding in Subtotal Column

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:

Client Formatted Data Unformatted Data A 1.6 1.578947368 B 0.8 0.789473684 C 0.8 0.789473684 D -0.8 -0.789473684 E 1.6 1.578947368 Total 3.9 3.947368421

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 !