Custom SSRS Invalid object name (but not a GUID)

Epicor ERP 10
1

We have a customized version of the POForm. We have added the PartRev table so that the drawing number can be displayed. All works great in PROD. However, the reference to the table is hard-coded; when I move the report to a new location, I get the following error: Invalid object name 'Epicor102.erp.PartRev’

I have done the following:

  • Created a relationship in the RDD between PODetail and PartRev (the PartRev table was in the RDD, but there was no relationship with any other table)

  • Created a new copy of the report style and changed the RDD to the RDDNew (the one with the relationship)

  • Synched the Dataset

  • Downloaded the .rdl and edited the last bit of POHeader query (in the .rdl)

From this:

ON T1.RptLanguageID = T4.RptLanguageID left join Epicor102.erp.PartRev T5 on T5.PartNum = T2.PartNum and T5.RevisionNum = T2.RevisionNum left join Epicor102.erp.PODetail T6 on T6.Company = T2.Company and T6.PONum = T2.PONum and T6.POLine = T2.POLine"

To this (removed the “Epicor102.erp.”):

ON T1.RptLanguageID = T4.RptLanguageID left join PartRev T5 on T5.PartNum = T2.PartNum and T5.RevisionNum = T2.RevisionNum left join PODetail T6 on T6.Company = T2.Company and T6.PONum = T2.PONum and T6.POLine = T2.POLine"

Now the report fails with this error: Invalid object name 'PartRev’

What is the process for incorporating the PartRev table into the POHeader query so that I can move the report between environments?

Thanks in advance!
—sam

2

I see that PartRev is not part of the base RDD.

Did you set the relationship PODetail->PartRev to Output?

Also, the table should get dumped with the same GUID as the rest of the tables, so you should use the same pattern like <PODetail_ + Parameters!TableGuid.Value + TX> and <PartRev_ + Parameters!TableGuid.Value + TX>

2 Likes
3

If your RDL is querying the PartRev table from the actual DB, you must have created a Datasource specific to it. And I assume it was pointing to the test environments DB. This datasource will need to be modified to point to the table in the PROD DB.

Open the RDL, select the datasource for the PartRev, open its Propoerties, and use the Test Connection.

4

Thanks so much. Adding the same +Parameters! bit did the trick.

1 Like
5

Thanks for looking at this.