Matrix w/ Subtotals Export to Excel rrRenderingError

Is it possible to export a matrix report that has subtotals on multiple
columns to Excel? With just the innermost group subtotaled, I don't get an
exception, but when I add subtotals for outer groups it will not export.
I've seen this question posted elsewhere, but no answer to it. Thanks!Magpie,
I have same problem. Do you have any idea of solution?
Regards,
Loretta
"Magpie" wrote:
> Is it possible to export a matrix report that has subtotals on multiple
> columns to Excel? With just the innermost group subtotaled, I don't get an
> exception, but when I add subtotals for outer groups it will not export.
> I've seen this question posted elsewhere, but no answer to it. Thanks!

Matrix Subtotals/Cells

I've seen in a fex posts on this forum that it's at this moment not yet possible to create a dynamic matrix where for each cell i can calculate his row-percentage, his column-percentage. His total percentage can be done.

A way i could still do this is to create another matrix that only holds the totals, but how can i reference a cell-value of a matrix from another field ? (If this can be done).

KG.

Harry.

Another approch i'm trying is make some hardcoded fields for the total of each row and column. In this case it is possible as i know that there will always be 7 rows (1week) and 5 performances ...

When referencing a fields as =Sum(Fields("DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value)).Value, "SdwOlap") i'm getting it right.

But when i place tis formula in a cell of a matrix i'm getting in every column the grand total and not the total for that row, although, in the row below i entered the formula ="DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value), and there i'm nicely seeing DW_1 to DW_7 depending on the right row.

Strange behaviour that i can't explain .....

|||

In the grouping scope parameter of the aggregate, you are specifying a value, "SdwOlap". What is this refering to? Is this the name of the dataset or matrix? If it is, then this explains why the totals are actually grand totals and not just the totals for the row. The aggregate is aggregating the data of this field for the entire dataset. Try setting this to the scope of the row grouping, or omit it altogether.

Ian

sql

Matrix SubTotals Visibility

I am creating a report using a matrix, and matrix subtotals.
I give the user the option of including the reporting hierarchy in their
reports. If they want to include it, I would like to hide the subtotal,
completely.
The reason for this is because if I include a hierarchy within a single
field of a report, the subtotals don't sum properly.
Is there any way to hide the entire subtotal field within a matrix report?
I have tried modifying the Hidden field (using an expression) of the
SubTotal textbox, but that only seems to hide the textbox itself, not
the subtotal fields.
Thanks,
NoahNevermind. Figured it out using InScope().
Noah
Noah wrote:
> I am creating a report using a matrix, and matrix subtotals.
> I give the user the option of including the reporting hierarchy in their
> reports. If they want to include it, I would like to hide the subtotal,
> completely.
> The reason for this is because if I include a hierarchy within a single
> field of a report, the subtotals don't sum properly.
> Is there any way to hide the entire subtotal field within a matrix report?
> I have tried modifying the Hidden field (using an expression) of the
> SubTotal textbox, but that only seems to hide the textbox itself, not
> the subtotal fields.
> Thanks,
> Noah

Matrix subtotals only getting first value

Hi
I have created a matrix with subtotal on both the Row and Column. However, the total only seems to be considering the first value it encounters. Has anybody any ideas why this should be the case?
sample output:
Col1 Col2 Col3 Total
Row1 1.0 1.0 1.0
Row2 1.0 2.0 1.0
Total 1.0 1.0 1.0 1.0
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=eb765bbec307481280b9ce093a602227
*****************************************Solved it, my grouping was wrong.
Opened up another heap of problems, but that is another story ...
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
*****************************************|||How did you resolve? I'm having a similar issue with a matrix. I've one
group for the row, one group for the column and 4 entries for the data. I've
switched the row and column group, eliminated entries for the data and still
have the result of the 1st instance of return data for the subtotal.
Thanks!
Michelle
"Jan Bodey via SQLMonster.com" wrote:
> Solved it, my grouping was wrong.
> Opened up another heap of problems, but that is another story ...
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
> *****************************************
>

Matrix subtotals jump to subreport?

I have a complex issue. I have a matrix that has a cell that jumps the user to a subreport sending parameters. Well the report also has subtotals compliments of the matrix. Well those subtotals also jump to the subreport sending *first* parameters. I don't want the subtotals tojump anywhere! How do I disable navigation on the subtotal row? Crazy!

Have you found a solution to this? I am having the same problem.

Matrix Subtotals

I looked at RS a couple years back, around the release of SP1. The biggest issue I had was the lack of Aggregates of Aggregates. This still seems to be the case in RS 2005, is that correct?

So help me with a work around.

In a Matrix report how can I?

Have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In RS 2000 you could not have an Aggregate of an Aggregate, nor could you have a “compound scope”. Because of this I went back to that other software.

I would love a way to deal with this, I have many reports that use this functionality, and would like to move to RS. If I could use the Matrix, I could author reports in 10% of the time.

Ideas?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

sql

Matrix Subtotals

I looked at RS a couple years back, around the release of SP1. The biggest issue I had was the lack of Aggregates of Aggregates. This still seems to be the case in RS 2005, is that correct?

So help me with a work around.

In a Matrix report how can I?

Have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In RS 2000 you could not have an Aggregate of an Aggregate, nor could you have a “compound scope”. Because of this I went back to that other software.

I would love a way to deal with this, I have many reports that use this functionality, and would like to move to RS. If I could use the Matrix, I could author reports in 10% of the time.

Ideas?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?
|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

Matrix Subtotals

I looked at RS a couple years back, around the release of SP1. The biggest issue I had was the lack of Aggregates of Aggregates. This still seems to be the case in RS 2005, is that correct?

So help me with a work around.

In a Matrix report how can I?

Have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?

Q1 Q2 Total

Beverages

Lemonade

Sales $ 100 150 250

Percent of Bev 44%

Percent of Total 19% ?How do I do these Percentages in a Matrix?

Soda

Sales $ 125 125 250

Percent of Bev

Percent of Total

Total Beverages 225 275 500

Snacks

Peanuts

Sales $ 100 150 250

Percent of Snacks

Percent of Total

Cookies

Sales $ 200 250 450

Percent of Snacks

Percent of Total

Total Snacks 300 400 700

Total Sales 525 675 1300

In RS 2000 you could not have an Aggregate of an Aggregate, nor could you have a “compound scope”. Because of this I went back to that other software.

I would love a way to deal with this, I have many reports that use this functionality, and would like to move to RS. If I could use the Matrix, I could author reports in 10% of the time.

Ideas?

Unfortunately, this is still a limitation of the matrix. While we would like to have addressed it for SQL 2005, we did not have enough time. It is very high on the list of items for the post 2005 release.|||Thanks for your reply.
I did get a work around, thanks to Brian Larson.
Maybe in 2005 SP1?|||Can you please tell us how?|||My work around did not work as well as I had hoped. I Did get an E-mail from Brian Welker that the Aggregates of Aggregates issue is about 3 on the priority list. He said that they were currently working on the reporting engine. I did not get an ETA. Having this functionality will be a huge time saver for me over what I do in Crystal Today.|||

OK I can give a partial work around for this.First of all let’s assume that the column group name is “Quarter”.

Getting the 19% figure is easy, it is Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, “Quarter”)

Getting the 44% is easy but you have to hard code some things.

Create another field called “Beverages”.In the calculated expression put in something like: IIF(Fields!Group.Value = “Beverages”, Fields!Sales.Value, 0).You may need to do some conversion to Decimal or etc.

Then you can use this formula to get the 44%: Sum(Fields!Sales.Value) / Sum(Fields!Beverages.Value, “Quarter”)

This is the best I can come up with for a work around until Microsoft can get the Aggregates issue on the drawing board.(They will in the coming year I would expect)

Matrix SubTotals

I have subtotal at the bottom of the matrix. I am using Distinct Count in the expression field of matrix. But when I run the report, The Subtotal is giving the sum of all not the Distict counts. I can see the properties of Subtotal row. But I can not find the expression Property for that. Please help me anyone know it or had this issue.

I appreciate your help.

Thanks in advance

Rams

Hi ramsk

The subtotal expression in matrix report cannot be directley accesed.
You need to use custom code if you wish to manipulate the data in a different
way. This is because the expression used in the Matrix subtotal is the same as that
of the detail rows by default.

Custom code can be written using vb.NET or c# by going to report properties
and selecting the code tab. Write the function to accept integer values(or whichever data type you use)
and the call it from the detail cell within the matrix using the following expression:

=Code.<insert function name here without sharp bracets>(<field name here without sharp brackets>)

EX: =Code.GetFields(Fields!Net_Invoice.Value)

Now you can do a count on the values using vb/c#
Using this method even the most complex calculations can be done.

Gerhard Davids

|||

Thank you Gerhard Davids. I don't know vb.NET/C# coding. I will try to do that.

Thanks,

Ramsk

|||

Actually,

You can access the subtotal for Matrix report. Click on the green triangle of your matrix totals and you can write your expression there.

Hammer

|||

Hi Hammer,

I'm sorry to have to inform you that this is in fact
not possible. There has been much contravercy
about this and it has been causing many people
headaches including me.

Thus we have been using this very tedious and
redundant workaround.If you follow this link you
see in the newsgroup discussion that clearly
states: it's not possible at this time.

Also if you search around a bit you will get the
same result. Even with CTP2 this functionality
is not available.

Just thaught I'd let you know as not to get some
peoples hopes up.

Gerhard Davids

Matrix subtotals

I have a matrix where the columns show the number of attained grades for a
location along with a subtotal.
Location A B C Passes
London 2 4 5 11
Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
need to add an extra subtotal column to give me the total points.
Location A B C Passes TotPoints
London 2 4 5 11 30
Is this possible? The reference material suggests that the Total columns are
always based on the other columns. That means I'd have to display the total
points for each grade which the user's don't want.
It gets worse as they also want the average points per candidate.
Have I missed something? Is there a way to do this?
Thanks,
AndrewHello Andrew,
Unfortunately, you could not add another SubTotal Column in the Matrix.
My suggestion is you could add a Table just beside the matrix to show the
SubTotal.
Also, if the Grade column is fixed, you could use a Table instead of Matrix.
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 for the reply. A matrix would've been useful as the number of grades
is variable.
Sounds like an opportunity for extra functionality in the matrix :)
Andrew|||Hello Andrew,
You may submit your feedback to the produce team directly.
http://connect.microsoft.com/sql
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.|||On Jul 17, 6:22 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a matrix where the columns show the number of attained grades for a
> location along with a subtotal.
> Location A B C Passes
> London 2 4 5 11
> Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
> need to add an extra subtotal column to give me the total points.
> Location A B C Passes TotPoints
> London 2 4 5 11 30
> Is this possible? The reference material suggests that the Total columns are
> always based on the other columns. That means I'd have to display the total
> points for each grade which the user's don't want.
> It gets worse as they also want the average points per candidate.
> Have I missed something? Is there a way to do this?
> Thanks,
> Andrew
For some reason my answer was not submitted.
Press on the column title and right click. From the drop down menu
press "Subtotal", it will add a column that does totals to your
matrix. Hope that helps.|||Hello Ayman,
Thanks for your provide. Your suggestion will create the Subtotal column.
But Duke's issue is that he wants to add an additional column which not
only sum the detail values.
Currently reporting services did not provide such a function for Matrix.
Anyway, thank you for your efforts.
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.|||On Jul 17, 6:22 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a matrix where the columns show the number of attained grades for a
> location along with a subtotal.
> Location A B C Passes
> London 2 4 5 11
> Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
> need to add an extra subtotal column to give me the total points.
> Location A B C Passes TotPoints
> London 2 4 5 11 30
> Is this possible? The reference material suggests that the Total columns are
> always based on the other columns. That means I'd have to display the total
> points for each grade which the user's don't want.
> It gets worse as they also want the average points per candidate.
> Have I missed something? Is there a way to do this?
> Thanks,
> Andrew
Right click on the matrix column header and press "Subtotal" from the
drop down menu. That should work.

Matrix Subtotals

I have created a matrix and I am trying to add a percentage subtotal on the
following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
I added in a subtotal by right clicking on the column group for Male/Female.
Know I need to add and additional field that calculates the Total % .
M F Total Total %
Age FY LFY FY LFY FY LFY FY LFY
0 10 5 1 5 11 10 .33 .46
1-4 4 2 12 3 16 5 .47 .23
5-9 3 1 4 6 7 7 .21 .32
Total 17 8 17 14 34 22This is a multi-part message in MIME format.
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
You'll probably need to check for scope in your cell expression, to make =the right calculation based on "where" you are in your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", ="In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of =ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, use this:
Fields!Name.Value / First(Fields!Amount.Value, "MatrixColumnGroupName") =- assuming that your first row is a total.
If it's not, you might be able to use SUM if you add the Matrix column =group name =3D Fields!Name.Value / SUM(Fields!Amount.Value, ="MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" <Asim@.discussions.microsoft.com> wrote in message =news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
>I have created a matrix and I am trying to add a percentage subtotal on =the > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) =data. Then > I added in a subtotal by right clicking on the column group for =Male/Female. > Know I need to add and additional field that calculates the Total % .
> > M F Total =Total %
> Age FY LFY FY LFY FY LFY FY LFY
> 0 10 5 1 5 11 10 .33 = .46
> 1-4 4 2 12 3 16 5 .47 = .23
> 5-9 3 1 4 6 7 7 .21 = .32
> Total 17 8 17 14 34 22
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You'll probably need to check for scope in your cell =expression, to make the right calculation based on "where" you are in =your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), =iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In =Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, =use this:
Fields!Name.Value / First(Fields!Amount.Value, ="MatrixColumnGroupName") - assuming that your first row is a total.
If it's not, you might be able to use SUM if you add =the Matrix column group name =3D Fields!Name.Value / =SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" =wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...>I have created a matrix and =I am trying to add a percentage subtotal on the > following. I have this =Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then > I added in a =subtotal by right clicking on the column group for Male/Female. > Know =I need to add and additional field that calculates the Total % .> >  =; M F = Total Total %> Age &nbs=p; FY LFY FY LFY FY LFY FY =LFY> 0 = 10 5 =1 5 11 10 .33 =.46> 1-4 &nbs=p; 4 2 12 3 =16 5 =.47 .23> 5-9 &nbs=p; 3 1 =4 6 7 =7 .21 .32> Total &n=bsp; 17 8 =17 14 34 22

---

--=_NextPart_000_003D_01C61F7D.57B99130--|||Kaisa,
You seem to understand this InScope function so well and yet I take your
suggestion as you say "you have to check for scope in your cell expression".
The cell expression only *allows* one expression. In my detail cell I have
something like Sum(Fields!CriticalCount.value) and I can see how to put that
into the InScope but I want to do something else if I am in the subtotal and
I have questions about that. You are getting close to providing the answer
here when you say "then to calculate the percent in the right scope..." but
*where* would you put this calculation in the *one* cell expression that is
there? I have messed with this for a long time and almost got it working
with ONE column and ONE row ... but still couldnt figure out how to create a
subtotal expression different ( I tried using some thing like
SUM(ReportItems!tbCritCount.value) for the subtotal expression and of course
got errors on the aggregate and using ReportItems! ) So how can I reference
the values I want? lets say I want to check the max value in a column at the
subtotal level? OK ... if I cant do that ... then lets say I want to SUM the
values of an expression that I have in the detail cell of the column. I am
just not getting this and I am really really trying to ... :-(
"Kaisa M. Lindahl" wrote:
> You'll probably need to check for scope in your cell expression, to make the right calculation based on "where" you are in your matrix.
> Use the following guideline:
> =Iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
> Then, to calculate the percent, in the right scope, use this:
> Fields!Name.Value / First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
> first row is a total.
> If it's not, you might be able to use SUM if you add the Matrix column group
> name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
> Kaisa M. Lindahl
> "Asim" <Asim@.discussions.microsoft.com> wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
> >I have created a matrix and I am trying to add a percentage subtotal on the
> > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
> > I added in a subtotal by right clicking on the column group for Male/Female.
> > Know I need to add and additional field that calculates the Total % .
> >
> > M F Total Total %
> > Age FY LFY FY LFY FY LFY FY LFY
> > 0 10 5 1 5 11 10 .33 .46
> > 1-4 4 2 12 3 16 5 .47 .23
> > 5-9 3 1 4 6 7 7 .21 .32
> > Total 17 8 17 14 34 22sql

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 SubTotals

The data cell contains '=iif( Fields!dt.Value <
#11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
(row & column) to show an * whenever the row or column shows an *.
How do I get the subtotal to ignore the *'s and subtotal on just those cells
that contain a value.I discovered that the subtotals cell is using the Fields!dt.Value from the
first matrix column, and since the columns are sorted oldest dt to newest the
dt.Value is < the date indicated.
Can someone think of a way I can fix this other the changing the column order?
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.|||I changed my iif to =iif(last(Fields!dt.Value)... and this fixed my problem.
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.

Matrix subtotal on some (not all) columns?

Anyone know how to do this?
In my matrix, some of the columns are money, other are percentages.
I want to show subtotals for the money, but will need to manually calculate
(using expressions) the percentage totals.
I only seem to be able to switch subtotal on for the whole thing (right
click subtotal)
Is there a way of having a row underneath perhaps or changing how the
subtotal works?I ended up using a table not a matrix and adding a group
then in the table or group footer you can put a SUM function
which one you choose will depend upon the structure of your data
"adolf garlic" wrote:
> Anyone know how to do this?
> In my matrix, some of the columns are money, other are percentages.
> I want to show subtotals for the money, but will need to manually calculate
> (using expressions) the percentage totals.
> I only seem to be able to switch subtotal on for the whole thing (right
> click subtotal)
> Is there a way of having a row underneath perhaps or changing how the
> subtotal works?|||How do you selectively display columns using the subtotal: especially with
calculating percentages.
I also have this problem, i have been searching this newsgroup to find a
similar question with answers, but i guess no one knows the anwser to this
one...
I am loathe to use a table instead of a Matrix, especially since, i am going
to be displaying all the days in any particular month...
I'll just have to go the table route also.
Some one please help!!!!!
Bibo
"adolf garlic" wrote:
> I ended up using a table not a matrix and adding a group
> then in the table or group footer you can put a SUM function
> which one you choose will depend upon the structure of your data
> "adolf garlic" wrote:
> > Anyone know how to do this?
> >
> > In my matrix, some of the columns are money, other are percentages.
> > I want to show subtotals for the money, but will need to manually calculate
> > (using expressions) the percentage totals.
> >
> > I only seem to be able to switch subtotal on for the whole thing (right
> > click subtotal)
> > Is there a way of having a row underneath perhaps or changing how the
> > subtotal works?|||I have similiar reports, and sorted my issues with custom calculated
sub-totals by re-thinking my data sourcing, and adding a field called
part-total to the datasource which will be a percentage of the total for
that group.
so: a group with 4 members will have part-total = 0.25 for each field.
On the Report Field you use an expression that says iif(part-total=1
...... to decide whether the is the total columns.
Give me more detail on your question and I can give you a more descriptive
solution.
From experience I could comment that for complicated reports, its worth
re-thining your data-sourcing.
I can confidently say, It shouldnt be a problem.
Marcell
"bibo" <bibo@.discussions.microsoft.com> wrote in message
news:8DD0DABB-FCBD-4506-929E-255F8DA232D1@.microsoft.com...
> How do you selectively display columns using the subtotal: especially with
> calculating percentages.
> I also have this problem, i have been searching this newsgroup to find a
> similar question with answers, but i guess no one knows the anwser to this
> one...
> I am loathe to use a table instead of a Matrix, especially since, i am
> going
> to be displaying all the days in any particular month...
> I'll just have to go the table route also.
> Some one please help!!!!!
> Bibo
> "adolf garlic" wrote:
>> I ended up using a table not a matrix and adding a group
>> then in the table or group footer you can put a SUM function
>> which one you choose will depend upon the structure of your data
>> "adolf garlic" wrote:
>> > Anyone know how to do this?
>> >
>> > In my matrix, some of the columns are money, other are percentages.
>> > I want to show subtotals for the money, but will need to manually
>> > calculate
>> > (using expressions) the percentage totals.
>> >
>> > I only seem to be able to switch subtotal on for the whole thing (right
>> > click subtotal)
>> > Is there a way of having a row underneath perhaps or changing how the
>> > subtotal works?

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix SubTotal

Hi,
Brief Explanation:
I need 2 fields (say, A and B) in the Matrix Data Region with their SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for only A should be displayed but both for subtotals both (sum(A), sum(B)) should be displayed.
Detail Explanation, If required:
I am working with a matrix and require 2 fields to be shown in the Data Region against 1 row and 1 column groups. To display 2 fields in the Data Region, i kept a rectangle in the Data Region and placed 2 textboxes inside it with each displaying it's own respective field values.
Now when i add a subtotal to the report, it displays 2 rows for subtotals for each of the Data fields. So far so good.
My requirement is to suppress One of the Data Fields in the Data Region section and LET only it's the Subtotal get displayed. To achieve this, when i keep one of the textboxes hidden, the subtotal also gets hidden.
How can i achieve this ? Even a workwround suggested would be appreciated.
Crystal does this by allowing to access subtotals fields seperately than the data section fields which RS does not.
ThanksYou could conditionally hide the textbox based on the InScope function (e.g.
<Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
However, that won't collapse out the space occupied by the hidden textbox.
The size of the subtotal cell (in the current version) must be the same as
the size of a detail cell. If you're willing to let your subtotals be a
little cramped, you could do a variation of this with three textboxes. Two
small textboxes (one for A and one for B) which take up half of the
rectangle each and one large textbox (for just A) which takes up the entire
rectangle (overlapping with the other textboxes). You could then hide the
small textboxes when in the detail cell and hide the large textbox when in a
subtotal cell.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"push" <push@.discussions.microsoft.com> wrote in message
news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> Hi,
> Brief Explanation:
> I need 2 fields (say, A and B) in the Matrix Data Region with their
SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
only A should be displayed but both for subtotals both (sum(A), sum(B))
should be displayed.
> Detail Explanation, If required:
> I am working with a matrix and require 2 fields to be shown in the Data
Region against 1 row and 1 column groups. To display 2 fields in the Data
Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
it with each displaying it's own respective field values.
> Now when i add a subtotal to the report, it displays 2 rows for subtotals
for each of the Data fields. So far so good.
> My requirement is to suppress One of the Data Fields in the Data Region
section and LET only it's the Subtotal get displayed. To achieve this, when
i keep one of the textboxes hidden, the subtotal also gets hidden.
> How can i achieve this ? Even a workwround suggested would be appreciated.
> Crystal does this by allowing to access subtotals fields seperately than
the data section fields which RS does not.
> Thanks
>|||This worked in the preview. Thanks. Now there is another issue. When i give a border for the Subtotals fields (A, B) (from Right Clink of that green corner), while previewing it says "Object Reference not set to an instance of an Object".
How can i place a border on the subtotals as a whole (i.e. for both A and B) ? Also, if, possible, how can i conditionally place the border for only one of the subtotals (i.e. only for subtotal A)?
Thanks,
"Chris Hays [MSFT]" wrote:
> You could conditionally hide the textbox based on the InScope function (e.g.
> <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> However, that won't collapse out the space occupied by the hidden textbox.
> The size of the subtotal cell (in the current version) must be the same as
> the size of a detail cell. If you're willing to let your subtotals be a
> little cramped, you could do a variation of this with three textboxes. Two
> small textboxes (one for A and one for B) which take up half of the
> rectangle each and one large textbox (for just A) which takes up the entire
> rectangle (overlapping with the other textboxes). You could then hide the
> small textboxes when in the detail cell and hide the large textbox when in a
> subtotal cell.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "push" <push@.discussions.microsoft.com> wrote in message
> news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > Hi,
> >
> > Brief Explanation:
> > I need 2 fields (say, A and B) in the Matrix Data Region with their
> SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
> only A should be displayed but both for subtotals both (sum(A), sum(B))
> should be displayed.
> >
> > Detail Explanation, If required:
> >
> > I am working with a matrix and require 2 fields to be shown in the Data
> Region against 1 row and 1 column groups. To display 2 fields in the Data
> Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
> it with each displaying it's own respective field values.
> > Now when i add a subtotal to the report, it displays 2 rows for subtotals
> for each of the Data fields. So far so good.
> > My requirement is to suppress One of the Data Fields in the Data Region
> section and LET only it's the Subtotal get displayed. To achieve this, when
> i keep one of the textboxes hidden, the subtotal also gets hidden.
> > How can i achieve this ? Even a workwround suggested would be appreciated.
> > Crystal does this by allowing to access subtotals fields seperately than
> the data section fields which RS does not.
> >
> > Thanks
> >
>
>|||You're setting the border properties the right way. I don't know why you're
getting that error. (I tried the same thing here and it works, so I'd need
your report and the associated data file to try to reproduce the problem
here). A workaround in the mean time: Just set the borders on one or both
of the small textboxes directly. Since they'll be hidden unless you're in a
subtotal cell, you don't actually need to use the subtotal-specific style
properties (the green-triangle ones).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"push" <push@.discussions.microsoft.com> wrote in message
news:027F0F5F-1971-4DEF-B118-7615AF3C988B@.microsoft.com...
> This worked in the preview. Thanks. Now there is another issue. When i
give a border for the Subtotals fields (A, B) (from Right Clink of that
green corner), while previewing it says "Object Reference not set to an
instance of an Object".
> How can i place a border on the subtotals as a whole (i.e. for both A and
B) ? Also, if, possible, how can i conditionally place the border for only
one of the subtotals (i.e. only for subtotal A)?
> Thanks,
>
> "Chris Hays [MSFT]" wrote:
> > You could conditionally hide the textbox based on the InScope function
(e.g.
> > <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> > However, that won't collapse out the space occupied by the hidden
textbox.
> > The size of the subtotal cell (in the current version) must be the same
as
> > the size of a detail cell. If you're willing to let your subtotals be a
> > little cramped, you could do a variation of this with three textboxes.
Two
> > small textboxes (one for A and one for B) which take up half of the
> > rectangle each and one large textbox (for just A) which takes up the
entire
> > rectangle (overlapping with the other textboxes). You could then hide
the
> > small textboxes when in the detail cell and hide the large textbox when
in a
> > subtotal cell.
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > > Hi,
> > >
> > > Brief Explanation:
> > > I need 2 fields (say, A and B) in the Matrix Data Region with their
> > SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values
for
> > only A should be displayed but both for subtotals both (sum(A), sum(B))
> > should be displayed.
> > >
> > > Detail Explanation, If required:
> > >
> > > I am working with a matrix and require 2 fields to be shown in the
Data
> > Region against 1 row and 1 column groups. To display 2 fields in the
Data
> > Region, i kept a rectangle in the Data Region and placed 2 textboxes
inside
> > it with each displaying it's own respective field values.
> > > Now when i add a subtotal to the report, it displays 2 rows for
subtotals
> > for each of the Data fields. So far so good.
> > > My requirement is to suppress One of the Data Fields in the Data
Region
> > section and LET only it's the Subtotal get displayed. To achieve this,
when
> > i keep one of the textboxes hidden, the subtotal also gets hidden.
> > > How can i achieve this ? Even a workwround suggested would be
appreciated.
> > > Crystal does this by allowing to access subtotals fields seperately
than
> > the data section fields which RS does not.
> > >
> > > Thanks
> > >
> >
> >
> >

Matrix Sub Total

Hi Experts...
I am using a matrix report. I am showing the subtotals of one of the columns
and it is comming correctly on the Right hand side of the column. My problem
is that I want the sub total to appear on the Left hand side of the selected
column insted of the Right side.
Is there any quick way of achieving this? Can anybody give a suggestion for
this?
Thanks
GaneshHi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voil=E1. :)
Regards, Paulo Cunha|||thanks Paulo for the reply..it was really helpful
"Paulo X" <pjmcunha@.gmail.com> wrote in message
news:1141646924.794781.114380@.j33g2000cwa.googlegroups.com...
Hi, Ganesh.
On the upper right corner of the subtotal cell, you should see a green
triangle.
Right-click over that triangle and select "Properties" (it should be
the only option enabled).
Then look for a property named "Position" set to the value "After".
You can switch it to "Before" and voilá. :)
Regards,
Paulo Cunhasql

Matrix Sorting

I want to sort my matrix by the subtotals. Is this possible? Does anyone
know how to do this?
ThanksI figured out this solution by using the InScope function in my Row Group
Sort feature
"Web Developer DM" wrote:
> I want to sort my matrix by the subtotals. Is this possible? Does anyone
> know how to do this?
> Thanks

matrix row & column subtotals

The "Adding Subtotals" section in MSDN has me totally confused, I'm not
seeing a Subtotal menu. I need to create a simple cross tab table but
I'm not having any luck.
_____________________________________________
| "TITLE" | Column Name | "TOTAL" |
_____________________________________________
| Row Name | data | row total |
_____________________________________________
| "TOTAL" | column total | matrix total |
_____________________________________________Right click the Column Name cell and choose subtotals, to get column totals.
Right click the Row Name cell and choose subtotals, to get row totals.
"Harolds" wrote:
> Right click the Column Name cell and choose subtotals.
> "tomk10" wrote:
> > The "Adding Subtotals" section in MSDN has me totally confused, I'm not
> > seeing a Subtotal menu. I need to create a simple cross tab table but
> > I'm not having any luck.
> >
> > _____________________________________________
> > | "TITLE" | Column Name | "TOTAL" |
> > _____________________________________________
> > | Row Name | data | row total |
> > _____________________________________________
> > | "TOTAL" | column total | matrix total |
> > _____________________________________________
> >
> >|||Right click the Column Name cell and choose subtotals.
"tomk10" wrote:
> The "Adding Subtotals" section in MSDN has me totally confused, I'm not
> seeing a Subtotal menu. I need to create a simple cross tab table but
> I'm not having any luck.
> _____________________________________________
> | "TITLE" | Column Name | "TOTAL" |
> _____________________________________________
> | Row Name | data | row total |
> _____________________________________________
> | "TOTAL" | column total | matrix total |
> _____________________________________________
>sql