group issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

group issue

Author

Topic

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2006-12-04 : 15:47:58

I have a problem in reporting services.
In the report, I have field1 (int) , field2 (char) and a group based on field1.
Now, I want Avg function for the group.
Something like:
if field2="C" then exclude it from the Avg function.
The function I wrote is:

Avg(iif((Fields!field2.Value="C"),0,Fields!field1.Value))

But I got #Error in the group field when I preview it.

Can someone help me?
Thanks!

jhermiz

3564 Posts

Posted - 2006-12-04 : 16:12:08

Avg = total / count

Let us assume your count is 0 and your total is 0
What happens to 0 / 0 ?

It is a common problem when people try to divide unknowns, its like Infinity / Infinity or NULL / NULL.
The result is not deterministic and is known as Undetermined.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2006-12-04 : 16:18:53

I used non-null or not 0 value to test it. 0/0 is not the case in here.
Anyway, what if I am using Sum instead of Avg function?
What can Sum function take care of excluding here?

Thanks!

jhermiz

3564 Posts

Posted - 2006-12-05 : 07:51:41

SUM is just that a function to sum a groups field.
So take the sum and divide it by the row count.

To get the row count use the RowNumber function in rs.

From RS Books Online:

"RowNumber Function
Returns a running count of all rows in the specified scope.

Syntax
RowNumber(Scope)
Parameters
Scope
(String) The name of a dataset, grouping, or data region that contains the report items to which to apply the aggregate function. If a dataset is specified, the running value is not reset throughout the entire dataset. If a grouping is specified, the running value is reset when the group expression changes. If a data region is specified, the running value is reset for each new instance of the data region. For more information about the Scope parameter, see Aggregate Functions.
Return Type
Returns an Integer.

Example
The following code example provides a running count of rows in the outermost data region:

RowNumber(Nothing)"

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2006-12-05 : 11:19:45

Thanks, jhermiz
It works! I created a sub-group and using
RunningValue (Fields!field1.Value, Avg, 'sub-group')
It's only aggregated the sub-group I want.

jhermiz

3564 Posts

Posted - 2006-12-05 : 11:54:51

good job Dance Doll, dont ever be afraid to post your RS problems! Glad you got it working.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources