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

avatar 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,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

avatar image

1.7k 2 3 6

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 27, 2009 at 07:18 PM

Seen: 3357 times

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

Copyright 2016 Redgate Software. Privacy Policy