PurAgent into OpenPO RDD SSRS

mlp · March 26, 2025, 7:55pm

Hello.

I modified OpenPO RDD to bring in the PurAgent table so I can get the actual name of the buyers. Linked it to POHeader via Company and BuyerID.

Used this in my query:

="SELECT T1.POType,T1.Calc_IncludeMfgPart,T1.Calc_IncludeSupplierPart,T1.Calc_RPTUSERID,T1.Vendor_Name,T1.Vendor_VendorID, T2.LineDesc,T2.MfgPartNum,T2.PartNum,T2.POLine,T2.PONUM,T2.PUM,T2.UnitCost,T2.VenPartNum,T2.Calc_DspCostPerCode,T2.Calc_MfgID, T2.PONum, T3.AssemblySeq,T3.DueDate,T3.JobNum,T3.JobSeq,T3.JobSeqType,T3.POLine as PORel_POLine,T3.PORelNum,T3.PromiseDt,T3.Calc_ExtCost,T3.Calc_OnOrder,T3.POLine_PartNum,T3.Calc_RelStatusDesc,T4.PurAgent_Name,
 FROM POHeader_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
 LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T3
  ON T2.Company = T3.Company AND T2.PONUM = T3.PONum AND T2.POLine = T3.POLine
LEFT OUTER JOIN PurAgent_" + Parameters!TableGuid.Value + " T4
  ON T1.Company = T4.Company AND T1.BuyerID = T4.BuyerID"

But then would get this error when I ran it:

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
Ice.Core.SsrsReportService.ReportExecutionSoapException: Exception of type 'Ice.Core.SsrsReportService.ReportExecutionSoapException' was thrown.
SOAP Fault: 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)
Detail: <ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsProcessingAborted</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An error has occurred during report processing.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Exceptions.ErrorStrings&amp;EvtID=rsProcessingAborted&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=15.0.1102.911</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">15.0.1102.911</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsProcessingAborted" msrs:HelpLink="https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Exceptions.ErrorStrings&amp;EvtID=rsProcessingAborted&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=15.0.1102.911" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An error has occurred during report processing.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorExecutingCommand" msrs:HelpLink="https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Exceptions.ErrorStrings&amp;EvtID=rsErrorExecutingCommand&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=15.0.1102.911" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'POHeader'.</Message><MoreInformation><Source></Source><Message>For more information about this error navigate to the report server on the local server machine, or enable remote errors</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.SetExecutionParameters(ParameterValue[] parameters, String parameterLanguage) in C:\_releases\ICE\ICE4.2.100.9\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 189
   at Ice.Core.SsrsReporting.SsrsRendererBase.ConfigureReportServerForReport(String reportPath, ParameterValue[] parameters) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 169
   at Ice.Core.SsrsReporting.SsrsSingleDocumentRenderer.<>c__DisplayClass1_0.<RenderDocument>b__0() in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsSingleDocumentRenderer.cs:line 49
   at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func`1 timedAction) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReport(String ssrsRenderFormat, String printProgram, Boolean ignorePageSettings) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 34
   at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func`2 reportsRenderer, Action`1 fillSysRptLstRow, Action`2 processReport, Func`3 filterTableAttachmentsFunc) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Erp.Internal.PM.PMR10.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_releases\ERP\ERP11.2.100.0\Source\Server\Internal\PM\PMR10\PMR10.cs:line 323
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.100.9\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.100.9\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 64
   at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
   at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_releases\ICE\ICE4.2.100.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

What am I doing wrong?

Thank you.

Hally · March 28, 2025, 1:06pm

Are you missing a space between the last t2.POLine and LEFT OUTER JOIN?

mlp · March 28, 2025, 3:37pm

I fixed that and still get the same error.

mlp · March 28, 2025, 3:48pm

So I tried changing it from PurAgent_Name to just Name. Didn’t work. Saw a comma after Name, so removed that. It loaded the report, but no actual buyer names. When I change it to PurAgent_Name, it errors. I don’t know what to call it.

mlp · March 28, 2025, 4:13pm

Well talking it out with AI helped. It was the naming convention messing me up. Thank you for helping. Here is the working query:

="SELECT T1.POType, T1.Calc_IncludeMfgPart, T1.Calc_IncludeSupplierPart, T1.Calc_RPTUSERID, 
        T1.Vendor_Name, T1.Vendor_VendorID, 
        T2.LineDesc, T2.MfgPartNum, T2.PartNum, T2.POLine, T2.PONUM, T2.PUM, T2.UnitCost, 
        T2.VenPartNum, T2.Calc_DspCostPerCode, T2.Calc_MfgID, 
        T2.PONum, 
        T3.AssemblySeq, T3.DueDate, T3.JobNum, T3.JobSeq, T3.JobSeqType, T3.POLine AS PORel_POLine, 
        T3.PORelNum, T3.PromiseDt, T3.Calc_ExtCost, T3.Calc_OnOrder, T3.POLine_PartNum, 
        T3.Calc_RelStatusDesc, 
        T4.Name AS PurAgent_Name
 FROM POHeader_" & Parameters!TableGuid.Value & " T1
 LEFT OUTER JOIN PODetail_" & Parameters!TableGuid.Value & " T2
     ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
 LEFT OUTER JOIN PORel_" & Parameters!TableGuid.Value & " T3
     ON T2.Company = T3.Company AND T2.PONUM = T3.PONum AND T2.POLine = T3.POLine
 LEFT OUTER JOIN PurAgent_" & Parameters!TableGuid.Value & " T4
     ON T1.Company = T4.Company AND T1.BuyerID = T4.BuyerID"

Hally · March 29, 2025, 1:08am

Sorrry I didn’t look closely at your code, in the original query there is a trailing comma after T4.PurAgent_Name

,T4.PurAgent_Name,
 FROM POHeader_" + Parameters!TableGuid.Value + " T1

aosemwengie1 · March 29, 2025, 4:01pm

Looks like you already solved this but I would have done this through pick links. Then all you have to do is reference the additional field in the dataset after selecting it in the rdd.