Matrix with totals at the top and a empty row

Hi,
I would like to have the following,
field 1 field 2 field 3
---
empty row
total 1000 1000 1000
empty row
field 4 2000 2000 2000
field 5 8000 8000 8000
Any advice on how to do that?Set Subtotal position to "Before" and use top and bottom padding to add
space around subtotal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to have the following,
> field 1 field 2 field 3
> ---
> empty row
> total 1000 1000 1000
> empty row
> field 4 2000 2000 2000
> field 5 8000 8000 8000
> Any advice on how to do that?
>|||Thanks this works.
Now just one more problem. If we use the example below, on the column
grouping(Contains Months) have an iif to check if the date is before a
parameter passed to the report. I then use either one or the other field
from the database depending on whether it is true or false.
When I then add the total it would give me the wrong value, not really sure
which value it is giving me, but it always one value from the column and not
a calculated value.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Set Subtotal position to "Before" and use top and bottom padding to add
> space around subtotal
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Reg" <reg@.dsl.za.org> wrote in message
> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I would like to have the following,
> >
> > field 1 field 2 field 3
> > ---
> > empty row
> > total 1000 1000 1000
> > empty row
> > field 4 2000 2000 2000
> > field 5 8000 8000 8000
> >
> > Any advice on how to do that?
> >
> >
>|||Would you like to create simple report that exhibits this problem and e-mail
it to me?
Thanks,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:%23NDgWQimEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks this works.
> Now just one more problem. If we use the example below, on the column
> grouping(Contains Months) have an iif to check if the date is before a
> parameter passed to the report. I then use either one or the other field
> from the database depending on whether it is true or false.
> When I then add the total it would give me the wrong value, not really
> sure
> which value it is giving me, but it always one value from the column and
> not
> a calculated value.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> Set Subtotal position to "Before" and use top and bottom padding to add
>> space around subtotal
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Reg" <reg@.dsl.za.org> wrote in message
>> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I would like to have the following,
>> >
>> > field 1 field 2 field 3
>> > ---
>> > empty row
>> > total 1000 1000 1000
>> > empty row
>> > field 4 2000 2000 2000
>> > field 5 8000 8000 8000
>> >
>> > Any advice on how to do that?
>> >
>> >
>>
>

Matrix Totals Problem - SSRS 2005

I wasn't able to get my totals working accurately in a table footer using SUM, so I though hmm, maybe try matrix...well, still same type of problems...a bit different but still, my totals are not totalling the columns!

Screen Shot:

http://www.photopizzaz.biz/matrix_totals_problem.jpg

I don't know what else to do, I give up at this point and need help from someone here who has already gone through these headaches!

I don't understand why BOL doesn't address more complicated issues such as this...they barely tell you anything there.

I believe you need to preface your column expressions with sum: ie: the expression under customer number would be: sum(fields!customernumber.Value)sql

Matrix Totals - left formatted

I have successfully created a matrix consisting of payroll dates as column
headers, with task types as my rows and hours/task/day the detail data. The
columns total perfectly, but the totals only display to the RIGHT of all the
data. We display our totals FIRST, then the detail data. Can I do this?
total hrs day 1 day 2
task 1 16 8 8Yes. Click on the little green triangle in the (row/column) heading to get
the subtotal properties and look at the properties window. There is a
"Position" property which is set to "After" by default. You can set it to
"Before", which gives you the effect you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
>I have successfully created a matrix consisting of payroll dates as column
> headers, with task types as my rows and hours/task/day the detail data.
> The
> columns total perfectly, but the totals only display to the RIGHT of all
> the
> data. We display our totals FIRST, then the detail data. Can I do this?
> total hrs day 1 day 2
> task 1 16 8 8|||Thank you. Wasn't sure what "Position" meant. I appreciate your quick
response.
"Robert Bruckner [MSFT]" wrote:
> Yes. Click on the little green triangle in the (row/column) heading to get
> the subtotal properties and look at the properties window. There is a
> "Position" property which is set to "After" by default. You can set it to
> "Before", which gives you the effect you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
> news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
> >I have successfully created a matrix consisting of payroll dates as column
> > headers, with task types as my rows and hours/task/day the detail data.
> > The
> > columns total perfectly, but the totals only display to the RIGHT of all
> > the
> > data. We display our totals FIRST, then the detail data. Can I do this?
> > total hrs day 1 day 2
> > task 1 16 8 8
>
>

Matrix Totals

Below is part of a matrix report. Sooo close, but I have two problems I have not been able to solve. Please help as a I have several similar reports to do.

1. Row totals. I have been able to get a row total by adding a row group (i.e., $849.7 in the first row). However it does not display a group total (i.e., the sum of $849.7 and $371.3 for Economic Development) for that column.

I have not been able to add a static column outside of the dynamic groupings. I thought this might be a resolution by displaying the sum of the Estimated Costs in a static column and hoping it would show the group totals the same as with the dynamic column totals. Is there a way to do this?

2. Sorting. The report needs to display the groups based on the descending total values. I have specified the following sorts on the groups: sum(Fields!Estimated_Cost),"matrix1_Proj_Typ_Group" descending (this is the first column) and sum(Fields!Estimated_Cost),"matrix1_Proj_Typ" descending (this is the second column). Neither sort appears to be work.

City

County

Federal

Joint

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Economic Development

Business District Development

$849.7

$816.0

66.83%

$27.2

2.23%

$0.0

0.0%

$6.5

0.53%

Industrial Sites & Parks

$371.3

$131.5

10.77%

$190.4

15.59%

$0.0

0.0%

$36.0

2.95%

Total

$947.6

77.61%

$217.6

17.82%

$0.0

0.0%

$42.5

3.48%

Education

K-12 New School Construction

$1,594.7

$223.0

10.05%

$1,363.5

61.43%

$0.0

0.0%

$0.0

0.0%

Non K-12 Education

$37.8

$3.1

0.14%

$33.2

1.5%

$0.0

0.0%

$0.0

0.0%

School System-wide Need

$587.1

$167.2

7.53%

$419.2

18.89%

$0.0

0.0%

$0.0

0.0%

Total

$393.2

17.72%

$1,816.0

81.82%

$0.0

0.0%

$0.0

0.0%

General Government

Other Facilities

$21.3

$17.5

4.3%

$3.9

0.95%

$0.0

0.0%

$0.0

0.0%

Property Acquisition

$8.8

$6.8

1.68%

$2.0

0.49%

$0.0

0.0%

$0.0

0.0%

Public Buildings

$375.5

$294.2

72.54%

$72.0

17.74%

$3.0

0.74%

$6.2

1.52%

Total

$318.5

78.52%

$77.8

19.19%

$3.0

0.74%

$6.2

1.52%

1. Row totals: finally ran across a reference to "subtotals" and it solved this issue perfectly.

2. Can anyone help me with the sorting issue? Is this a bug or am I specifying this incorrectly?

|||

Regarding sorting:

What exactly are you trying to sort? You have a matrix - so if you are trying to sort descending based on the totals for "Economic Development", it will impact the overall column group sort order (which is shared for all row groups!)

You cannot have every row group instance ("Economic Development", etc.) sort the column group differently. They share the same column groups. If just the cell data is re-sorted, the final matrix data would be correct for some row/column intersections but incorrect for other intersections.

-- Robert

Matrix Totals

Anyway to put the Totals column to the left of the data column?Subtotals have a Position property (at the bottom of the VS properties
window). By default it is "After". Does "Before" work for you?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%23aaipLZpEHA.648@.tk2msftngp13.phx.gbl...
> Anyway to put the Totals column to the left of the data column?
>

Matrix Total Line till the end of the dynamic columns & Bold totals and sub-totals

I am using Matrix in one of my reports and I would like to have my
sub-total & total line strech until the end of the last 'dynamic'
column.
Also I would like to display the totals and sub-totals in bold.
Is this possible?
If yes, please let me know the workaround as soon as possible.
Thanking you in advance.Hi Alkesh,
I also want the same thing to be done. Do let me know if you do get a
solution to it.
Thanks,
Param
Capgemini India|||Param,
One way out is to calculate the totals and the subtotals in SP itself.
Then put a conditional iif to get it formatted. Let me know your views.
Rgds,
Alkesh
alkesh.patel@.math.netsql

matrix total column

How do I get the 'Totals' column in a matrix to appear on the left, just
after the row headers instead of on the far right?In the very left hand column, insert a rectangle containing two text boxes
into each cell. This gives the impression of two columns, even though there
is only the one.
It's a work round but it does the job. It would be nice to see this
functionality appear out of the box.
"rocket salad" wrote:
> How do I get the 'Totals' column in a matrix to appear on the left, just
> after the row headers instead of on the far right?|||Here's a tip: Check what happens if you export this report to Excel. I got
into trouble with a report that looked good in RS and PDF, and threw a nice
little error message in my customized text box when doing something
similar...
Kaisa M. Lindahl Lervik
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
> In the very left hand column, insert a rectangle containing two text boxes
> into each cell. This gives the impression of two columns, even though
> there
> is only the one.
> It's a work round but it does the job. It would be nice to see this
> functionality appear out of the box.
> "rocket salad" wrote:
>> How do I get the 'Totals' column in a matrix to appear on the left, just
>> after the row headers instead of on the far right?|||I'm actually quite surprised to find it works perfectly fine. What you say
makes sense, because the logic of having cells within cells sounds a bit
hokey. When I try it, though, it exports fine. I'm using RS2000 and Excel
2003, maybe you were having a problem with a different version of Excel?
"Kaisa M. Lindahl Lervik" wrote:
> Here's a tip: Check what happens if you export this report to Excel. I got
> into trouble with a report that looked good in RS and PDF, and threw a nice
> little error message in my customized text box when doing something
> similar...
> Kaisa M. Lindahl Lervik
> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
> news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
> > In the very left hand column, insert a rectangle containing two text boxes
> > into each cell. This gives the impression of two columns, even though
> > there
> > is only the one.
> >
> > It's a work round but it does the job. It would be nice to see this
> > functionality appear out of the box.
> >
> > "rocket salad" wrote:
> >
> >> How do I get the 'Totals' column in a matrix to appear on the left, just
> >> after the row headers instead of on the far right?
>
>|||Same version of Excel.
Nothing is better than it works for you. :)
Kaisa
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:35CD264D-3EA7-4062-97A4-D9DE1457FB6B@.microsoft.com...
> I'm actually quite surprised to find it works perfectly fine. What you say
> makes sense, because the logic of having cells within cells sounds a bit
> hokey. When I try it, though, it exports fine. I'm using RS2000 and Excel
> 2003, maybe you were having a problem with a different version of Excel?
> "Kaisa M. Lindahl Lervik" wrote:
>> Here's a tip: Check what happens if you export this report to Excel. I
>> got
>> into trouble with a report that looked good in RS and PDF, and threw a
>> nice
>> little error message in my customized text box when doing something
>> similar...
>> Kaisa M. Lindahl Lervik
>> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
>> news:005C4819-D8A9-4F59-B55F-87E41440DC6F@.microsoft.com...
>> > In the very left hand column, insert a rectangle containing two text
>> > boxes
>> > into each cell. This gives the impression of two columns, even though
>> > there
>> > is only the one.
>> >
>> > It's a work round but it does the job. It would be nice to see this
>> > functionality appear out of the box.
>> >
>> > "rocket salad" wrote:
>> >
>> >> How do I get the 'Totals' column in a matrix to appear on the left,
>> >> just
>> >> after the row headers instead of on the far right?
>>|||You can set this by a property called "position" as "Before" (default is
"After")
To do so, Click on the Green Triangle & property. There u can see position
as last property.
"rocket salad" wrote:
> How do I get the 'Totals' column in a matrix to appear on the left, just
> after the row headers instead of on the far right?

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 Percentage

I have a matrix with dynamic rows and columns. I got the totals for the
Rows and Columns by
right clicking the groups and selecting the SubTotal option.
Can I display the totals in percentage.
[Column1] [Columns2] [ Total] [C-Percent]
[Row1] 1 2 3 50%
[Row2 ] 2 1 3 50%
----
[Total] 3 3 6
[R-Percent](50%) (50%) (100%)
Any help is appreciated.
Thanks
ReddyCreate a column, and set your expression to 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 Lervik
"reddy" <pparlapa@.gmail.com> wrote in message
news:OklmLNQVGHA.6048@.TK2MSFTNGP11.phx.gbl...
>I have a matrix with dynamic rows and columns. I got the totals for the
>Rows and Columns by
> right clicking the groups and selecting the SubTotal option.
> Can I display the totals in percentage.
> [Column1] [Columns2] [ Total] [C-Percent]
> [Row1] 1 2 3 50%
> [Row2 ] 2 1 3 50%
> ----
> [Total] 3 3 6
> [R-Percent](50%) (50%) (100%)
> Any help is appreciated.
> Thanks
> Reddy
>sql

matrix showing wrong totals

Going bonkers here. I have a matrix that displays total referrals by month
for the year. Two months, Feb and April are off by 1 when I view the matrix,
but other months show correct amt. when just doing a simple query with same
criteria, the dataset is correct. I've looked at the data countless times
but cannot deterine where the issue is. Possible matrix problem? anyone seem
results llike this?
HELP!more food for thought. I changed the query a bit. rather than selecting all
companies who referred to us, I selected just one. Results were accurate with
just one referrer. I'm still perplexed though...
"Brian L" wrote:
> Going bonkers here. I have a matrix that displays total referrals by month
> for the year. Two months, Feb and April are off by 1 when I view the matrix,
> but other months show correct amt. when just doing a simple query with same
> criteria, the dataset is correct. I've looked at the data countless times
> but cannot deterine where the issue is. Possible matrix problem? anyone seem
> results llike this?
> HELP!|||are you using olap or relational?
sounds like a standard olap situation where you need to write some
crazy-ass MDX statement
-Aaron
Brian L wrote:
> more food for thought. I changed the query a bit. rather than selecting all
> companies who referred to us, I selected just one. Results were accurate with
> just one referrer. I'm still perplexed though...
> "Brian L" wrote:
> > Going bonkers here. I have a matrix that displays total referrals by month
> > for the year. Two months, Feb and April are off by 1 when I view the matrix,
> > but other months show correct amt. when just doing a simple query with same
> > criteria, the dataset is correct. I've looked at the data countless times
> > but cannot deterine where the issue is. Possible matrix problem? anyone seem
> > results llike this?
> >
> > HELP!

Matrix Question

In a matrix with multiple groups on an axis, which has sub totals, how do
you determine in an expression where a particular data cell is?
For example:
----
| -2003 | +2004 |
Total |
----
| Q1 | Q2 | Q3 | Q4 | |
|
----
Item 1 | x1 | x2 | x3 | x4 | x5 |
x6 |
----
Item 2 | x7 | x8 | x9 | x10 | x11 |
x12 |
----
x1 = Item 1 for 2003-Q1
x2 = Item 1 for 2003-Q2
x3 = Item 1 for 2003-Q3
x4 = Item 1 for 2003-Q4
x5 = Item 1 for 2004
x6 = Item 1 for (2003 + 2004)
x7 = Item 2 for 2003-Q1
x8 = Item 2 for 2003-Q2
x9 = Item 2 for 2003-Q3
x10 = Item 2 for 2003-Q4
x11 = Item 2 for 2004
x12 = Item 2 for (2003 + 2004)
For instance:
At cell position x4, how do I know that I am in group 2 (Quarters), at cell
position x5, how do I know that I am in group 1 (years), and at cell
position x6, how do I know that I am in group 1 (years) sub total'Did you look at the InScope function? It will allow you to distinguish
between cells in subtotals and cells in the groupings. More information on
InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Allan" <paul_allan@.maxqtech.com> wrote in message
news:O%23pHpENnEHA.3172@.TK2MSFTNGP10.phx.gbl...
> In a matrix with multiple groups on an axis, which has sub totals, how do
> you determine in an expression where a particular data cell is?
> For example:
> ----
> | -2003 | +2004
|
> Total |
> ----
> | Q1 | Q2 | Q3 | Q4 | |
> |
> ----
> Item 1 | x1 | x2 | x3 | x4 | x5 |
> x6 |
> ----
> Item 2 | x7 | x8 | x9 | x10 | x11 |
> x12 |
> ----
> x1 = Item 1 for 2003-Q1
> x2 = Item 1 for 2003-Q2
> x3 = Item 1 for 2003-Q3
> x4 = Item 1 for 2003-Q4
> x5 = Item 1 for 2004
> x6 = Item 1 for (2003 + 2004)
> x7 = Item 2 for 2003-Q1
> x8 = Item 2 for 2003-Q2
> x9 = Item 2 for 2003-Q3
> x10 = Item 2 for 2003-Q4
> x11 = Item 2 for 2004
> x12 = Item 2 for (2003 + 2004)
> For instance:
> At cell position x4, how do I know that I am in group 2 (Quarters), at
cell
> position x5, how do I know that I am in group 1 (years), and at cell
> position x6, how do I know that I am in group 1 (years) sub total'
>

Matrix Question

I have a 2 column matrix, (2 months) with sales totals by day in the rows. Is there a way to add a 3rd column and show the +/- % for each day?

Thanks!

BobP

You'll need to add a calculation column in the query first, then add the column to the matrix.

Matrix Problems

Hi All,
I need to get the following desing in a matrix...
---
Revison Rev1 Rev2 Rev3 Rev4
---
Totals
---
Revenue $100 $200 $400 $500
Cost $10 $30 $40 $50
---
Toals is static row how to get that row in a matrrix'
Please help me out here.......
Thanks,You could put the Totals as a group header or footer, depending on what you
are trying to achieve.
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:C79192E6-2C58-4412-AA48-6C6BAB289D25@.microsoft.com...
> Hi All,
> I need to get the following desing in a matrix...
> ---
> Revison Rev1 Rev2 Rev3 Rev4
> ---
> Totals
> ---
> Revenue $100 $200 $400 $500
> Cost $10 $30 $40 $50
> ---
> Toals is static row how to get that row in a matrrix'
> Please help me out here.......
> Thanks,|||I have a static matrix column example at www.msbicentral.com
it is called Matrix.StaticColumns.RDL and it is under downloads, reporting
services, RDL
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:C79192E6-2C58-4412-AA48-6C6BAB289D25@.microsoft.com...
> Hi All,
> I need to get the following desing in a matrix...
> ---
> Revison Rev1 Rev2 Rev3 Rev4
> ---
> Totals
> ---
> Revenue $100 $200 $400 $500
> Cost $10 $30 $40 $50
> ---
> Toals is static row how to get that row in a matrrix'
> Please help me out here.......
> Thanks,

Matrix order of totals

I have a matrix that shows sums by month. The dataset returns both month and quarter. I want to have subtotals by Quarter. I have this working and the matrix returns columns as follows:
Jan Feb March Q1 April May June Q2 ... Total

What I would like to have is
Jan Feb March April May ... Q1 Q2 Q3 Q4 Total

Is there anyway to have subtotals displayed at the end of the row?

For dynamic groups (e.g. quarter), the subtotals are always part of that group. They currently cannot be moved out of that group instance.

Another way of doing this, is to use a table in the report with explicit columns for Jan, Feb, ..., Dec, Q1, ..., Q4, Total. You would then need to use expressions to calculate your month/quarter/year aggregations.

-- Robert

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 column totals and the total row

I am trying to sum the columns in a matrix and am having trouble with the
totals row.
I right-click the row group and click subtotals. This creates the Total row
just fine, with the â'Totalâ' cell directly under the row group cell. However,
the cell in the data column is inaccessible, so Iâ'm not sure how or where to
put the sum expression for the columns. I canâ't put it in the â'Totalâ' cell of
the totals row, as this would simply total all the data in the matrix without
regard to column group. So what I have is the 1st row values in the total
row. If I canâ't get to the column cell on the total row, where do I enter the
sum expression for the columns?
This has to be a simple thing, if someone can just point me in the right
direction.
Thanks.It's the expression in the data cell itself. There's no separate subtotal
function.
"David" wrote:
> I am trying to sum the columns in a matrix and am having trouble with the
> totals row.
> I right-click the row group and click subtotals. This creates the Total row
> just fine, with the â'Totalâ' cell directly under the row group cell. However,
> the cell in the data column is inaccessible, so Iâ'm not sure how or where to
> put the sum expression for the columns. I canâ't put it in the â'Totalâ' cell of
> the totals row, as this would simply total all the data in the matrix without
> regard to column group. So what I have is the 1st row values in the total
> row. If I canâ't get to the column cell on the total row, where do I enter the
> sum expression for the columns?
> This has to be a simple thing, if someone can just point me in the right
> direction.
> Thanks.
>

matrix column aggregations

Quick question for you pro's...

Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?

Thanks in advance for your help,

Clint

Hello xtrout,

It is possible. What u have to do is - use a rectangle control, which will work as a container. Now u can place as many textboxes in it as u want. Where each text box is having a different aggregate function. If u r intended to display the aggregation only in subtotal column not at detail level then u can put the following expression for the 'visibility' property of the desired control (i.e. the textbox which u don't want to display in the detail section)

=iif(InScope("ColCountry"), true, false)

Here 'ColCountry' is the column group name in my case

I think this will solve ur problem

|||

Thanks for the info. It is close, but still does not seem to work properly. Using the rectangle with with your provided expression puts the two different aggregates into the same column so there is two columns nested in one. Do you know how to separate them? Maybe I need to do a tabular report instead of the matrix.

Thanks,

Clint

|||

Sorry xtrout,

I don't know any other alternative in matrix control. But I am using it this way by giving different column headers within a single column.

|||Thanks for the suggestion. It was worth a shot.

Matrix - Totals

In reference to Matrix, how can I total columns?
Thank you.Resolved
"Terry" wrote:
> In reference to Matrix, how can I total columns?
> Thank you.|||Did you use sub-totals'
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6CB1B741-066F-4523-A12E-7F81858D1999@.microsoft.com...
> Resolved
> "Terry" wrote:
>> In reference to Matrix, how can I total columns?
>> Thank you.|||The sub-totals did not work because I was not using an aggregate in the group
column. I tried to convert the group column into text which caused the
sub-totals not to be displayed.
Have a Merry Christmas and a Happy New Year.
"Greg" wrote:
> Did you use sub-totals'
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:6CB1B741-066F-4523-A12E-7F81858D1999@.microsoft.com...
> > Resolved
> >
> > "Terry" wrote:
> >
> >> In reference to Matrix, how can I total columns?
> >>
> >> Thank you.
>
>

Matrix - Totals

What does the following error mean?
[rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
references a field outside an aggregate function. Value expressions in
matrix cells should be aggregates, to allow for subtotaling.
Build complete -- 0 errors, 1 warningsResolved
"Terry" wrote:
> What does the following error mean?
> [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> references a field outside an aggregate function. Value expressions in
> matrix cells should be aggregates, to allow for subtotaling.
> Build complete -- 0 errors, 1 warnings|||Figured it out. For others knowledge, you need to add the First() around the
value, like this for BorderStyle:
=iif(First(Fields!desc.Value)="Sent","Solid","None")
"Sharon" wrote:
> How was this resolved?
>
> "Terry" wrote:
> > Resolved
> >
> > "Terry" wrote:
> >
> > > What does the following error mean?
> > >
> > > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > > references a field outside an aggregate function. Value expressions in
> > > matrix cells should be aggregates, to allow for subtotaling.
> > > Build complete -- 0 errors, 1 warnings|||How was this resolved?
"Terry" wrote:
> Resolved
> "Terry" wrote:
> > What does the following error mean?
> >
> > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > references a field outside an aggregate function. Value expressions in
> > matrix cells should be aggregates, to allow for subtotaling.
> > Build complete -- 0 errors, 1 warnings

Matrices & Totals

I have a matrix with two levels of row grouping and one level of column
grouping. For each cell I need to show the percentage of the group subtotal.
Here's an example.
CG1 CG2 CG3
# % # % # %
RG1 RG11 50 50% 20 10% 400 20%
RG12 20 20% 80 40% 300 15%
RG13 30 30% 100 50% 1300 65%
Total 100 33% 200 40% 2000 12%
RG2 RG21 10 5% 50 17% 1000 7%
RG22 30 15% 60 20% 2000 13%
RG23 40 20% 70 23% 3000 20%
RG24 60 30% 80 27% 4000 27%
RG25 60 30% 40 13% 5000 33%
Total 200 67% 300 60% 15000 88%
Grand Total 300 100% 500 100% 17000 100%
I've stuck with formula to calculate %.
Please, help!
--
Thanks,
IDhi,
Its hard to get the total from the report .. I had the same problem. What I
did, I took the total of the each rowm from the Query itself and in the
report screen, in the % cell, I did
Fields!RG!.Value/Fields!Total.Value and put its format P2.
it was working fine with these expression.
try this...
Bismi
"exkievan" wrote:
> I have a matrix with two levels of row grouping and one level of column
> grouping. For each cell I need to show the percentage of the group subtotal.
> Here's an example.
>
> CG1 CG2 CG3
> # % # % # %
> RG1 RG11 50 50% 20 10% 400 20%
> RG12 20 20% 80 40% 300 15%
> RG13 30 30% 100 50% 1300 65%
> Total 100 33% 200 40% 2000 12%
> RG2 RG21 10 5% 50 17% 1000 7%
> RG22 30 15% 60 20% 2000 13%
> RG23 40 20% 70 23% 3000 20%
> RG24 60 30% 80 27% 4000 27%
> RG25 60 30% 40 13% 5000 33%
> Total 200 67% 300 60% 15000 88%
> Grand Total 300 100% 500 100% 17000 100%
> I've stuck with formula to calculate %.
> Please, help!
> --
> Thanks,
> ID|||Thanks, Bismi. I did it the same way. I thought there's a way to do it on the
report.
--
Thanks,
ID
"Bismi" wrote:
> hi,
> Its hard to get the total from the report .. I had the same problem. What I
> did, I took the total of the each rowm from the Query itself and in the
> report screen, in the % cell, I did
> Fields!RG!.Value/Fields!Total.Value and put its format P2.
> it was working fine with these expression.
> try this...
> Bismi
> "exkievan" wrote:
> > I have a matrix with two levels of row grouping and one level of column
> > grouping. For each cell I need to show the percentage of the group subtotal.
> >
> > Here's an example.
> >
> >
> > CG1 CG2 CG3
> > # % # % # %
> > RG1 RG11 50 50% 20 10% 400 20%
> > RG12 20 20% 80 40% 300 15%
> > RG13 30 30% 100 50% 1300 65%
> > Total 100 33% 200 40% 2000 12%
> > RG2 RG21 10 5% 50 17% 1000 7%
> > RG22 30 15% 60 20% 2000 13%
> > RG23 40 20% 70 23% 3000 20%
> > RG24 60 30% 80 27% 4000 27%
> > RG25 60 30% 40 13% 5000 33%
> > Total 200 67% 300 60% 15000 88%
> > Grand Total 300 100% 500 100% 17000 100%
> >
> > I've stuck with formula to calculate %.
> >
> > Please, help!
> > --
> > Thanks,
> >
> > ID