Adding a Table to OrderAck

LexLawnjac · August 26, 2021, 3:26pm

Hey guys.
I’m having trouble adding a table to the OrderAck Report Data Definition. Below are the steps I took and where I am stuck.

Opened Report Data Definition and pulled up OrderAck. When to actions and Duplicated the report. The New report is called Order AckJC.
I then proceeded to add the table POHeader and excluded all the items I wanted to use for the new Report (picture provided). I also added a report relationship linking OrderRel to PO Header(Picture provided).

Relationship968×570 19.8 KB

SOFormBOL845×563 21.2 KB

TablePOHeader983×578 26.6 KB
I then Opened the Standard SSRS report in Report Bulder and saved it in our custom folder as SOFormBOL.
Opened Report Style Maintenance and opened Report OrderAck. Actions Copy Report Style and named it Standard - SSRS - JAX. on the Report Location I edited the report name from SOForm to SOFormBOL that I created in step 3 (Picture attached). The I clicked on Sync Dataset.
Opened up the SOFormBOL in report builder and the new data set from the POHeader table was there. I then dragged one item from the POHeader data set to the report to test it. Saved the report.
Opened Order entry, selected a random Order, and print previewed it with the new report style. Preview never came up so I checked the System Monitor and got the following error.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘POHeader’. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
Stack Trace:
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\RL3.2.200.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\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 142
at Ice.Core.SsrsReporting.SsrsRendererBase.<>c__DisplayClass12_0.b__0() in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:_Releases\ICE\ICE3.2.200.4\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 92
at Epicor.Hosting.Trace.TraceHandle.AddTimedIfEnabled(String traceFlag, Action actionToTime, Func2 messageBuilder) in C:\_Releases\ICE\ICE3.2.200.4\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 39 at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func1 timedAction) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 256
at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 150
at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 31
at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func2 reportsRenderer, Action1 fillSysRptLstRow, Action2 processReport) in C:\_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 50 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 301 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 226 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func2 executeCommand, Func`2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111
at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
at Erp.Internal.OM.SalesOrderAck.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:_Releases\ERP\UD10.2.200.4\Source\Server\Internal\OM\SalesOrderAck\SalesOrderAck.cs:line 612
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:_Releases\ICE\RL3.2.200.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 98
at Ice.Hosting.TaskCaller.ExecuteTask() in C:_Releases\ICE\RL3.2.200.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 57
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:_Releases\ICE\RL3.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 547

Any help would be greatly appreciated.

Mark_Wonsil · August 26, 2021, 3:31pm

There’s the problem. Read about it on this thread:

RDD SSRS Adding a UD table, Sync Dataset? - ERP 10 - Epicor User Help Forum (epiusers.help)

cfinley · August 26, 2021, 3:33pm

Yeah Mark is right. Sync Data Button = Bad.

Also your screenshot is not of your relationship that you made to POHeader.

LexLawnjac · August 26, 2021, 3:34pm

Sorry put wrong relationship. Here is the correct one.

LexLawnjac · August 26, 2021, 3:35pm

Alright, so would you recommend deleting everything and re-creating the report while not kitting the SyncDataset?

LexLawnjac · August 26, 2021, 3:36pm

Clicking*

LexLawnjac · August 26, 2021, 3:36pm

Attached the correct Picture.

cfinley · August 26, 2021, 3:40pm

I personally have tried to unravel that mess manually. Save yourself the time and recreate. It may be fixed in newer versions, but historically its never worked well.

LexLawnjac · August 26, 2021, 3:41pm

I’ll give it a go and updated you guys. Thanks for the help.

ckrusen · August 26, 2021, 3:43pm

Also, using a relationship of “Definition” will limit the original records to only those that have a matching POHeader record. So none of the lines for Stock items would show up.

cfinley · August 26, 2021, 3:46pm

Agree. I have had some weirdness with Definition only. I use Output across the board for all joins, and change the join type in the SQL query inside the SSRS report. I feel like most of my past issues were with weird temp datasets that aren’t actually tables.

ckrusen · August 26, 2021, 3:55pm

And another gotcha that will affect you at the RDD level, is excluding fields in the added table that are used in the joins.

A quick test that the RDD “works” (menaing doesn’t generate runtime errors, as opposed to not giving you the output you desire), is to make the changes to the RDD, duplicate the report style and update its DataDef value to the new RDD. Make no changes to the new RDL. The report should run identical to the source you copied it from. If you get any erros, they are in the RDD.

LexLawnjac · August 27, 2021, 2:30pm

Update.
1)I recreated the New RDD, changed the relationship to Output.
2)Then created the report style in OrderAck.
3)Next I opened the original SOForm and resaved it as SOFormBOL n the Custom Reports folder.
4)On the report style I pointed to my new data definition with the added tables and on the Report Location I changed the name to SOFormBOL. I DID NOT CLICK ON SYNC DATASET.
5) Ran a trial Print without changing anything and it came up just like the original.
Currently when I open the report in our customs report folder or when I download it to my desktop the new data source POHeader is not showing up under Datasets. How can i get the new table to come in?

Mark_Wonsil · August 27, 2021, 3:15pm

What field are you trying to add Jose?

cfinley · August 27, 2021, 4:59pm

The step the evil button tried to automate, was the step of going into your SSRS report dataset and adding the link/fields into the query. You can manually add this by finding your main dataset. Usually named something similar to the reportstyle. Right Click > Properties. Find the formula expression button for the query and manually add the link/fields in

SELECT T2.YourNewFieldHere FROM OrderRel_" + Parameters!TableGuid.Value + " T1 INNER JOIN POHeader_" + Parameters!TableGuid.Value + " ON T1.Company = T2.Company AND T1.PONum = T2.PONum

LexLawnjac · August 27, 2021, 6:23pm

Alright so I went to OrderHed and modified the Expression to the following

=“SELECT T1.RptLanguageID,T1.CardNumber,T1.NeedByDate,T1.OrderComment,T1.OrderDate,T1.OrderNum,T1.PONum,T1.Calc_BillToAddressList,T1.Calc_BillToContact,T1.Calc_CardNumber,T1.Calc_CustPartOpts,T1.Calc_FobDesc,T1.Calc_MultiNeedDate,T1.Calc_MultiRef,T1.Calc_MultiShipContacts,T1.Calc_MultiShipTo,T1.Calc_MultiShipVia,T1.Calc_PrcEmail,T1.Calc_PrcFax,T1.Calc_PrcPhone,T1.Calc_SalesPerson,T1.Calc_ShipToAddressList,T1.Calc_ShipToContact,T1.Calc_ShipToEmail,T1.Calc_ShipToFax,T1.Calc_ShipToPhone,T1.Calc_ShipViaDesc,T1.Calc_TermsDesc,T1.CurrencyCode_CurrDesc,T1.CurrencyCode_CurrSymbol,T1.Customer_ResaleID, T1.Calc_CurrSymbol, T1.Calc_MiscTotal, T1.Calc_LineMiscTotal,T2.Company,T2.ContractNum,T2.DisplaySeq,T2.DocDiscount,T2.DocUnitPrice,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.OrderComment AS OrderDtl_OrderComment,T2.OrderLine,T2.PartNum,T2.POLine,T2.PricePerCode,T2.Reference,T2.RevisionNum,T2.SalesUM,T2.SellingQuantity,T2.WarrantyComment,T2.XPartNum,T2.XRevisionNum,T2.Calc_ExtPrice,T2.Calc_LineDesc,T2.PartNum_PartDescription,T2.WarrantyCode_WarrDescription,T2.Calc_UOMForeignDesc, T2.Calc_FSAInstallationDesc, T2.Calc_FSAInstallationType, T2.Calc_FSAInstallPrice,T2.Calc_LineTaxAmt,T2.Calc_FSAInstallationRequired, T3.NeedByDate AS OrderRel_NeedByDate,T3.OrderRelNum,T3.Reference AS OrderRel_Reference,T3.ReqDate,T3.SellingReqQty,T3.ShipViaCode,T3.Calc_ResaleID,T3.Calc_ShipToAddressList AS OrderRel_Calc_ShipToAddressList,T3.Calc_ShipToContact AS OrderRel_Calc_ShipToContact,T3.ShipViaCode_Description,T3.Calc_OrderRelCounter,T4.Calc_ExtPrice AS RptLiteralsCalc_ExtPrice,T4.Calc_OrderTotal AS RptLiteralsCalc_OrderTotal,T4.Calc_SalesPerson AS RptLiteralsCalc_SalesPerson,T4.Calc_ShipToContact AS RptLiteralsCalc_ShipToContact,T4.RptLiteralsDiscount,T4.FaxNum AS RptLiteralsFaxNum,T4.FOB AS RptLiteralsFOB,T4.NeedByDate AS RptLiteralsNeedByDate,T4.OrderDate AS RptLiteralsOrderDate,T4.RptLiteralsLLine,T4.OrderQty AS RptLiteralsOrderQty,T4.OrderRelNum AS RptLiteralsOrderRelNum,T4.PhoneNum AS RptLiteralsPhoneNum,T4.Reference AS RptLiteralsReference,T4.ResaleID AS RptLiteralsResaleID,T4.RevisionNum AS RptLiteralsRevisionNum,T4.RptLiteralsLCrdNum,T4.RptLiteralsLCustPart,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLPhone,T4.RptLiteralsLLNMSC,T4.RptLiteralsLOrderAck,T4.RptLiteralsLORMSC,T4.RptLiteralsLORNUM,T4.RptLiteralsLOurPart,T4.RptLiteralsLPage,T4.RptLiteralsLPONum,T4.RptLiteralsLPriPer,T4.RptLiteralsLPrtDes,T4.RptLiteralsLQty,T4.RptLiteralsLShipTo,T4.RptLiteralsLSHSHC,T4.RptLiteralsLSHSHM,T4.RptLiteralsLSLSHE,T4.RptLiteralsLSoldTo,T4.RptLiteralsLSubTotal,T4.RptLiteralsLTerms,T4.RptLiteralsLYURPOLN,T4.RptLiteralsSalesKit,T4.UnitPrice AS RptLiteralsUnitPrice,T4.Warranty AS RptLiteralsWarranty,T4.ShipViaCode AS RptLiteralsShipViaCode,T4.OrderLine AS RptLiteralsOrderLine, T4.RptLiteralsSeeBelow, T1.Calc_TaxMethod, T4.RptLiteralsLTotalTax, T1.DocTotalTax, T1.DocOrderAmt, T1.Calc_TotalTaxAmt, T4.RptLiteralsLInstallation, T4.RptLiteralsLInstallTotal, T5.ShipViaCode as ShipDtlShipViaCode
FROM OrderHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID LEFT OUTER JOIN POHeader_" + Parameters!TableGuid.Value + " T5 ON T3.Company = T5.CompanyAND T3.PONum = T5.PONum AND T3.OrderNum = T5.OrderNum"

The Bold is what I added. Then added it to the fields as a query field. Picture below

But I am still erroring out with the following error.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘OrderHed’. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
Stack Trace:
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.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 497
at Ice.Core.SsrsReporting.SsrsRendererBase.ConfigureReportServerForReport(String reportPath, ParameterValue[] parameters) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 163
at Ice.Core.SsrsReporting.SsrsSingleDocumentRenderer.<>c__DisplayClass1_0.b__0() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsSingleDocumentRenderer.cs:line 49
at Ice.Core.SsrsReporting.SsrsRendererBase.<>c__DisplayClass12_0.b__0() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:_Releases\ICE\ICE3.2.300.3\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 92
at Epicor.Hosting.Trace.TraceHandle.AddTimedIfEnabled(String traceFlag, Action actionToTime, Func2 messageBuilder) in C:\_Releases\ICE\ICE3.2.300.3\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 39 at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func1 timedAction) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 264
at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 158
at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 31
at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func2 reportsRenderer, Action1 fillSysRptLstRow, Action2 processReport, Func3 filterTableAttachmentsFunc) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 57
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 303
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 228
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func2 executeCommand, Func2 executeReader, SqlObjectsCreated sqlObjectsCreated) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111
at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
at Ice.Core.RptTaskBase1.XMLClose() in c:\_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 216 at Erp.Internal.OM.SalesOrderAck.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_projects\ERP\RL10.2.300.0\Source\Server\Internal\OM\SalesOrderAck\SalesOrderAck.cs:line 608 at Ice.Core.TaskBase1.StartProcess(Int64 instanceTaskNum, String outputFileName) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 47
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in c:_Releases\ICE\RL3.2.300.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 98
at Ice.Hosting.TaskCaller.ExecuteTask() in c:_Releases\ICE\RL3.2.300.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 57
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in c:_Releases\ICE\RL3.2.300.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 577

Did I do something Wrong???

LexLawnjac · August 27, 2021, 6:26pm

Adding the POHeader table as shown in my last reply.
Thanks for the help.

cfinley · August 27, 2021, 6:42pm

Are you able to enable remote errors? are you on prem?

You are missing a space between T5.Company and the AND in your join statement

LexLawnjac · August 27, 2021, 6:48pm

Adding a Space. I am on Prem.

LexLawnjac · August 27, 2021, 6:48pm

Adding a Space. I am on Prem.