Search code examples
reporting-servicesssrs-2008

SSRS report ended with an error

I used this expression =Avg(Fields!AncinitetAntalDag.Value) to get Average of seniority per employees , But i get #Error and i dont exactly why ! and i'm struggled for hours to find out how can i solved this ! Can someone please point me in the right direction?

Design :
ds


Preview :
enter image description here


Query Design :

enter image description here


Query:

SELECT
(CASE
WHEN u.ResignationDate IS NOT NULL THEN
CONVERT(varchar(11), DATEDIFF(day, WorkStartDate, ResignationDate)) 
ELSE
CONVERT(varchar(11), DATEDIFF(day, WorkStartDate, GETDATE())) 
end) as SeniorityViaNumberDay
Solution

  • You've converted the value from DATEDIFF to a varchar. You can't average this. Try not converting it and just using the number of days returned from your DATEDIFF.

    Her's the query simplified too..

    SELECT DATEDIFF(day, WorkStartDate, ISNULL(ResignationDate, GetDate())) AS SeniorityViaNumberDay