Hi all, I have what I think is quite a good question - ie, difficult
to answer! :)

Supposing I have 10 rows each containg a fee value but only one row
actually has a figure in it of, lets say, 10. All the other rows by
default, instead of being empty will show zeros.

If I wanted to display the average for all the rows, I simply add a
group, add a footer and add the expression "=AVG(Fields!Fee.Value)"
This would return the value 1 (10 divided by 10 rows)

However, I want to only average where the value is NOT zero.

I tried the following but just got "#Error" and no data.

=AVG(iif(Fields!Fee.Value = "0", "NULL", Fields!Fee.Value))

If anyone has a solution to this I'd be most grateful!

Thanks

Instead of using average use sum/count and have the count be the count of 
values > 0.

I don't have time right now to get the exact expression for you. Let me know 
if you need more info to do this and I'll see what I can do.

-- 
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Karl" <karl@rhodesfamily.co.uk> wrote in message 
news:504da21b-ae17-4f51-9e49-a5f8fa3104d9@p8g2000yqb.googlegroups.com...
> Hi all, I have what I think is quite a good question - ie, difficult
> to answer! :)
>
> Supposing I have 10 rows each containg a fee value but only one row
> actually has a figure in it of, lets say, 10. All the other rows by
> default, instead of being empty will show zeros.
>
> If I wanted to display the average for all the rows, I simply add a
> group, add a footer and add the expression "=AVG(Fields!Fee.Value)"
> This would return the value 1 (10 divided by 10 rows)
>
> However, I want to only average where the value is NOT zero.
>
> I tried the following but just got "#Error" and no data.
>
> =AVG(iif(Fields!Fee.Value = "0", "NULL", Fields!Fee.Value))
>
> If anyone has a solution to this I'd be most grateful!
>
> Thanks 

On 1 Dec, 17:55, "Bruce L-C  [MVP]" <bruce_lcNOS...@hotmail.com>
wrote:
> Instead of using average use sum/count and have the count be the count of
> values > 0.
>
> I don't have time right now to get the exact expression for you. Let me know
> if you need more info to do this and I'll see what I can do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Karl" <k...@rhodesfamily.co.uk> wrote in message
>
> news:504da21b-ae17-4f51-9e49-a5f8fa3104d9@p8g2000yqb.googlegroups.com...
>
> > Hi all, I have what I think is quite a good question - ie, difficult
> > to answer! :)
>
> > Supposing I have 10 rows each containg a fee value but only one row
> > actually has a figure in it of, lets say, 10. All the other rows by
> > default, instead of being empty will show zeros.
>
> > If I wanted to display the average for all the rows, I simply add a
> > group, add a footer and add the expression "=AVG(Fields!Fee.Value)"
> > This would return the value 1 (10 divided by 10 rows)
>
> > However, I want to only average where the value is NOT zero.
>
> > I tried the following but just got "#Error" and no data.
>
> > =AVG(iif(Fields!Fee.Value = "0", "NULL", Fields!Fee.Value))
>
> > If anyone has a solution to this I'd be most grateful!
>
> > Thanks

Thanks for your suggestion, I'll have a go at it that way today.

If you do get the time though I'd appreciate your sample code, not
only incase I dont work it out, but it'd be handy for others in future
to see it here..

Thanks

On 2 Dec, 09:21, Karl <k...@rhodesfamily.co.uk> wrote:
> On 1 Dec, 17:55, "Bruce L-C =A0[MVP]" <bruce_lcNOS...@hotmail.com>
> wrote:
>
>
>
> > Instead of using average use sum/count and have the count be the count =
of
> > values > 0.
>
> > I don't have time right now to get the exact expression for you. Let me=
 know
> > if you need more info to do this and I'll see what I can do.
>
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
>
> > "Karl" <k...@rhodesfamily.co.uk> wrote in message
>
> >news:504da21b-ae17-4f51-9e49-a5f8fa3104d9@p8g2000yqb.googlegroups.com...
>
> > > Hi all, I have what I think is quite a good question - ie, difficult
> > > to answer! :)
>
> > > Supposing I have 10 rows each containg a fee value but only one row
> > > actually has a figure in it of, lets say, 10. All the other rows by
> > > default, instead of being empty will show zeros.
>
> > > If I wanted to display the average for all the rows, I simply add a
> > > group, add a footer and add the expression "=3DAVG(Fields!Fee.Value)"
> > > This would return the value 1 (10 divided by 10 rows)
>
> > > However, I want to only average where the value is NOT zero.
>
> > > I tried the following but just got "#Error" and no data.
>
> > > =3DAVG(iif(Fields!Fee.Value =3D "0", "NULL", Fields!Fee.Value))
>
> > > If anyone has a solution to this I'd be most grateful!
>
> > > Thanks
>
> Thanks for your suggestion, I'll have a go at it that way today.
>
> If you do get the time though I'd appreciate your sample code, not
> only incase I dont work it out, but it'd be handy for others in future
> to see it here..
>
> Thanks


Cracked it... Thanks for your suggestion on the route to take Bruce..

=3DSUM(Fields!Fee.Value) / IIf(Count(iif(Fields!Fee.Value > 0, 1,
Nothing)) =3D 0, 1, Count(iif(Fields!Fee.Value > 0, 1, Nothing)))