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.

Cyclic

Well, obviously the fields are shifted one to the left. I guess you need to update the SSRS data set to reflect the current output of the query. – Acupuncture

my bad! Deleted the .rdl.data and it's working. Sorry for the stupid question :(, please, add the answer for give you the points ;) – Cyclic

I found that if you hit the refresh icon on the report it will reload the cached data and save you the trouble of deleting the file. – Ary

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.

Canter

I believe it was designed as a feature. If you have a long running query it is handy to be able to preview the report over and over without having to run the query each time. Unfortunately it is not intuitive and sure caused me confusion until I learned how it works. – Ary

Oh i see, i will change my answer to reflect that. – Canter

I also got the same error and i got fixed by the same. thanks a lot! – Weathersby

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.

Sympetalous