Troubleshoot SSRS "Query execution failed for dataset 'CashTransaction'

[Adam (Adam Szymanski)]

I’ve browsed through the forums, but I’m not finding what I’m looking for. We are upgrading from .400 to .700 and running into a custom SSRS issue. Our ARForm throws the error “Query execution failed for dataset ‘CashTransaction’”. I tried deleting the dataset in the report, but saving errors and mentions a subreport uses this data.

SO, how do I go about troubleshooting this? When/where does the data for this query get generated in Epicor? I’ve looked over the RDD that feeds this report (ARForm_TSG), but I don’t see any reference to a “TSG_CashTransaction” table, or the specific fields in the query.

I obviously want to fix the issue, but I’m also curious what happens between the RDD and the data that feeds these SSRS reports. Is there another layer that I’m missing that I should be looking at?

[jkane (John Kane)]

You need to check the SSRS Report Server logs on the server to get the “full” error. Do you have access to the server where the report server is?

[Jonathan]

Do you have both environments so you can compare the report data definition in 400 vs 700?

[Adam (Adam Szymanski)]

I do have access to the SQL server, but I’m not sure where the log is located.

[jkane (John Kane)]

probably C:Program Files/Microsoft SQL Server/MSRS11.MSSQLSERVER/Reporting Services/Log Files

[ckrusen (Calvin Krusen)]

FWIW - it’s odd that the query expression doesn’t refer to a GUID parameter.

[Michael_Ramsey]

This was the post I was aware of speaking to similar issues.
In my case, I was able to upload the reports from local files and that fixed the issue.

Report issues with upgrade from 10.1.500 to 10.2.700

Hello, we are in the process of upgrading form 10.1.500 to 10.2.700 and ran into issues with three reports: Sales Order Acknowledgement, AR Invoice and MICR Check. We were able to resolve our MICR check issue internally, but are still having issues with the other two. We are getting errors similar to this and Support is not helpful: “Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:System.ArgumentException: Column ‘Calc_AttributeSetShortDesc’…

[Adam (Adam Szymanski)]

This was helpful and also caught my eye, but your post caused me to ask why… ended up being a customization that Epicor put into our system years ago (a view created on the reports db) and I didn’t copy that view to the new db.

[Adam (Adam Szymanski)]

In the end, I re-uploaded the SSRS manually through the web gui, no go. I deleted and re-imported the RDD, compared with our current .400 version, nothing.

@ckrusen helped me rethink the issue. Years ago Epicor installed a custom view on our reporting db because of some customization that they couldn’t get into the SSRS. I copied the view over to our new db and it moved thing along, but now had new errors:

The information in Epicor’s system monitor was pretty basic “Query execution failed…” The best way to troubleshoot this, I found, was to launch the MS SQL Server Management Studio (select the ‘reporting services’ server type at login), right-click server → properties → advanced → security → “Enable Remote Errors” = TRUE. Re-run the report and now the System Monitor listed the fields that my custom report was looking for, but were no longer available. I cleaned up the SQL in the custom report and was good to go.