I have a query that returns the averages for a selected group of records.
select AVG(h.stkhstClose), h.stkhstcsisym
from stkhst h
JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
and u.unvmemUnvID = 29001
group by h.stkhstcsisym
order by h.stkhstcsisym
this works and returns 99 averages. However, I need it to average only the
last 50 records of each group based on the date. I'm looking for a clean way
to do this without using a temporary table or cursor. and ideas would be
appreciated
thanks
kesJust a guess. See my signature for a more precise answer.
SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
<whatever> ORDER BY datecolumn DESC) x
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> I have a query that returns the averages for a selected group of records.
> select AVG(h.stkhstClose), h.stkhstcsisym
> from stkhst h
> JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
> and u.unvmemUnvID = 29001
> group by h.stkhstcsisym
> order by h.stkhstcsisym
> this works and returns 99 averages. However, I need it to average only the
> last 50 records of each group based on the date. I'm looking for a clean
way
> to do this without using a temporary table or cursor. and ideas would be
> appreciated
> thanks
> kes|||Thank You Aaron (you seem to answer a lot of my postings and your suggestion
s
have always proven helpful)
this will get the average for one group but how about the rest? Would a
where stkhstDate IN (select top 50 stkhstdate from stkhst where stkhstid =
xx order by stkhstdate DESC)
thank you
kes
"Aaron [SQL Server MVP]" wrote:
> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||>> this will get the average for one group but how about the rest?
Did you read Aaron's post? To repeat:
See his signature for a more precise answer.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Anith|||ok, fair enough.
stkhst:
CREATE TABLE [stkhst] (
[stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
[stkhstCsiSym] [int] NULL ,
[stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstDate] [int] NULL ,
[stkhstOpen] [decimal](9, 4) NULL ,
[stkhstHi] [decimal](9, 4) NULL ,
[stkhstLow] [decimal](9, 4) NULL ,
[stkhstClose] [decimal](9, 4) NULL ,
[stkhstVol] [int] NULL ,
[stkhstDiv] [int] NULL ,
[stkhstX] [int] NULL ,
[stkhstO] [int] NULL ,
[stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
[stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstCPosL] [int] NULL ,
[stkhstCPosH] [int] NULL ,
[stkHstCCol] [int] NULL
) ON [PRIMARY]
GO
unvmem:
CREATE TABLE [unvmem] (
[unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
[unvmemCsiId] [int] NOT NULL ,
[unvmemUnvID] [int] NOT NULL ,
[unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
) ON [PRIMARY]
GO
"Aaron [SQL Server MVP]" wrote:
> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||noted, posted
thanks
kes
"Anith Sen" wrote:
> Did you read Aaron's post? To repeat:
> See his signature for a more precise answer.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> --
> Anith
>
>|||What about sample data and desired results?
The point is that we're not going to drive to Wichita or Kansas or wherever
you are to see what data is in your table and try to figure out what result
you want from that data. And we're certainly not going to spend our
afternoon inventing fictitious but possibly unrealistic data to populate
your empty table, then spend time developing a solution against that, only
to find out all the "buts" that come with the assumptions we made. Please
supply sample data in the form of INSERT statements, and the resultset you
want based on that data.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO|||Kurt,
Your tables seem to have no primary keys which is a critical design flaw.
Generally for such problems, others cannot test the solutions without sample
data. You have not provided that either. Also as a side note, if your scheme
allows, you may want to look closely at your naming convention as well.
Here is another attempt with guesswork:
SELECT AVG( stkhstClose ), stkhstcsisym
FROM ( SELECT TOP 50 h.stkhstClose, h.stkhstcsisym
FROM stkhst h
INNER JOIN unvmem u
ON h.stkhstcsisym = u.unvmemCsiId
WHERE u.unvmemUnvID = 29001
ORDER BY h.stkhstcsisym ) D ( stkhstClose, stkhstcsisym )
GROUP BY stkhstcsisym ;
Anith|||Kurt,
You want to extract and average the last 50 records for each group...
Just add a where clause that restricts the query to operate only on those
records which have 50 or less "partners" (in the same group) after them...
Select AVG(h.stkhstClose), h.stkhstcsisym
From stkhst h
Where (Select Count(*) From stkhst
Where stkhstcsisym = h.stkhstcsisym
And DateColumn >= h.DateColumn) <= 50
Select * From
"Kurt Schroeder" wrote:
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO
> "Aaron [SQL Server MVP]" wrote:
>|||My apologies, I did not mean to imply that I needed more than advise. Please
understand that I do not feel it appropriate to ask for more than just that.
I feel it would be unfair to you or anyone else to do my work for me.
Aaron, your first posting to my question gave me what I needed to search for
the answer. My real query is much more complex, but this part of it was
simple enough to post for advice.
Again I wish to thank you for your help.
Humbly yours
kes
"Aaron [SQL Server MVP]" wrote:
> What about sample data and desired results?
> The point is that we're not going to drive to Wichita or Kansas or whereve
r
> you are to see what data is in your table and try to figure out what resul
t
> you want from that data. And we're certainly not going to spend our
> afternoon inventing fictitious but possibly unrealistic data to populate
> your empty table, then spend time developing a solution against that, only
> to find out all the "buts" that come with the assumptions we made. Please
> supply sample data in the form of INSERT statements, and the resultset you
> want based on that data.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>
Showing posts with label averages. Show all posts
Showing posts with label averages. Show all posts
Sunday, February 19, 2012
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.
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.
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?
Thanks
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.
|||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:
> 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.
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?
Thanks
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.
|||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:
> 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.
Thursday, February 16, 2012
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 (SystemI
D
>= @.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:
> 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.
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 (SystemI
D
>= @.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:
> 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 with multi-select
I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:
Policy A: 2000
Policy B: 1000
Policy C: 3000
Average: 2000
Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies
the total policy amounts would be :
Policy A: 4000
Policy B: 2000
Policy C: 6000
Average: 2000
The summation (12000) should be divided by the number of policies * the number of carriers (6).
However, I wind up getting 4000 as my average. Any thoughts?
Here is the MDX I am using:
Total Policy Premiums:
sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))
Avg:
avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])
Any insight would be greatly appreciated.
You could do this for example:
AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),
[Measures].[TOTAL POLICY PREM] )
Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.
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?
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?
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.
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?
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.
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?
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.
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?
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
Subscribe to:
Posts (Atom)