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:
August (3 pay month)
After adding the expression to the month field I get:
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.
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
Now that I’ve refreshed my dataset the report now looks like this: