December 8, 2014

Adding a new field to an SSRS Report and seeing #Error on preview

Written by

I don’t know how many times I’ve done this, the scenario goes something like this, your boss emails you asking for some new field to be added to a report. You spend quite a bit of time adding the necessary logic to the underlying dataset, everything looks great from SQL side of things. Next you update the dataset, refresh the field list so far so good, finally you add the new field to the report hit preview and everything fails, you see the dreaded #Error.

In the example below I have a profit and loss report, what I need to do is highlight those months where we have had three pay cycles (fortnightly pay) as it will effect the expense lines. In order to achieve this I’ve added a flag to the underlying dataset that picks out the three pay months. The idea is that for a three pay months I’ll show the month name + “(3 pay month)”

So if August was a three pay month then:

July
August (3 pay month)
September
October
etc etc

After adding the expression to the month field I get:

ProfitAndLossByFinancialMonth-Error
Figure: Adding a new field to a report

So how to fix this error? Just delete the *.data file that’s in your report project file. I can’t believe that this “bug” is there and it catches me out every few months when come back to edit and existing report.

Reporting-Service-Data-File
Figure: Delete the *.data file and everything should work again

Alternatively you can also just “refresh” in the preview, such a simple thing but so easy to overlook when trying to fix the dreaded #Error

Reporting-Service-Refresh
Figure: Refresh when in preview

Now that I’ve refreshed my dataset the report now looks like this:

ProfitAndLossByFinancialMonth-Working
Figure: Three pay month showing correctly

 

Leave a Reply

Your email address will not be published. Required fields are marked *