Epicor REST API - Report now broken

We have used Epicor objects directly for awhile now to both print reports and edit data. We are now switching to using the REST API instead for a new project. We’ve been able to get it working successfully but are having a strange issue that we’d like to get the community’s input on.

The symptom that we’re seeing is that the SSRS report generated via a GUID returns a failure saying that “A column named ‘ResaleID’ already belongs to this table”. The odd thing is that the report was working fine before and wasn’t changed. Odder still is that we have two instances of Epicor running on our application server (Epicor and EpicorTest) and they have separate task agents, app pools, databases, etc. and they both return the same error.

This first happened last week and we tried restarting everything (task agent, app pool, regenerated data model) and finally rebooted the entire Epicor app server and nothing made it work again. We then restored the whole VM to a point before the symptoms began and it started working again. Yay!

Now this morning, it is having the same exact symptom.

A little more context:
This is the ARForm report and we use a customized version of the original. The two reports share a Data Definition and the original report still works.

A common thing that would have happened in both instances was that we tried to generate the report via the REST API using “RunDirect”. Since it’s complaining about a field already existing, my hunch would be that our REST API call is leaving a session active or not cleaning up data tables or something but I can’t make that square with the fact that both instances are failing and that a VM reboot didn’t fix it.

Any thoughts or leads would be most welcome.


P.S. Below is the relevant code where we call the REST API.

dynamic ds = new
    ARInvFormParam = new List<dynamic>()
            InvoiceNum = invoiceId,
            WorkstationID = dbReportId,
            AutoAction = "SSRSGenerate",
            AgentSchedNum = 0,
            AgentTaskNum = 0,
            AgentID = "SystemTaskAgent",
            ReportStyleNum = 1004,
            //DraftCopy = false // this marks as printed. We are printing after generating, but this was hard to find.        }
    // generate report directly
    await _api.BoPostAsync("Erp.RPT.ARInvFormSvc", "RunDirect", JsonConvert.SerializeObject(new { ds }));
1 Like

Could you publish the json output of this ?

Probably doesn’t matter, but let’s have a look.

Also, do you get the same error with SubmitToAgent ?

Thanks for the reply Kevin!

JSON output:

  "ds": {
    "ARInvFormParam": [
        "InvoiceNum": 225343,
        "WorkstationID": "5af27354-3684-4673-9597-457339642249225343",
        "AutoAction": "SSRSGenerate",
        "AgentSchedNum": 0,
        "AgentTaskNum": 0,
        "AgentID": "SystemTaskAgent",
        "ReportStyleNum": 1004

As far as trying SubmitToAgent, unfortunately the only way I know to test is to restore our Epicor App VM which will have to happen after hours.


I don’t think the problem lies with the REST call in any fashion. I think something else is wrong.

@gpayne you know about the rdd stuff a bit right?

@klincecum I do not play much with RDDs. I would use the report guid to check what data the temporary report tables have made and to make sure the guid was changing on each request.

1 Like

Please can I see your RDL?

Thanks again for the responses.

So I haven’t been able to verify the GUID because it doesn’t seem to get to the point where it creates the tables. My data point on that is if I run the report from within Epicor and select a Week for the Archive period, I don’t see any new tables show up in the database. My direct testing from within SSRS (getting the same error as the task agent gives) is by using a dataset/GUID from before it broke.

I have attached the files. There are several subreports in the overall report.

ARForm_Reports.zip (144.9 KB)

Why aren’t you using the RDD for this?

LEFT JOIN Epicor.Erp.Customer T6
    ON T1.Company = T6.Company
		AND T1.CustNum = T6.CustNum"

This may be causing your duplicate error.

LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6 ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum

or just use link tables for your Country field.

In your ARForm_CustomerDetails subreport, there is custom code adding content to a datatable, including the ResaleID field. Might want to check into that.

It is in the Value expression for textbox RptLiteralsLEMaila2


@aarong - That is a great question. I don’t know why it was implemented that way. I changed to the latter and it does not seem to make a difference.

I’m not familiar with link tables. Could you expand on that?

I had seen that when looking for what the original message was flagging on but I remove it and all usages of ResaleID in the CustomerDetails subreport and it didn’t make any difference.

As an FYI, I’ve pasted the entire error message from Epicor below. Frustratingly, I have not been able to find any Hidden expressions in any of the reports/subreports that seem to be the smoking gun.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
System.Web.Services.Protocols.SoapException: The Hidden expression for the tablix ‘Tablix1’ contains an error: A column named 'ResaleID' already belongs to this DataTable. ---> Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The Hidden expression for the tablix ‘Tablix1’ contains an error: A column named 'ResaleID' already belongs to this DataTable.
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String Format, String DeviceInfo, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 148
   at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func`1 timedAction) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 88
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReport(String ssrsRenderFormat, String printProgram, Boolean ignorePageSettings) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 247
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 408
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.ARFormReportProcessor.ProcessReportParts() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ARFormReportProcessor.cs:line 42
   at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func`2 reportsRenderer, Action`1 fillSysRptLstRow, Action`2 processReport, Func`3 filterTableAttachmentsFunc) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 305
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 229
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func`2 executeCommand, Func`2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 229
   at Erp.Internal.AR.ARInvForm.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_releases\ERP\ERP10.2.700.0\Source\Server\Internal\AR\ARInvForm\ARInvForm.cs:line 1445
   at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 83
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE3.2.700.7\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 117
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE3.2.700.7\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 59
   at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
   at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 454

Thanks all for chiming in,

There are a couple Hidden expressions on rows buried in the Details group that are related to ResaleID.


Side note… this is one wild RDL! :smiley:

I’m sure it’s obvious by now that it’s an inherited report :grin:

I’m trying to figure out all the pieces myself.

Thanks for the tip. What is that code.obj.xxxxx snippet doing? Where is the function defined?

Good question. Typically it’s defined in the Code section of the report itself. I’m not seeing these ResaleID functions.

I think it’s also possible to reference external DLLs. Epicor does that for some reports, but I don’t know much about it.

This doesn’t appear to be standard Epicor, because the ResaleID function calls in those Hidden Expressions are not in the default ARForm RDL.

Looks like there is a reference listed.

It must be in CLSEpicor. I’ll try removing the Hidden logic for now to see if that fixes the issue.

Here is what it’s doing:

public string GetCustomer_ResaleID(int InvNum)
  if (ARForm.ARForm_DataTable == null)
    return "";
  this.ARForm_view = ARForm.ARForm_DataTable.DefaultView;
  this.ARForm_view.RowFilter = "Calc_InvoiceNum = " + (object) InvNum;
  DataTable table = this.ARForm_view.ToTable();
  string customerResaleId = "";
  if (table.Rows.Count > 0)
    customerResaleId = table.Rows[0][0].ToString();
  return customerResaleId;

Found using DotPeek. There are other similar tools. ILSpy, etc.

Seems complicated when you could just query the ResaleID value directly from the dataset. Maybe that is old, from an early version of Epicor?

In the default ARForm there is a Hidden Expression that looks like this:

=((Fields!Calc_CustResaleID.Value = "") OR First(Fields!Calc_Localization.Value, "Company") <> EE") AND (Fields!Calc_CustShipToEORINumber.Value = "")

Well, I went silent for a day and it’s because… I came to work Tuesday morning to find it working fine. :man_facepalming:

We haven’t seen it fail since. The report definition has not changed between working and not so it doesn’t seem like that is our culprit.

I am now doubting that restoring the Virtual Machine last time did anything productive. Wondering if our REST method of calling reports (we are using multiple tasks to call up to 3 instances of the report simultaneously) is causing issues. Then eventually it gets purged from the system?

I had this same error yesterday printing AR Invoices. The form we had used for the last 8 years suddenly stopped working with the error:

Exception of type ‘Ice.Core.SsrsReportService.ReportExecutionSoapException’ was thrown.
SOAP Fault: The Hidden expression for the tablix ‘Tablix1’ contains an error: A column named ‘ResaleID’ already belongs to this DataTable.

The Epicor default AR Invoice form did work, but our form would not print any invoices. Verified with AR that there was not update to Epicor or any Customer tables.

Get this, I had the same resolution. Spent half the day trying to figure this out and today it just started working.