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 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 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 Reports Subtotals

Hi,
I have a two parter based on matrix reports
I am trying to develop a report which displays data for weekdays as columns
and Servers as rows. The range of the dates is selectable by the user so is
not fixed. We track start / end times for each server as it starts / ends
backups and the report is displayed somewhat similarly to below.
S M T W T F
S
SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
11:45 19:45 18:05 etc
IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
11:45 19:45 18:05 etc
The data looks something like this
ServerName varchar(100),
StartTime datetime,
EndTime datetime,
SuccessTF bit
Part 1
--
What i want to do is have subtotals that are for the number of records in
each column; so we can see successfull backups.
Part 2
--
The (first or final) column would be to determine the success of the backups
overall; i have this as a value against every row in sql server but only want
to display it once at the end.I think you want the CountRows function.
CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
the second part, I think the First() function is what you are after.Steve
MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> Hi,
> I have a two parter based on matrix reports
>
> I am trying to develop a report which displays data for weekdays as
> columns
> and Servers as rows. The range of the dates is selectable by the user so
> is
> not fixed. We track start / end times for each server as it starts / ends
> backups and the report is displayed somewhat similarly to below.
> S M T W T F
> S
> SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> 11:45 19:45 18:05 etc
> IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> 11:45 19:45 18:05 etc
> The data looks something like this
> ServerName varchar(100),
> StartTime datetime,
> EndTime datetime,
> SuccessTF bit
> Part 1
> --
> What i want to do is have subtotals that are for the number of records in
> each column; so we can see successfull backups.
> Part 2
> --
> The (first or final) column would be to determine the success of the
> backups
> overall; i have this as a value against every row in sql server but only
> want
> to display it once at the end.|||Steve,
I agree, only a subtotal row puts a value out for every column and doesnt
seem to let me edit the contents of it. They are all greyed out and display a
strange calculation based on the dates / times in the columns above.
Chris
"Steve MunLeeuw" wrote:
> I think you want the CountRows function.
> CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> the second part, I think the First() function is what you are after.Steve
> MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > Hi,
> > I have a two parter based on matrix reports
> >
> >
> > I am trying to develop a report which displays data for weekdays as
> > columns
> > and Servers as rows. The range of the dates is selectable by the user so
> > is
> > not fixed. We track start / end times for each server as it starts / ends
> > backups and the report is displayed somewhat similarly to below.
> >
> > S M T W T F
> > S
> > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > 11:45 19:45 18:05 etc
> > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > 11:45 19:45 18:05 etc
> >
> > The data looks something like this
> >
> > ServerName varchar(100),
> > StartTime datetime,
> > EndTime datetime,
> > SuccessTF bit
> >
> > Part 1
> > --
> > What i want to do is have subtotals that are for the number of records in
> > each column; so we can see successfull backups.
> >
> > Part 2
> > --
> > The (first or final) column would be to determine the success of the
> > backups
> > overall; i have this as a value against every row in sql server but only
> > want
> > to display it once at the end.
>
>|||Chris,
I am looking for the same type of answer and have a similar issue. When
at the subtotal level I want to do something different. In my example I have
several detail cells (they refer to as "static" or mutiple data fields
because they are all meant to be grouped by the same row/column constraints).
Anyway ... one of these fields is a flag that is either a 1 or 0 depending on
whether a field value (threshold) is one that matches a parameter value
entered to run the report. So in the subtotal I want to SUM all the values
and get a count of how many thresholds are tracking. I cant figure out how
to SUM this value when the detail cell expression is not a SUM it is an IIF
statement. In other words, like you , I want to do something different on
the subtotal but there isnt an option to alter the expression.
"Chris Hoare" wrote:
> Steve,
> I agree, only a subtotal row puts a value out for every column and doesnt
> seem to let me edit the contents of it. They are all greyed out and display a
> strange calculation based on the dates / times in the columns above.
> Chris
>
> "Steve MunLeeuw" wrote:
> > I think you want the CountRows function.
> >
> > CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> > the second part, I think the First() function is what you are after.Steve
> > MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> > news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > > Hi,
> > > I have a two parter based on matrix reports
> > >
> > >
> > > I am trying to develop a report which displays data for weekdays as
> > > columns
> > > and Servers as rows. The range of the dates is selectable by the user so
> > > is
> > > not fixed. We track start / end times for each server as it starts / ends
> > > backups and the report is displayed somewhat similarly to below.
> > >
> > > S M T W T F
> > > S
> > > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > 11:45 19:45 18:05 etc
> > > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > 11:45 19:45 18:05 etc
> > >
> > > The data looks something like this
> > >
> > > ServerName varchar(100),
> > > StartTime datetime,
> > > EndTime datetime,
> > > SuccessTF bit
> > >
> > > Part 1
> > > --
> > > What i want to do is have subtotals that are for the number of records in
> > > each column; so we can see successfull backups.
> > >
> > > Part 2
> > > --
> > > The (first or final) column would be to determine the success of the
> > > backups
> > > overall; i have this as a value against every row in sql server but only
> > > want
> > > to display it once at the end.
> >
> >
> >|||MJT:
In the end I gave up and wrote some asp to write the report out to excel.
We looked and using a sub report to do the totaling but the sql became overly
complex (and getting things to line up was a total pain)
Chris
"MJT" wrote:
> Chris,
> I am looking for the same type of answer and have a similar issue. When
> at the subtotal level I want to do something different. In my example I have
> several detail cells (they refer to as "static" or mutiple data fields
> because they are all meant to be grouped by the same row/column constraints).
> Anyway ... one of these fields is a flag that is either a 1 or 0 depending on
> whether a field value (threshold) is one that matches a parameter value
> entered to run the report. So in the subtotal I want to SUM all the values
> and get a count of how many thresholds are tracking. I cant figure out how
> to SUM this value when the detail cell expression is not a SUM it is an IIF
> statement. In other words, like you , I want to do something different on
> the subtotal but there isnt an option to alter the expression.
> "Chris Hoare" wrote:
> > Steve,
> >
> > I agree, only a subtotal row puts a value out for every column and doesnt
> > seem to let me edit the contents of it. They are all greyed out and display a
> > strange calculation based on the dates / times in the columns above.
> >
> > Chris
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> > > I think you want the CountRows function.
> > >
> > > CountRows("Department")http://msdn2.microsoft.com/en-us/library/ms156330.aspxOn
> > > the second part, I think the First() function is what you are after.Steve
> > > MunLeeuw"Chris Hoare" <choare@.nospam.nospam> wrote in message
> > > news:8B9F0A4D-E8E6-4B97-BE1F-16D7A0F8D0A9@.microsoft.com...
> > > > Hi,
> > > > I have a two parter based on matrix reports
> > > >
> > > >
> > > > I am trying to develop a report which displays data for weekdays as
> > > > columns
> > > > and Servers as rows. The range of the dates is selectable by the user so
> > > > is
> > > > not fixed. We track start / end times for each server as it starts / ends
> > > > backups and the report is displayed somewhat similarly to below.
> > > >
> > > > S M T W T F
> > > > S
> > > > SQL Server 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > > 11:45 19:45 18:05 etc
> > > > IIS Server 1 09:00 15:00 18:00 19:00 19:00 21:00 22:00
> > > > 11:45 19:45 18:05 etc
> > > >
> > > > The data looks something like this
> > > >
> > > > ServerName varchar(100),
> > > > StartTime datetime,
> > > > EndTime datetime,
> > > > SuccessTF bit
> > > >
> > > > Part 1
> > > > --
> > > > What i want to do is have subtotals that are for the number of records in
> > > > each column; so we can see successfull backups.
> > > >
> > > > Part 2
> > > > --
> > > > The (first or final) column would be to determine the success of the
> > > > backups
> > > > overall; i have this as a value against every row in sql server but only
> > > > want
> > > > to display it once at the end.
> > >
> > >
> > >

Matrix Reports Spawn Extra Page - Enclosing in List fixes this.

We really like using the Matrix reports, but we were finding that every Matrix report that we created would spawn an extraneous blank page. We tried putting the matrix in a rectangle, which works well for positioning other items on reports, but this had no effect on the problem.

Then we tried placing the matrix in a list with the list group details set to "=Nothing". It worked great - no more extra pages. Looked and didn't see this tip mentioned elsewhere so thought it might be worth sharing.

This usually happens because you have too many columns in your matrix, and it is spreading onto a second page horizontally.

Several other ways to fix this are: have less columns; make the columns and the fonts within them smaller; decrease the size of the margins on the report page; and alter the report page dimensions so that it is rendered in landscape mode.

Matrix Reports Filter

I am trying to have a matrix report filter on records that had sales in a prior month but no sales in the current month. Basically looking for accounts we “lost”

For example:

Name Invoice Date Quantity

Joe 4/1/2007 7

John 5/1/2007 13

John 4/1/2007 7

Tim 6/12/2007 6

The matrix report pulls back

Name Month

April May June

Joe 7

Tim 7 13

John 6

I am trying to return only names that had quantity in May and don't in June.

I would like it only to return Tim’s record because we lost his business in June.

Any ideas?

I think in your example you switched Tim and John between the query results and matrix.

Anyway, the best place to do this is in your query. I suspect you'll be running this report based on a "Month" parameter.

So in pseudo code you would write your query like

Code Snippet

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month - 1 month

AND account NOT IN

(

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month

)

Matrix Questions

I have been running into a couple of problems with my Matrix style reports:

First my Matrix is a drillthrough that looks like this

Year Group QuarterGroup Detail name Detail name Group1 Group2 Group3 Detail data Detail Data

1. How do I interactively sort by a detail column name, right now I'm using a switch statement in the Group1-3 sorts to allow the user to sort, the problem is that the user has to choose how to sort before running the report and I can't set the sort direction. Sort direction doesn't take an expression.

2. How do I collapse a Detail name column, when I set the visible property to false on the text boxes in the column all they disappear but the Year and Quarter Group textboxes don't resize.

3.

I would also like to have an drillthrough matrix report that looks like this

Year Group QuarterGroup Detail % Change over previous time period Group1 Group2 Group3 Detail data % of change from last quarter or year

What expression should I place in the % of change from last quarter or year box?

Anybody?

Matrix Control Reports...

Hi Folks,
I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
I have 2 matrix controls which feed of 2 different datasets. There are 3
columns in each matrix control which are part of a hierarchy. when the user
drills down a parent on the first control, is it possible for the child on
the second matrix control to be also visible.
For example when user clicks on the + sign before "A", "B" is made visible
on first matrix, at the same time "B" should be visible on the second matrix.
2. I have 2 matrix controls side by side, i have to show and hide the 2
based on a report parameter. Is it possible to dynamically alter the location
of the matrix control on the report. For example if the 1st matrix control is
made invisible, can i move 2 matrix control to take the place of the first
matrix cntrol.
Thank you
RamdasHi Ram,
I have the same problem (your second) - I want to decide the location of
the Matrix dynamically at run time. (Reporting Services)
If you've come to a solution, kindly contact me
DAVIDMA4@.YAHOO.COM
David
--
×?×?×?
"Ram" wrote:
> Hi Folks,
> I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
> I have 2 matrix controls which feed of 2 different datasets. There are 3
> columns in each matrix control which are part of a hierarchy. when the user
> drills down a parent on the first control, is it possible for the child on
> the second matrix control to be also visible.
> For example when user clicks on the + sign before "A", "B" is made visible
> on first matrix, at the same time "B" should be visible on the second matrix.
> 2. I have 2 matrix controls side by side, i have to show and hide the 2
> based on a report parameter. Is it possible to dynamically alter the location
> of the matrix control on the report. For example if the 1st matrix control is
> made invisible, can i move 2 matrix control to take the place of the first
> matrix cntrol.
> Thank you
> Ramdas
>

Matrix - Column Grouping

I am using Matrix table for one of my reports
.
I would like to have three column groupings; where first grouping is
default. Other two should be based on the parameter selection. If '<None>'
is selected from the parameter option I do not want to show that secondary
grouping.
Is it possible?I'm not sure what you are trying to do.
If you just want to dynamically toggle the visibility of groups within a
matrix, you should install and check out the sample reports (e.g.
CompanySales.rdl). Also read BOL:
http://msdn.microsoft.com/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
However, if you want to statically "hide" detail groupings based on
parameter values you should use a dynamic column grouping expression based
on IIF. If you want to always "hide" a certain inner grouping based on a
parameter value, you would just group on a constant (and make sure to use a
similar expression for the column grouping label):
=iif(Parameters!P1.Value = "None", 1, Fields!ProductCategory.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RA" <rchaudhary-nospam@.storis.com> wrote in message
news:OTVO0hGZEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I am using Matrix table for one of my reports
> .
> I would like to have three column groupings; where first grouping is
> default. Other two should be based on the parameter selection. If '<None>'
> is selected from the parameter option I do not want to show that secondary
> grouping.
>
> Is it possible?
>