back everything up in a single file?

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1icnk$v0b$1@.news.iastate.edu...
> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing

In the code above, DBBackup is not a file, it's a backup device, so in
theory it's possible that your DBA used to point the device at different
physical files to provide some sort of rotation, although from your
description that sounds unlikely. In fact, personally, I can't see how it
would be possible to manage backups effectively using her approach.

In any case, if you need a quick solution, I would suggest creating one or
more database maintenance plans, and back up all your databases to a
convenient disk location. After that, use your standard backup software or
methods to copy the backup files to tape and/or another physical server. The
maintenance plan wizard (Enterprise Manager, Tools menu) should be easy
enough to use that you can set this up fairly quickly. It will back up
directly to files, and can also remove old backups after a period you
specify.

I'm not saying that that is the best or only approach, and some
functionality (eg differential backups) isn't available from the wizard, but
as a way of quickly putting something manageable in place, it should be
fine.

After that, find a new DBA and/or start reading the Books Online information
on "Recovery Models" and "Backup and Restore Operations".

Simon|||Thanks much for your response, Simon. I'll check out the online
information you mentioned. I understand DBBackup is not a file. It's a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:

==========
Name Physical Location Device Type

DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========

So, I don't think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I'm wrong.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"comp.databases.ms-sqlserver" <anonymous@.devdex.com> wrote in message
news:403cfe5e$0$197$75868355@.news.frii.net...
> Thanks much for your response, Simon. I'll check out the online
> information you mentioned. I understand DBBackup is not a file. It's a
> logic device that can be changed to point to different physical devices.
> But at the same time, each backup device can only point to one physical
> device, either disk file or tape, right? Our SQL Server Enterprise
> Manager->Management->Backup shows:
> ==========
> Name Physical Location Device Type
> DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
> ===========
> So, I don't think this DBBackup.BAK which contains full, differential
> and log backups would work in restore. Please correct me if I'm wrong.
> Bing
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

A single file or device can contain multiple backups, and you can restore
any individual backup from it, as long as it makes sense in the context of
what you're restoring (you can't restore database A logs to database B, for
example, or restore logs out of order). It doesn't matter if you mix backup
types and databases in one file, except from a management perspective.

You can select the individual backup to restore in the Enterprise Manage
restore dialogue using the "First backup to restore" drop down. This may be
a clearer way to demonstrate what you can do (on a test server only, of
course), assuming you have sysadmin permissions:

/* Create a test DB */
create database foo
go

/* Back up the DB twice, and the log once, to the same file,
** with an msdb backup in the middle to show that we can
** mix backups.
*/
backup database foo to disk = 'c:\foo.bak'
go
backup database foo to disk = 'c:\foo.bak'
go
backup database msdb to disk = 'c:\foo.bak'
go
backup log foo to disk = 'c:\foo.bak'
go

/" View the backups available in the file */
restore headeronly from disk = 'c:\foo.bak'
go

/* Restore the 1st backup and make the DB available */
restore database foo from disk = 'c:\foo.bak' with file = 1, recovery
go
/* Restore the 2nd backup, but do not recover (so we can apply the log) */
restore database foo from disk = 'c:\foo.bak' with file = 2, norecovery
go
/* Restore the log and make the DB available */
restore log foo from disk = 'c:\foo.bak' with file = 4, recovery
go

/* Clean up */
drop database foo
go
exec master..xp_cmdshell 'del c:\foo.bak', no_output
go

Simon|||Great. Thanks a lot again for the lucid explanations with examples!
Very helpful. Seems one file is not a big problem. I need to read
more to work out a best solution for our situation.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
if the backup made on s1 can be restored on s2. Briefly, what hit me
was the backup file made on s1 could not be viewed on s2.

On server s1, back up (both full and differential) all the databases and
logs into a backup device which points to the disk file
e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
DBBackup.BAK on s1.

Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
DBBackup.BAK as a file in My Documents fine. But in the Enterprise
Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
error 'This device does not contain any backup sets'.

How so?

Bing

Bing Du wrote:

> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing|||Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

Bing Du wrote:

> I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
> if the backup made on s1 can be restored on s2. Briefly, what hit me
> was the backup file made on s1 could not be viewed on s2.
> On server s1, back up (both full and differential) all the databases and
> logs into a backup device which points to the disk file
> e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
> DBBackup.BAK on s1.
> Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
> DBBackup.BAK as a file in My Documents fine. But in the Enterprise
> Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
> error 'This device does not contain any backup sets'.
> How so?
> Bing
> Bing Du wrote:
>> Greetings,
>>
>> Our former SQL Server 2000 DBA backed up everything in a single disk
>> file. By everything I mean, full backup, differential backup and
>> transaction logs. See below for details of how the backup is
>> currently set up. When I did 'view contents' of DBBackup, I saw it
>> contained the mixture of log, full and diff backups of verious dates.
>> She has quit. Other coworkers said (in a not-sure tone) she was able
>> to restore the databases from such a single file, although nobody ever
>> saw or knew how she did recovery. My knowledge about SQL Server,
>> especially regarding its backup/restore is limited. But I've ever
>> worked with other databases, e.g. Oracle, MySQL and Postgres. I think
>> log backups, full backups and differential backups should be
>> completely separated. Also, in each type of backups, each database
>> should have its own backup file. Please advise.
>>
>> =====
>> Log backup:
>>
>> CREATE PROCEDURE sp_lbackup AS
>> Backup log AGEP To DBBackup
>> Backup log careerfairs To DBBackup
>> Backup log CoEdocuments To DBBackup
>> Backup log committee To DBBackup
>> Backup log conference To DBBackup
>> GO
>>
>> Full backup:
>>
>> CREATE PROCEDURE dbo.sp_fullbackup AS
>> Backup database AGEP To DBBackup WITH INIT
>> Backup database CoEdocuments To DBBackup
>> Backup database careerfairs To DBBackup
>> Backup database committee To DBBackup
>> Backup database conference To DBBackup
>> GO
>>
>> Diff backup:
>>
>> CREATE PROCEDURE sp_diffbackup AS
>> Backup database AGEP To DBBackup with differential
>> Backup database careerfairs To DBBackup with differential
>> Backup database CoEdocuments To DBBackup with differential
>> Backup database committee To DBBackup with differential
>> Backup database conference To DBBackup with differential
>> GO
>> =====
>>
>> Thanks in advance for any help,
>>
>> Bing
>|||"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1l794$aui$1@.news.iastate.edu...
> Ok, seems like only local disks show up when a new backup device is
> defined. After I copied the backup from the network shared directory to
> a local directory on s2, restore worked.
> Bing

<snip
FYI, it is possible to backup and restore from UNC paths, provided that the
account used to run MSSQL has access to the path:

backup database foo to disk = '\\MyServer\MyBackups\foo.bak'

Enteprise Manager only displays local drives, but I believe you can type in
a UNC path in the backup/restore dialogues, although I don't use EM much, so
I'm not 100% sure about that.

Simon|||I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

Bing

Simon Hayes wrote:

> "Bing Du" <bdu@.iastate.edu> wrote in message
> news:c1l794$aui$1@.news.iastate.edu...
>>Ok, seems like only local disks show up when a new backup device is
>>defined. After I copied the backup from the network shared directory to
>>a local directory on s2, restore worked.
>>
>>Bing
>>
>
> <snip>
> FYI, it is possible to backup and restore from UNC paths, provided that the
> account used to run MSSQL has access to the path:
> backup database foo to disk = '\\MyServer\MyBackups\foo.bak'
> Enteprise Manager only displays local drives, but I believe you can type in
> a UNC path in the backup/restore dialogues, although I don't use EM much, so
> I'm not 100% sure about that.
> Simon|||Bing Du (bdu@.iastate.edu) writes:
> I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

And what does "did not work" mean?

As Simon says, it depends on the account under which SQL Server is running.
If SQL Server is installed to run as Local Service, then you cannot access
network resources from SQL Server.

A word of caution, though, about backing up or restore from network
devices. I don't think is fully supported. That is, you can do it, but
you will be safe if you back up to local disk, and copy of the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949C56828223Yazorman@.127.0.0.1...
> Bing Du (bdu@.iastate.edu) writes:
> > I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.
> And what does "did not work" mean?
> As Simon says, it depends on the account under which SQL Server is
running.
> If SQL Server is installed to run as Local Service, then you cannot access
> network resources from SQL Server.
> A word of caution, though, about backing up or restore from network
> devices. I don't think is fully supported. That is, you can do it, but
> you will be safe if you back up to local disk, and copy of the network.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

According to Books Online (under BACKUP):

"If using a network server with a Uniform Naming Convention (UNC) name or
using a redirected drive letter, specify a device type of disk."

But anyway, a backup to a UNC path may fail because of network issues, so
it's fair to say that a local backup is a safer option.

Simon

Avg based on column values

Greetings,

I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.

The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record

-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.

Everytime I to set the value of a field or parameter at runtime I get a read-only error.

Maybee the only way is to use calculated fields.

Any ideas.

Create two functions:

line_sum(string) returns sum numbers in your Field|||

Thanks for the input.

It looks like this would work for row based averages. I should have stated that I need to Avg columnwise in the footer.

I have the database calcing and returning a row based avaerage for each record. I was looking for an OnNewRecord event or someway of calcing on vars.

I may have to create a new field of comma averages for each row. bleh!

Averages in Matrix report

Greetings,
I have a report that works just great:
Jan Feb Mar Apr
Facility1 80 78 65 90
Facility2 85 79 90 49
It tells you a calculated score for each facility for each month. It's
exactly what they asked for. Until I showed it to them. Now they want
more info.
So I added another row to sort by - Region
Jan Feb Mar
region1 facility1 80 78 65
facility2 85 79 90
region2 facility3 81 65 82
facility4 84 90 71
And that works great. The problem is, they want a YTD col, and these
are scores. I can't just add them up, I have to either average them,
or calc a YTD score for each facility and put it out to the right after
the last months column.
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
The second problem is, I'd like to be able to see an average for
Regions. Is it possible?
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region average 82.5 78.5 77.5 79.49
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
region average 82.5 77.5 76.5 78.83
here's my RDL.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<Width>5in</Width>
<CanGrow>true</CanGrow>
<Value>Monthly Scorecard</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=(Sum(Fields!MonthTotal.Value) & " %
")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!month.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.96875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>Cypress_APP</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_month">
<GroupExpressions>
<GroupExpression>=Fields!month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!month.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<FontSize>9pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>month</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=choose(Fields!month.Value,
"Jan Score",
"Feb Score",
"Mar Score",
"Apr Score",
"May Score",
"Jun Score",
"July Score",
"Aug Score",
"Sept Score",
"Oct Score",
"Nov Score",
"Dec Score"
)</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>4.90625in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>=Fields!PortfolioName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PortfolioName.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!name.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>3in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="RowColor">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.03125in</Width>
</RowGrouping>
<RowGrouping>
<Width>0.03125in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Month Total</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>1.05in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Cypress_APP">
<rd:DataSourceID>ed3b9625-deb2-4de7-b3be-35b5117506fa</rd:DataSourceID>
<DataSourceReference>Cypress_APP</DataSourceReference>
</DataSource>
</DataSources>
<Width>7.375in</Width>
<DataSets>
<DataSet Name="Cypress_APP">
<Fields>
<Field Name="name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PortfolioName">
<DataField>PortfolioName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="month">
<DataField>Month</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="measureID">
<DataField>MeasureID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MonthTotal">
<DataField>MonthTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Cypress_APP</DataSourceName>
<CommandText>SELECT e.Name, Portfolios.Name AS
PortfolioName, sc.Month, sc.MeasureID, dbo.udfn_M1vsM2(CASE WHEN
sc.[MeasureID] IN (26)
THEN (CASE sc.[Measure1] WHEN 0 THEN 0 ELSE
sc.[Measure2] / sc.[Measure1] END) ELSE sc.[Measure1] END, CASE WHEN
sc.[MeasureID] IN (26)
THEN sc.[Measure4] ELSE sc.[Measure2] END,
m.BusinessRule, m.partialScore, m.Score) AS MonthTotal
FROM ScoreCard sc INNER JOIN
Enterprise e ON sc.EnterpriseID = e.EnterpriseID
INNER JOIN
Measure m ON sc.MeasureID = m.MeasureID INNER
JOIN
Enterprise_View ev ON e.EnterpriseID = ev.EnterpriseID INNER JOIN
(SELECT EnterpriseID, [Name]
FROM Enterprise
WHERE EnterpriseTypeID = 2) Portfolios
ON Portfolios.EnterpriseID = ev.ParentID
WHERE (sc.Year = YEAR(@.date1))
GROUP BY Portfolios.Name, e.Name, sc.Month, sc.MeasureID, m.MeasureID,
sc.Measure1, sc.Measure2, sc.Measure4, m.BusinessRule, m.partialScore,
m.Score
ORDER BY sc.Month, e.Name, m.MeasureID</CommandText>
<QueryParameters>
<QueryParameter Name="@.date1">
<Value>=Parameters!date1.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>1577349c-ff67-4564-9cb1-afa782e635fe</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="date1">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd(DateInterval.Day, (DatePart(DateInterval.Day,
Now)) * -1, Now)</Value>
</Values>
</DefaultValue>
<Prompt>date1</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>> And that works great. The problem is, they want a YTD col, and these
> are scores. I can't just add them up, I have to either average them,
> or calc a YTD score for each facility and put it out to the right after
> the last months column.
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
No problem at all. I would create the average based upon the values coming
back from your query. Depending on how your report is set up, you will need
to place logic in your code to account for the number of months. If a month
has a zero score, don't include it in your average calculation...or
something like that.
> The second problem is, I'd like to be able to see an average for
> Regions. Is it possible?
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region average 82.5 78.5 77.5 79.49
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
> region average 82.5 77.5 76.5 78.83
Depending on how you have your report set up, there are several ways to do
this. You can add up the actual report objects (textbox1, textbox2, etc) and
get your average that way (check out the RunningValue() function), or you
could write your own function in the code block that maintains the average
value per region as the report renders.|||G wrote:
> No problem at all. I would create the average based upon the values coming
> back from your query. Depending on how your report is set up, you will need
> to place logic in your code to account for the number of months. If a month
> has a zero score, don't include it in your average calculation...or
> something like that.
Yes, but how do I show it. When I click the subtotal button, it puts a
total bar out there. If I put a formula in there, it puts the forumula
in the header of the report, not on the subtotal line like I want it.
> > The second problem is, I'd like to be able to see an average for
> > Regions. Is it possible?
> >
> > Jan Feb Mar YTD
> > region1 facility1 80 78 65 74.33
> > facility2 85 79 90 84.66
> > region average 82.5 78.5 77.5 79.49
> >
> > region2 facility3 81 65 82 76
> > facility4 84 90 71 81.66
> > region average 82.5 77.5 76.5 78.83
> Depending on how you have your report set up, there are several ways to do
> this. You can add up the actual report objects (textbox1, textbox2, etc) and
> get your average that way (check out the RunningValue() function), or you
> could write your own function in the code block that maintains the average
> value per region as the report renders.
Once I have the value, how do I insert it? as another rowgroup?