Matrix Subtotals

I've just started using SSRS 2005 and am pretty impressed, however, I have
hit a stumbling block with Matrix sub totals, I have data displayed like so :
Client 1, 2006-01, £10000
Client 1, 2006-02, £15000
client2, 2006-01, £25000
client2, 2006-02, £10000
client2, 2006-03, £5000
(I have left out the pivoted data for simplicity - the above are all rows)
If I add a subtotal it totals all of the pivoted totals at the base of the
matrix but I actually want to subtotal on each client, for example, client1
would have a total of £25000 and Client2 a total of £40000. The subtotal is
grouped by creditorID ie Client1, Client2 so I'm not sure what I am missing.
Is it even possible to subtotal by each group? Any help appreciated
MarkHi Mark,
Thank you for your posting!
Based on my experience, you could do this. I assume you use the date field
as the row group. The easied way to do this is right-click the text of the
date field and click Subtotal. Then you could get the subtotal of date
field and grouped by the creditID field.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks a lot for that Wei, I'm getting very strange results - if I add a
subtotal to the CreditorID field it adds a grand total at the end of the
matrix, if I add it below the period / date field it gives a wildly
inaccurate figure. Could it be something to do with Scope - been searching
the net for Scope info but not much luck - I think I'll need to go back to
the drawing board on this one,
Can you recommend any good books or links which go into Matrix reports in
detail. Any further help greatfully received.
Mark
"Wei Lu [MSFT]" wrote:
> Hi Mark,
> Thank you for your posting!
> Based on my experience, you could do this. I assume you use the date field
> as the row group. The easied way to do this is right-click the text of the
> date field and click Subtotal. Then you could get the subtotal of date
> field and grouped by the creditID field.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Mark,
Thank you for the update.
As for the subtotal issue, please send an email to me and I will send the
sample rdl file to you.
As for the books for Matrix report, you could refer the SQL Books online
help:
Working with Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms157334(d=ide).aspx
My direct email address is weilu@.ONLINE.microsoft.com (please remove the
ONLINE when you send the email), you may send an email to me directly and I
will reply with the sample.
Please let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix Subtotal Format

Have several rows in a Matrix, every row has a different format (percentage,
Currently, Number). The Matrix has 3 groups (all groups not displayed below)
Average Shop1 Shop2 Shop3
Technicians 8.0 7 5 2
Efficiency 70.5% 60.0 75.2 70.1
Hours 1,000 500 2000 1000
I do an average for all columns display the average for that column on the
left side
In my development environment (Visual Studio) the average column formatting
works fine and takes the formatting of other columns in the row. After being
deployed to the reporting server and viewing through the report viewer the
formatting does not seem to work and seems to take the formatting of the
first group.
ThanksAfter hours of digging, the issue seems to be because of SP2.
http://forums.microsoft.com/MSDN/showpost.aspx?postid=2215374&siteid=1
"jeo78" wrote:
> Have several rows in a Matrix, every row has a different format (percentage,
> Currently, Number). The Matrix has 3 groups (all groups not displayed below)
> Average Shop1 Shop2 Shop3
> Technicians 8.0 7 5 2
> Efficiency 70.5% 60.0 75.2 70.1
> Hours 1,000 500 2000 1000
> I do an average for all columns display the average for that column on the
> left side
> In my development environment (Visual Studio) the average column formatting
> works fine and takes the formatting of other columns in the row. After being
> deployed to the reporting server and viewing through the report viewer the
> formatting does not seem to work and seems to take the formatting of the
> first group.
> Thankssql

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 !