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.
> > >
> > >
> > >