|
|
You're in the
sql server reporting services
group:Aggregate scoping on AVG function
sql server reporting services:
I have a report with the following structure in a table: table1_Group1 Header table1_Group2 Header Detail Record: =Fields!X.Value table1_Group2 Footer =Avg(Fields!X.Value) table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") In the table1_Group1 Footer, I want the average of all the table1_Group2 averages. The figure doesn't come out right if I average all the detail records in table1. I specifically need to average only the table1_Group2 Footer values. When I use the "table1_Group2" scope parameter, the typical "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." I am very sure that "table1_Group2" is the name of my containing group that I want to average. I have noticed that others with this same problem do not have any replies to their questions - so I'm throwing up this hail Mary in hopes that someone will be able to help. -- Ken Fayal
Other than (perhaps) rounding errors, The average of the group averages
should equal the average of the detail rows..... Can you account for the difference in value? -- Wayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. [quoted text, click to view] "Ken Fayal" wrote:
> Hello, > I have a report with the following structure in a table: > > table1_Group1 Header > table1_Group2 Header > Detail Record: =Fields!X.Value > table1_Group2 Footer =Avg(Fields!X.Value) > table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") > > In the table1_Group1 Footer, I want the average of all the table1_Group2 > averages. The figure doesn't come out right if I average all the detail > records in table1. I specifically need to average only the table1_Group2 > Footer values. > > When I use the "table1_Group2" scope parameter, the typical "The scope > parameter must be set to a string constant that is equal to either the name > of a containing group, the name of a containing data region, or the name of a > data set." I am very sure that "table1_Group2" is the name of my containing > group that I want to average. I have noticed that others with this same > problem do not have any replies to their questions - so I'm throwing up this > hail Mary in hopes that someone will be able to help. > > > -- > Ken Fayal
Wayne,
Thanks for responding to my problem. I put simplistic values in there to just show an example without going into too much detail. My real question was if you can use the name of a table grouping as a scope parameter for an aggregate function. The error message leads you to believe that you can use the "name of a containing group" as the scope parameter of an aggregate function. "table1_Group2" would definitely be the name of my containing groupp. But no matter what form of "table1_Group2" I use - it doesn't work. -- Ken Fayal Raptor Development, Inc. [quoted text, click to view] "Wayne Snyder" wrote:
> Other than (perhaps) rounding errors, The average of the group averages > should equal the average of the detail rows..... Can you account for the > difference in value? > > > -- > Wayne Snyder MCDBA, SQL Server MVP > Mariner, Charlotte, NC > > I support the Professional Association for SQL Server ( PASS) and it''s > community of SQL Professionals. > > > "Ken Fayal" wrote: > > > Hello, > > I have a report with the following structure in a table: > > > > table1_Group1 Header > > table1_Group2 Header > > Detail Record: =Fields!X.Value > > table1_Group2 Footer =Avg(Fields!X.Value) > > table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") > > > > In the table1_Group1 Footer, I want the average of all the table1_Group2 > > averages. The figure doesn't come out right if I average all the detail > > records in table1. I specifically need to average only the table1_Group2 > > Footer values. > > > > When I use the "table1_Group2" scope parameter, the typical "The scope > > parameter must be set to a string constant that is equal to either the name > > of a containing group, the name of a containing data region, or the name of a > > data set." I am very sure that "table1_Group2" is the name of my containing > > group that I want to average. I have noticed that others with this same > > problem do not have any replies to their questions - so I'm throwing up this > > hail Mary in hopes that someone will be able to help. > > > > > > -- > > Ken Fayal
I receive a similar scope parameter error when attempting to avg a detail
row. I am using a group to sum the columns. Any pointers on where to look for the proper way to do averaging? The RS books online are not helping. [quoted text, click to view] "Wayne Snyder" wrote:
> Other than (perhaps) rounding errors, The average of the group averages > should equal the average of the detail rows..... Can you account for the > difference in value? > > > -- > Wayne Snyder MCDBA, SQL Server MVP > Mariner, Charlotte, NC > > I support the Professional Association for SQL Server ( PASS) and it''s > community of SQL Professionals. > > > "Ken Fayal" wrote: > > > Hello, > > I have a report with the following structure in a table: > > > > table1_Group1 Header > > table1_Group2 Header > > Detail Record: =Fields!X.Value > > table1_Group2 Footer =Avg(Fields!X.Value) > > table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") > > > > In the table1_Group1 Footer, I want the average of all the table1_Group2 > > averages. The figure doesn't come out right if I average all the detail > > records in table1. I specifically need to average only the table1_Group2 > > Footer values. > > > > When I use the "table1_Group2" scope parameter, the typical "The scope > > parameter must be set to a string constant that is equal to either the name > > of a containing group, the name of a containing data region, or the name of a > > data set." I am very sure that "table1_Group2" is the name of my containing > > group that I want to average. I have noticed that others with this same > > problem do not have any replies to their questions - so I'm throwing up this > > hail Mary in hopes that someone will be able to help. > > > > > > -- > > Ken Fayal
I am starting to wonder if this is an early SSRS 2005 bug. I'm about to pull
my hair out...here's my thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266724&SiteID=1&mode=1 its seriously handicapping me since I cannot do this one in SQL...it's just way too complicated for SQL for my calculation and copying the forumula down from the Group definitely is not going to work for me in this situation either. [quoted text, click to view] "Ken Fayal" wrote:
> Wayne, > Thanks for responding to my problem. I put simplistic values in there to > just show an example without going into too much detail. My real question > was if you can use the name of a table grouping as a scope parameter for an > aggregate function. The error message leads you to believe that you can use > the "name of a containing group" as the scope parameter of an aggregate > function. "table1_Group2" would definitely be the name of my containing > groupp. But no matter what form of "table1_Group2" I use - it doesn't work. > > > -- > Ken Fayal > Raptor Development, Inc. > > > "Wayne Snyder" wrote: > > > Other than (perhaps) rounding errors, The average of the group averages > > should equal the average of the detail rows..... Can you account for the > > difference in value? > > > > > > -- > > Wayne Snyder MCDBA, SQL Server MVP > > Mariner, Charlotte, NC > > > > I support the Professional Association for SQL Server ( PASS) and it''s > > community of SQL Professionals. > > > > > > "Ken Fayal" wrote: > > > > > Hello, > > > I have a report with the following structure in a table: > > > > > > table1_Group1 Header > > > table1_Group2 Header > > > Detail Record: =Fields!X.Value > > > table1_Group2 Footer =Avg(Fields!X.Value) > > > table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") > > > > > > In the table1_Group1 Footer, I want the average of all the table1_Group2 > > > averages. The figure doesn't come out right if I average all the detail > > > records in table1. I specifically need to average only the table1_Group2 > > > Footer values. > > > > > > When I use the "table1_Group2" scope parameter, the typical "The scope > > > parameter must be set to a string constant that is equal to either the name > > > of a containing group, the name of a containing data region, or the name of a > > > data set." I am very sure that "table1_Group2" is the name of my containing > > > group that I want to average. I have noticed that others with this same > > > problem do not have any replies to their questions - so I'm throwing up this > > > hail Mary in hopes that someone will be able to help. > > > > > > > > > -- > > > Ken Fayal
Ken,
This may or may not be a tough one. The problem you're having is not an SRS bug, it's just the way it does aggregates for groups. What you're going to need to do is derive a count of the number of averages and then a sum of the averages. Divide the sum of the averages by the count of the averages. sum(avg)/count(avg) See the logic below. What you're getting now... Group1 Scope 1 + 2 + 3 = 6 6 / 3 = 2 G1Avg Group2 Scope 4 + 5 + 6 + 7 = 22 22 / 4 = 5.5 G2Avg Table Scope 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 28 / 7 = 4 TableAvg What you want... G1Avg+G2Avg=sum(avg) 2 + 5.5 = 7.5 sum(avg)/count(avg)=Avg of Avgs 7.5 / 2 = 3.75 avg of avgs Hope this helps
So can you relate that to my expression and how I'd do this? I guess I'm not
getting it. If you look at my URL, there, it has all the info on ProjFee Thanks a lot! -- dba123 [quoted text, click to view] "toolman" wrote:
> Ken, > This may or may not be a tough one. > The problem you're having is not an SRS bug, it's just the way it does > aggregates for groups. > What you're going to need to do is derive a count of the number of > averages and then a sum of the averages. > Divide the sum of the averages by the count of the averages. > sum(avg)/count(avg) > See the logic below. > What you're getting now... > Group1 Scope > 1 + 2 + 3 = 6 > 6 / 3 = 2 G1Avg > Group2 Scope > 4 + 5 + 6 + 7 = 22 > 22 / 4 = 5.5 G2Avg > Table Scope > 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 > 28 / 7 = 4 TableAvg > > What you want... > G1Avg+G2Avg=sum(avg) > 2 + 5.5 = 7.5 > sum(avg)/count(avg)=Avg of Avgs > 7.5 / 2 = 3.75 avg of avgs > > Hope this helps >
But if I can't reference my Group fields from my footer expression, then how
am I gonna do this? maybe I'm totally off track of what you meant but my whole problem is that in my table footer, I cannot reference my group textbox field names directly to even do an average. I'm only using one group header, not 2 groups in my table and one footer. -- dba123 [quoted text, click to view] "toolman" wrote:
> Ken, > This may or may not be a tough one. > The problem you're having is not an SRS bug, it's just the way it does > aggregates for groups. > What you're going to need to do is derive a count of the number of > averages and then a sum of the averages. > Divide the sum of the averages by the count of the averages. > sum(avg)/count(avg) > See the logic below. > What you're getting now... > Group1 Scope > 1 + 2 + 3 = 6 > 6 / 3 = 2 G1Avg > Group2 Scope > 4 + 5 + 6 + 7 = 22 > 22 / 4 = 5.5 G2Avg > Table Scope > 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 > 28 / 7 = 4 TableAvg > > What you want... > G1Avg+G2Avg=sum(avg) > 2 + 5.5 = 7.5 > sum(avg)/count(avg)=Avg of Avgs > 7.5 / 2 = 3.75 avg of avgs > > Hope this helps >
whoops, I replied to the wrong damn thread, my fault...this thread opened
automatically and I started to reply, no wonder this made no sense hahaha sorry -- dba123 [quoted text, click to view] "dba123" wrote:
> I am starting to wonder if this is an early SSRS 2005 bug. I'm about to pull > my hair out...here's my thread > > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266724&SiteID=1&mode=1 > > its seriously handicapping me since I cannot do this one in SQL...it's just > way too complicated for SQL for my calculation and copying the forumula down > from the Group definitely is not going to work for me in this situation > either. > > > > "Ken Fayal" wrote: > > > Wayne, > > Thanks for responding to my problem. I put simplistic values in there to > > just show an example without going into too much detail. My real question > > was if you can use the name of a table grouping as a scope parameter for an > > aggregate function. The error message leads you to believe that you can use > > the "name of a containing group" as the scope parameter of an aggregate > > function. "table1_Group2" would definitely be the name of my containing > > groupp. But no matter what form of "table1_Group2" I use - it doesn't work. > > > > > > -- > > Ken Fayal > > Raptor Development, Inc. > > > > > > "Wayne Snyder" wrote: > > > > > Other than (perhaps) rounding errors, The average of the group averages > > > should equal the average of the detail rows..... Can you account for the > > > difference in value? > > > > > > > > > -- > > > Wayne Snyder MCDBA, SQL Server MVP > > > Mariner, Charlotte, NC > > > > > > I support the Professional Association for SQL Server ( PASS) and it''s > > > community of SQL Professionals. > > > > > > > > > "Ken Fayal" wrote: > > > > > > > Hello, > > > > I have a report with the following structure in a table: > > > > > > > > table1_Group1 Header > > > > table1_Group2 Header > > > > Detail Record: =Fields!X.Value > > > > table1_Group2 Footer =Avg(Fields!X.Value) > > > > table1_Group1 Footer =Avg(Fields!X.Value, "table1_Group2") > > > > > > > > In the table1_Group1 Footer, I want the average of all the table1_Group2 > > > > averages. The figure doesn't come out right if I average all the detail > > > > records in table1. I specifically need to average only the table1_Group2 > > > > Footer values. > > > > > > > > When I use the "table1_Group2" scope parameter, the typical "The scope > > > > parameter must be set to a string constant that is equal to either the name > > > > of a containing group, the name of a containing data region, or the name of a > > > > data set." I am very sure that "table1_Group2" is the name of my containing > > > > group that I want to average. I have noticed that others with this same > > > > problem do not have any replies to their questions - so I'm throwing up this > > > > hail Mary in hopes that someone will be able to help. > > > > > > > > > > > > -- > > > > Ken Fayal
Ken,
I just went and looked at your code/expressions, etc. WOW!! I'm really sorry to have offered such a simplistic example. That being said, your answer does weedle down to sum(avg)/count(avg) but how or even whether you're going to get something as big as your expressions down to something that basic is...well, I'm just really glad I don't have to address it! Sorry I can't be more help
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |