Backing up log file with a script setup as a job

Is there any way to include in this script below something
to tell the job to backup will the log file when it gets
to a certain percentage say 85%.
Thanks,
Aboki.
/* Script for backing up the transaction log when it is
getting full and clear the space for the log file. */
/* This can also apply to any other system but you need to
change drive and part location */
/* to match where backup location */
Backup LOG IntegraProd TO
DISK = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\IntegraProdTran.bak'
GOYou could write a job to run every so often, which uses DBCC PERFLOG
to get information about log space and log space used. If log space for your
database is > 85% then issue your t-sql command.
The log is cyclic, it should wrap around and re-use empty space
where possible. It needs to keep information on the oldest active
transaction
for rollback. If your log is constantly at a certain size, this will most
likely be
right for the database anyway.
HTH
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GO|||You can set an alert to start the job. There should be an example alert you
can modify already installed with SQL.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GOsql

Background color for each first row in groups

I have a report that groups on departments. I want the first row for each
group to be light gray, while the row below it are white. Any suggestions?
ThanksIt has been a little while since you posted this question, but I think I have
an answer I discovered while researching a solution to one of my problems.
The RowNumber aggregate function might be helpful to you. Try the following
somewhere in your report to verify this will help:
RowNumber("group_name")
Try concatinating that to a text field on the row you want the coloring to
ultimately be on; I believe you will find that the first record in each group
has a 1. Presuming it does give you a 1 where you want it to be, all you
need to do then is select the row in question, and place the following
expression in the Background Color field (or any othr formatting field, like
Font Weight or Font Style):
=IIF( RowNumber("group_name") = 1 , "Light Gray" , "White" )
"PatNUFC" wrote:
> I have a report that groups on departments. I want the first row for each
> group to be light gray, while the row below it are white. Any suggestions?
> Thanks

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so would
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
Regards,
Jamie
Ready for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code was
> purchased. In the code is some code that appears awkward. (See below) The
> suggestion is to use a clause if exists (select blah blah -see below) then.
> Given that the manufacturer wrote it this way (three to four years ago), is
> there any reason why it may be a bad idea to change this proc... If so would
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>
|||Top 1 is just to show the item exists. Thanks for the reply David. There is
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different locking
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shipping
is another one.
Regards,
Jamie
"David Markle" wrote:

> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance.
> HTH
> -Dave
> thejamie wrote:
>

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so would
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
--
Regards,
JamieReady for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code was
> purchased. In the code is some code that appears awkward. (See below) The
> suggestion is to use a clause if exists (select blah blah -see below) then.
> Given that the manufacturer wrote it this way (three to four years ago), is
> there any reason why it may be a bad idea to change this proc... If so would
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>|||Top 1 is just to show the item exists. Thanks for the reply David. There is
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different locking
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shipping
is another one.
--
Regards,
Jamie
"David Markle" wrote:
> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance.
> HTH
> -Dave
> thejamie wrote:
> > We have code from a manufacturer that we have not changed since the code was
> > purchased. In the code is some code that appears awkward. (See below) The
> > suggestion is to use a clause if exists (select blah blah -see below) then.
> > Given that the manufacturer wrote it this way (three to four years ago), is
> > there any reason why it may be a bad idea to change this proc... If so would
> > you use "If Exists..." or something else.
> >
> > SELECT TOP 1
> > @.PickTicketID = AMU.PICK_TICKET_ID,
> > @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> > @.ItemKey = tUID.ITEM_KEY
> > FROM
> > tIU WITH(NOLOCK)
> > INNER JOIN
> > tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> > INNER JOIN
> > ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> > WHERE
> > tIU.MU_Key = @.MUKey
> >
> > IF ISNULL(@.PickTicketID,'') <> ''
> > --do stuff
> >
>

Awkward phrase

We have code from a manufacturer that we have not changed since the code was
purchased. In the code is some code that appears awkward. (See below) The
suggestion is to use a clause if exists (select blah blah -see below) then.
Given that the manufacturer wrote it this way (three to four years ago), is
there any reason why it may be a bad idea to change this proc... If so woul
d
you use "If Exists..." or something else.
SELECT TOP 1
@.PickTicketID = AMU.PICK_TICKET_ID,
@.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
@.ItemKey = tUID.ITEM_KEY
FROM
tIU WITH(NOLOCK)
INNER JOIN
tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
INNER JOIN
ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
WHERE
tIU.MU_Key = @.MUKey
IF ISNULL(@.PickTicketID,'') <> ''
--do stuff
Regards,
JamieReady for this one? Here's your answer, and you're sure to hate it: ;-)
"It depends."
It all depends on how often this procedure is created, and how the
underlying talbes are indexed. The question is this: What problem are
you trying to solve here? Performance? If so, it would be really
strange (and probably a mark of bad indexes) if this procedure performed
poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
would have to go. But doing that could kill the concurrency of the
database -- a lot of developers use NOLOCK as a crutch to avoid dealing
with locks at the expense of potentially munging your data.
Other than that, the code wouldn't be that bad, if there were an ORDER
BY. The strange thing here is the "TOP 1". Do you expect there to be
more than one row? If so, which row should it be? You'd need an ORDER
BY there to tell SQL Server, or you'll get something random. You could
GROUP BY all of the columns, but if you *knew* that there would only be
one row coming off the query, the TOP 1 *could* get you better performance.
HTH
-Dave
thejamie wrote:
> We have code from a manufacturer that we have not changed since the code w
as
> purchased. In the code is some code that appears awkward. (See below) T
he
> suggestion is to use a clause if exists (select blah blah -see below) then
.
> Given that the manufacturer wrote it this way (three to four years ago), i
s
> there any reason why it may be a bad idea to change this proc... If so wo
uld
> you use "If Exists..." or something else.
> SELECT TOP 1
> @.PickTicketID = AMU.PICK_TICKET_ID,
> @.PTLine = AMU.LINE_NUMBER, @.SOI = TUID.SOI, @.INVKey = tUID.INV_Key,
> @.ItemKey = tUID.ITEM_KEY
> FROM
> tIU WITH(NOLOCK)
> INNER JOIN
> tUID WITH(NOLOCK) ON tIU.UID_Key = tUID.UID_Key
> INNER JOIN
> ASSIGNED_MU AMU WITH(NOLOCK) ON AMU.MU_Key = tIU.MU_Key
> WHERE
> tIU.MU_Key = @.MUKey
> IF ISNULL(@.PickTicketID,'') <> ''
> --do stuff
>|||Top 1 is just to show the item exists. Thanks for the reply David. There i
s
a problem with this particular database - it was inherited and the nolock is
a crutch. It would be nice to know exactly where to put in different lockin
g
hints and where to eliminate them completely, but my mere four months here
don't allow me that perspective just yet. In my mind, establishing any
pipelined process and fitting the locking hints to perform based on the
pipelines would be an improvement. Pipeline example might be 1- picking an
item in the warehouse, 2- moving it into production, 3- preparing the
material and 4- packing and shipping it. This is too wide a scope however;
maybe just picking is the single pipeline and maybe just packing and shippin
g
is another one.
--
Regards,
Jamie
"David Markle" wrote:

> Ready for this one? Here's your answer, and you're sure to hate it: ;-)
> "It depends."
> It all depends on how often this procedure is created, and how the
> underlying talbes are indexed. The question is this: What problem are
> you trying to solve here? Performance? If so, it would be really
> strange (and probably a mark of bad indexes) if this procedure performed
> poorly. If it's cleanliness and data integrity issues, clearly NOLOCK
> would have to go. But doing that could kill the concurrency of the
> database -- a lot of developers use NOLOCK as a crutch to avoid dealing
> with locks at the expense of potentially munging your data.
> Other than that, the code wouldn't be that bad, if there were an ORDER
> BY. The strange thing here is the "TOP 1". Do you expect there to be
> more than one row? If so, which row should it be? You'd need an ORDER
> BY there to tell SQL Server, or you'll get something random. You could
> GROUP BY all of the columns, but if you *knew* that there would only be
> one row coming off the query, the TOP 1 *could* get you better performance
.
> HTH
> -Dave
> thejamie wrote:
>

AWE and limiting below 4GB SQL Server Memory Allocation

We are running W2K Advanced Server with SQL Server
Enterprise Edition. Problem is SQL Server takes almost
all of the memory 7.8GB of 8GB and Peoplesoft application
pages like crazy. Anyway, is there a way to limit the
below 4GB SQL Server memory and use all of the above 4GB
limit? Peoplesoft can't address the above 4GB line. The
real soluition is getting the !#@.$ application off of the
server. But I can't do that right now. Is there a patch
or some way to limit the below 4GB SQL Server memory to
say 2GB and let it have all 4GB above the 4GB line?You cannot tell sql server to use what memory area. You
should set the max memory for sql server to 4gb in sql
enterprise manager. At startup sql server will grab the
first 4gb of memory. So, configure sql server in service
control manager to start manually. Start the Peoplesoft
application first so that it will get the memory it needs.
Then start the sql server and it will grab 4gb of memory
out of available memory. SQL server will lock that memory
and won't dynamically release/acquire memory as it needs
(this is a feature of AWE).
This isn't perfect solution, but you will survive.
>--Original Message--
>We are running W2K Advanced Server with SQL Server
>Enterprise Edition. Problem is SQL Server takes almost
>all of the memory 7.8GB of 8GB and Peoplesoft application
>pages like crazy. Anyway, is there a way to limit the
>below 4GB SQL Server memory and use all of the above 4GB
>limit? Peoplesoft can't address the above 4GB line. The
>real soluition is getting the !#@.$ application off of the
>server. But I can't do that right now. Is there a patch
>or some way to limit the below 4GB SQL Server memory to
>say 2GB and let it have all 4GB above the 4GB line?
>.
>

AVG using ROW_NUMBER

I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc.Does anybody know how I can get this?

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

Results:

Partition This Year Date Code Location Code

1 .85 20070101 1

2 .58 20070509 1

1 .52 20070808 2

2 .54 20070905 2

3 .26 20070104 3

3 .26 20070905 3

Is this what you're looking for?

Code Snippet

select

Partition

,(sum(Thisyear)/count(*)) 'ThisYearAvg'

from

(

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

) a

group by

Partition

|||

The average of what?

select

...,

avg(Score) over(partition by Shop.Location_Code) as avg_score

from

....

AMB

|||

Yes Anthony, That's exactly what I was looking for. Thank you so much for your help!

Lindsay

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
>>I need to average the resulting averages from the query below
>>This query gets the averages for each TowerNumber.
>>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
>> AS AvgSupplyCalciumHardness
>>FROM tblTowers
>>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND
>>(SystemID
>>= @.SystemID) AND
>> (LocationID = @.LocationID)
>>GROUP BY LocationID, SystemID, TowerNumber
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 2005 5 5 10 20 5 5 5 5 5 10 75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total AVG 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 77.31 2005 5 0 5 10 20 5 0 5 5 5 5 0 10 75 5.77 -93.8% -100.0% -92.9% -89.5% -71.4% -94.1% -100.0% -94.4% -95.5% -90.9% -94.4% -100.0% -80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It will be a great help to us if you can send the detail.Currently we are hanging on this issue.We not only need average,we need Count and total also .The Detail is involved expression to convert from minutes(from stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,
Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,
Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks|||

Even I am facing same problem. Can someone please explain step by step. Thanks in advance.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

2005

5

5

10

20

5

5

5

5

5

10

75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

AVG

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

77.31

2005

5

0

5

10

20

5

0

5

5

5

5

0

10

75

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

2005

5

5

10

20

5

5

5

5

5

10

75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

AVG

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

77.31

2005

5

0

5

10

20

5

0

5

5

5

5

0

10

75

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks