Been trying to get a sort order into this report and cant seem to get the syntax right:
<CommandText>="SELECT T1.ActTranQty,T4.PONum,T4.PackSlip,T3.RevisionNum,T4.ReceiptDate as TheDate,T5.Name,T6.TypeCode,T1.AsOfDate,T1.AssemblySeq,T1.AssetNum,T1.BurUnitCost,T1.Calc_DocNum,T1.Calc_InvoiceNum,T1.Calc_LineNum,T1.Calc_PrintedBy,T1.Calc_SNList,T1.Calc_VendorAddrList,T1.Calc_VendorID,T1.Calc_WareHouseDesc,T1.Company,T1.EmpID,T1.ExtCost,T1.InvAdjReason,T1.JobNum2,T1.JobSeq2,T1.LotNum2,T1.OrderLine,T1.OrderNum,T1.PackLine,T1.PackNum,T1.PBInvNum,T1.PORelNum,T1.POUnitCost,T1.ProjProcessed,T1.SubUnitCost,T1.SysDate,T1.SysTime,T1.TranDocTypeID,T1.TranNum,T1.TranQty,T1.WareHouse2,T1.AdditionNum,T1.AsOfSeq,T1.AssemblySeq2,T1.BinNum,T1.BinNum2,T1.BinType,T1.Calc_Bin2Description,T1.Calc_BinDescription,T1.Calc_EntryPerson,T1.Calc_InvoiceDate,T1.Calc_ReasonDescription,T1.Calc_WareHouse2Desc,T1.Calc_Weight,T1.DisposalNum,T1.EntryPerson,T1.InvAdjSrc,T1.JobNum,T1.JobSeq,T1.LbrUnitCost,T1.LegalNumber,T1.LoanFlag,T1.LotNum,T1.MscNum,T1.MtlUnitCost,T1.OrderRelNum,T1.PartDescription,T1.PartNum,T1.Plant,T1.Plant2,T1.POLine,T1.POReceiptQty,T1.PurPoint,T1.TranDate,T1.TranReference,T1.TranType,T1.UM,T1.WareHouseCode,T1.RptLanguageID,T1.MtlBurUnitCost,T1.PCID
FROM PartTran_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3
ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum
LEFT OUTER JOIN RcvDtl_" + Parameters!TableGuid.Value + " T4
ON T1.Company = T4.Company AND T1.PartNum = T4.PartNum AND T1.LotNum = T4.LotNum
LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.VendorNum = T5.VendorNum
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.PartNum = T6.PartNum
ORDER BY T4.ReceiptDate"</CommandText>
Does anyone know what I’m doing wrong?
Error received when running the report:
Program Ice.Services.Lib.RunTask when executing task 4183923 raised an unexpected exception with the following message: RunTask:
Ice.Core.SsrsReporting.SsrsCaller.SsrsException: The SSRS server returned the status code 500 (InternalServerError) with the following error text:
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 ‘PartTran’. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
at Ice.Core.SsrsReporting.SsrsCaller.SoapCaller.CallAsync[TContent,TResponse,TSoapHeader](String actionName, TContent content, TSoapHeader soapHeader) in C:_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsCaller\SoapCaller.cs:line 60
at Ice.Core.SsrsReporting.SsrsCaller.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning& warnings, String& streamIds) in C:_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsCaller\ReportExecutionService.cs:line 72
at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 162
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func1 timedAction) in C:\_releases\ICE\ICE4.2.300.5\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.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 247 at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 418 at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163 at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 34 at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func2 reportsRenderer, Action1 fillSysRptLstRow, Action2 processReport, Func3 filterTableAttachmentsFunc) in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58 at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359 at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93 at Ice.Core.RptTaskBase1.XMLClose() in C:_releases\ICE\ICE4.2.300.5\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
at Erp.Internal.IM.PrintMovement.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:_releases\ERP\ERP11.2.300.0\Source\Server\Internal\IM\PrintMovement\PrintMovement.cs:line 255
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:_releases\ICE\ICE4.2.300.5\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
at Ice.Hosting.TaskCaller.ExecuteTask() in C:_releases\ICE\ICE4.2.300.5\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.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\ICE4.2.300.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450
Also would like to know the syntax for top 1 to be applied after the sort.