At a loss how to troubleshoot this one so I’m hoping one of you fine folks can nudge me in the right direction.
A highly customized ARForm has stopped working with only the error message “Query execution failed for dataset ‘ARForm’” This really doesn’t help much. And because it doesn’t generate the report tables with the GUID after them, I cannot run the query in SQL query analyzer to see what has changed.
My suspicion is that maybe a new field has been added to one of my tables that now is finding ‘ambigious’ in the query.
I’m at a loss how to troubleshoot this other than revert back to the original RDD ARForm instead of my 7th generation one and start adding more tables slowly until it breaks again. If anyone has experience working with the ARForm RDD, it is extremely slow to work with and given the large number of tables/fields needed, it takes forever to remove unneeded columns/labels in order to not go over the limit.
Is there any documentation that sums up added fields or database changes between the two versions I’ve been working with?
Custom RDD’s should work with the built-in report. I’d do the following:
Duplicate your RDD (we’ll refer to this as RDDx)
Make a new style using RDDx, and the default RDL.
Try to run that.
If it doesn’t work, slowly remove fields of the tables you added to RDDx. First try doing this by marking all fields (except the ones used in relationships) as excluded. If that doesn’t work, try removing the tables you added, one at a time.
Finally, now that the report fields and query is cleaned up. Duplicate the ARForm Data Def and strip out everything that isn’t needed.
My thinking is that the RDD is super slow to work with so getting everything out of there that isn’t needed will make it easier to work with in the future.
Do you see anything wrong with my plan? This is my first upgrade and want to put the effort in now to ensure future major version upgrades go more smoothly.
When you do an upgrade, Epicor will inject their new Data Sources and Relationships into your RDD. Sometimes you need to remove them or tweak them, sometimes a Left Join could duplicate your data, if snuck in.
Here is my Customized ARForm Report 10.1.600 v 10.2.400. Before and After Upgrade.
However Epicor will not modify your .rdl file and add anything new into that! But if let’s say your rdl uses Calculated_Something and Epicor changed the logic, renamed it or removed it - you have to uplift it manually. (CC @JeffLeBert)
What I do usually is grab the base and compare it using www.winmerge.org and then make a decision, sometimes its easier to re-customize from Base.
But definitely look at your RDD first.
As you can see the ARForm has a gazillion changes.
One more thing… my idea for trying to get the RDD to work with the basic RDL, was that the report rendering takes RDL’s query and applies it against the dataset. If the RDL’s query was very basic, any failure to render would indicate that the dataset isn’t being created, and therefore the problem is in the RDD.
Just curious, if you submit the report (not print or preview), does it succeed?
And my first guess would be along the lines of what you said about a new field in 10.2.400 being ambiguous - or possibly conflicting with an existing field name.
And one last thing… Ive never seen the RDL’s query SO customized. I was under the impression that typically, one would just add the new fields to SELECT parts. Or occasionally adding joins for the newly added tables. And seeing your query start with a SELECT DISTINCT SUM and that your tables aren’t named T1, T2, etc…, really shows you massively manually tweaked that query is.
I didn’t work on the migration so anything I saw is just guesses.
Auto fixing the RDL is the same problem we get with Sync Dataset, so I don’t think we could or should do it. For trivial reports you could do it, but monsters like ARForm need a human. We would love to be able to convert everything with a magic wand, but that is impossible.
Given that, when you customize something, document your changes and be prepared to make them again if necessary. (Usual caveat about speaking for myself, not my company. )
Thanks for all the suggestions here. I feel I’m way over my head on this one, yet I always seem to find a way to get these things fixed after some amount of effort.
I’ve taken the new 10.2 RDD and added my other tables and relationships that my HIGHLY customized Invoice uses.
I’ve run the stock SSRS report using this new RDD and the base report prints so that tells me the RDD is NOT the culprit.
So that leaves the RDL, but again, it’s highly customized. (The invoice has been changed to look like the client’s previous Invoice, plus the Pack Slip is added at the bottom so they both can be emailed using a 3rd party tool).
The only thing I can think of to try for my next step is to take the Base report, blow everything away on the report itself, keeping the datasets, then copy paste my report fields back into the report and adjust the dataset to include my new fields. This seems super time consuming and I remember it was almost impossible to get everything to fit on the page properly.
Any other suggestions for my next step? Again, my RDD seems to be working as I can use it to print the Base ARForm.rdl. And because I can print it, I can archive the data. Looking at that data in SQL server shows that indeed the new fields I need are present in the dataset.
Also - have you tried setting the archive period on the report to 1 day. running the report, getting the report GUID and running the report direct in SSRS.
Upon reading this, I thought this was some super PRO tip that not many people know of. Unfortunately, it just gives the same error that I get in the System Monitor.
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘ARForm’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Are the remote errors easy to set up and do they give an actual SQL execution error?
Regardless, you’re right about the added data sources. I’ve actually taken the base 10.2 RDD and added my 3 tables and their relationships and the form is now working. The only issue is I have one field left to add in the dataset query and it’s a SUM so I’m going to have to add a GROUP BY on all the other fields in the query, which is going to take some time.
Thanks for all the guidance. I’ve been burning through days of effort on this nasty report.
Glad your getting there - in order to test the SQL, copy the expression from the ARForm dataset in SSRS, open an SSMS session and paste it into a new query, replacing Parameters!TableGuid.Value with the GUID from system monitor.
omg… I forgot about this. I had mistakenly thought that a GUID only was generated when the report actually rendered. Which didn’t make sense given it’s just a data call.
You’ll also need to clean up the expression as it is a formula that evaluates to a string. Take out the leading =", and any other areas that change from string to variable. Basically, replace
" + Parameters!TableGuid.Value + " with the GUID
so that
OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
This has totally saved me. Show me a ‘meaningful’ error message and I can fix it. I’m proficient with SQL.
Up until now I just got “Query execution failed for dataset ‘ARForm’” so all I could do was find something that worked, then slowly add to it. Which is very time consuming when you have a highly modified form.
Thanks again to everyone. Being able to debug in SQL query analyzer has saved me.
)
