SSRS: field shows correct in query but wrong in report preview

Question

I have the following query:

SELECT TOP ( 10 )
        EMPLOYEE ,
        NAME ,
        SUM(QTYFINISHED) AS QTY ,
        SUM(HOURS) AS REALTIME ,
        SUM(PROCESSTIME * QTYFINISHED / PROCESSQTY / 60) AS CALCTIME ,
        SUM(PROCESSTIME * QTYFINISHED / PROCESSQTY / 60) / SUM(HOURS) AS EFFI
FROM    EMPLOYEE
GROUP BY EMPLOYEE ,
         NAME
ORDER BY Eficience DESC

When I execute it in the 'Query Design' I have the right values:

  Employee  Name     QTY     REALTIME    CALCTIME    EFFI
      2     Peter    10        10           5         0,5
      3     John     10        10           10         1
      4     Thomas   10        12           9         0,75
      ...

But when I add the fields in a table in the report the Preview make some mess between the fields:

  Employee  Name     QTY     REALTIME    CALCTIME    EFFI
      2     10        10           5          0,5    #Error
      3     10        10           10          1     #Error
      4     10        12           9          0,75   #Error
      ...

If I delete the field 'Name' from the query (and of course, from the report), I get the right values in the Query Design and the Report Preview.

note: the real query include a JOIN and fields are referenced with the table namespace, but I let it out due readability reason because I don't believe make any difference.

Answer 1

You must delete the filename.rdl.data in the project folder. This is a feature for SSDT. After deleting the rdl.data file, it will create a new one on the next viewing preview with the proper query and it will remove #ERROR.

Answer 2

Another possible way, that will make things easier in the future, is to create an external tool, just like described here:

• Go to Tools > External Tools...

• Add a new tool with the following settings:

  • Title: Clear Report Data Cache
  • Command: "%WinDir%\System32\cmd.exe"
  • Arguments: /C DEL /S /Q "$(SolutionDir)*.rdl.data"
  • Check options:
    • Use Output window
    • Close on exit

Now whenever you want to delete your report data cache, just go to Tools > Clear Report Data Cache.