Matrix / Table Combo

Hello,
I have an request that is throwing me for a loop. The user is asking
for a report that provides purchasing amounts by months for the last
15 months, then asking for summary stats from different tables, i.e
sales, cogs and inventory. Normally I would use a matrix report to
present the 15 months of data. I could use sub queries to retrieve
all
the columns that are needed, but I was hoping there was a simpler
solution. Any thoughts or suggestions would be greatly appreciated.
R/ AnthonyOn Jan 29, 4:14 pm, awakin <a...@.ibtinc.com> wrote:
> Hello,
> I have an request that is throwing me for a loop. The user is asking
> for a report that provides purchasing amounts by months for the last
> 15 months, then asking for summary stats from different tables, i.e
> sales, cogs and inventory. Normally I would use a matrix report to
> present the 15 months of data. I could use sub queries to retrieve
> all
> the columns that are needed, but I was hoping there was a simpler
> solution. Any thoughts or suggestions would be greatly appreciated.
> R/ Anthony
As you eluded to, you could use a nested table in a matrix control.
One alternative would be to nest a subreport in the matrix control and
pass parameters, where necessary, from the matrix control to the
subreport. Also, you could use Jump to Report in the navigation
properties of a cell in the matrix control to jump to another report
and pass parameters where necessary. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

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 !