SSRS Upgrade - {field} does not belong to table - any solutions?

Epicor ERP 10
1

I read the posts and tried to get around errors in our reports with upgrading from 10.1 to 10.2. We have several customer facing reports that break in 10.2.400, prefer not contacting customs group or doing a complete rewrite (EpicCare solutions).

I took a small report just to see if I could get a workaround - haven’t found it. What I did:
(sample is the Job Pick List; error Column ‘Calc_BCPCID’ does not belong to table JobBin )

  • Looked at the history in the report folder zip - only change was adding PCID
  • RDD for standard had BCPID as a calculated field in JobBin
  • Added that field to our custom RDD, saved
  • Ran a compare on query in standard RDL versus my custom RDL
  • Added Calc_BCPID and PID to the query and query fields in my custom to match the standard
  • Uploaded the changed report and verified it overwrote
  • Still getting the same error - closed out of everything and every tried a app server recycle, same error.

Only 5 of our 20 custom reports are erring out - unfortunately invoice, quote, and sales order acknowledgment are in the failing group. They are highly customized and customer facing…

I’d greatly appreciate the help if you’ve been able to solve something like this!

Thanks!
Jenn

2

If reports are calling out custom RDD’s, have you already tried rebuilding, those RDDs from scratch?
It’s been pretty common for me to have to rebuilld failing custom RDDs after an upgrade.

3

The field Calc_BCPID has to do with Barcodes. Do any other reports that have barcodes on them work?

But more surprisingly, “Calc_xxxx” fields in an RDD are kind of “built-in”. They hold a value that is calulated during the RDD process. For this one, I’d guess the Calc_BCPID is equal to the PID with some framing characters added (as required by the barcode). For example, if using code39 an asterisk is required at the start and end. So if PCID = 1234-5678, Calc_BCPID would be set to *1234-5678* But that;s all handled by the RDD’s underlying code.

I know that one can add them to an RDD, but unless you edit some underlying code, how does the RDD know what the value should be?

4

We added a bunch of tables and relationships in our customer facing RDDs, so I was trying to avoid rebuilding them from scratch. Not concerned about the amount of time as much as the regression testing if we start from base again.

5

Even when field(s) are “std, built-in”, if inlcuded in a custom RDD, still a good chance you need to rebuild the RDD.

i.e. if the standard version of report(s) are working, copy their RDDs and duplcate every setting from
your old, custom RDD(s).

Also, are you able to open the RDL file(s) in report builder?
I’ve seen a few cases of invalid datasources after an upgrade too.

1 Like
6

I can open it in report builder, just can’t get a GUID because it fails to build (even on generate only). Looks like I’ll start with the RDD rebuild and go from there.

7

Its a pain, but here are my steps:

  1. make a new copy of the base report
  2. Change the RDD to you custom RDD
  3. Run the report
  4. Use the the GUID to test you custom version (and view the SQL tables if you can)

Note the calculated fields may have a new name that is similar but different (like Calculated_BCPCID). Or it may have moved to a new table.

8

Thanks Jason.
I have been viewing the SQL tables - it’s a big help in understanding the reports :slight_smile:

9

And for those in the Cloud who do not have access to the SQL tables, you can still use the Report Style function under the Actions menu that allows you to download the data as an XML file (ala Crystal reports) to see the data built for that particular RDD.

10

Wanted to post what my solution will end up being. I was able to recreate just the RDD and have it read the report properly.

That being said, the RDD has got to be the slowest screen in Epicor. My 1st report was the smallest / easiest. Only 5 extra tables, 5 extra relationships, then going through each of the Exclusions lists and making sure I have everything included that is needed. Even with the filters on for the checkbox, it took a considerable amount of time. Now on to all the other reports, and they wonder why we don’t upgrade monthly!

I heard a rumor RDDs will eventually be going away - not soon enough in my book :wink:

Jenn

1 Like