x

Computing Average in Reporting Services exclude values less than 0

Hello - I have this for a textbox in a SQL Server Reporting Services report. This is to take the total average

=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,0))/(Count(Fields!Q1Rating.Value) - Sum(iif(Fields!Q1Rating.Value<0,1,0))) +                     
Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,0))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,0)))+                    
Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,0))/(Count(Fields!Q3Rating.Value) - Sum(iif(Fields!Q3Rating.Value<0,1,0))) +                    
Sum(iif(Fields!Q4Rating.Value>0,Fields!Q4Rating.Value,0))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,0)))/4                    

I don't want to divide by 0 because if the value for Q3Rating is -1 then it will be displayed as 0 and I want to exclude those values. It would be -1 if someone choose N/A and I am representing N/A as -1.

more ▼

asked Oct 27, 2009 at 07:18 PM in Default

Adam Olson gravatar image

Adam Olson
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

So, If you do not want to use values 0 or less, why not just exclude them as your first if does?

Explain more about what you are after and you will get a response.

You can always set it to 1. This way, you will avoid divide by zero errors and just come out with the original value.

=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,1))/(Count(Fields!Q1Rating.Value)
- Sum(iif(Fields!Q1Rating.Value<0,1,1))) + Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,1))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,1)))
+Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,1))/(Count(Fields!Q3Rating.Value) -
Sum(iif(Fields!Q3Rating.Value<0,1,1))) +Sum(iif(Fields!Q4Rating.Value>0,Fields!
Q4Rating.Value,1))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,1)))/4
more ▼

answered Nov 16, 2009 at 12:36 PM

RickD gravatar image

RickD
1.7k 1 1 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x94

asked: Oct 27, 2009 at 07:18 PM

Seen: 2951 times

Last Updated: Nov 16, 2009 at 01:30 PM