Filtered Table makes average AVG incorrect

Last Post 20 Feb 2009 01:26 PM by QuestionGuy. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
QuestionGuy
New Member
New Member

--
20 Feb 2009 11:35 AM
I have a 2005 RS. I have 1 stored proc. I want to add 4 tables to the report and on each table specific filter so that I don't have to run the proc 4 times with different criteria. I have 1 group so that I can see the average overall employee appraisal rating for each employee. After I put the filter on the table, the avg function will ONLY return whole numbers and .00, even if I specify the format on the RS, or specifically return the value as DECIMAL(9,2) from the proc. For exmaple, the average should be 4.26, but it returns 4.00. If I remove the filter, it will return it as a decimal, but of couse I have incorrect data because I need the filter to be there. (there are 4 different areas that I need to display)

I could add a filter on the group only and not that table, and then it will be in the correct format. But, at the end of the report I also need to have an over all average for the entire table...so that's while I need to put the filter at the table level and not the group level.

I have a different report where the filter does not make the average incorrect, but that report has 2 groups instead of 1.

Is this a bug? I have tried to recreate this report from scratch several times.
QuestionGuy
New Member
New Member

--
20 Feb 2009 01:26 PM
Maybe I was the bug, not sure. In my proc I changed UNION to UNION ALL since I didn't want to filter out duplicates. But there were thousands of rows so I am still surprised the average came out a whole number for each section before I made the change.
You are not authorized to post a reply.

Acceptable Use Policy