How To Merge These into One Report?

NateS · March 22, 2023, 2:06pm

Good morning,
I have these two BAQs that when grouped and summarized, gives me the nice compact data I want:

I would like to know if there is a way to combine this data at the BAQ report level. I want to output a BAQ report that shows these summaries by department, on the same line. I want to see the value from both BAQs for each department. (i.e. SHEETMETAL $10000, $150000)

But I also want to retain the details so that we can see the job level information for each department. In these BAQs. The department summaries contain all of the individual job values. I don’t want to lose this by grouping the BAQ before the output.

How can I get each department’s summary value to appear on the same line in the report? For example at the top of the report I just want an overview of all the departments and the two values from the two BAQs. Later in the report I want to see the details for each department showing the individual job details.

Department	Target	Value
BRIDGEPORT MILLS, MANU/TOOLMKG (11 items) 	0.815006201	0.257809802
BURRING DEPT-TUMBLE-SAND BLAST (38 items) 	0.626771116	0.562242369
CNC LATHES (29 items) 	0.747821818	0.118868699
CNC MILLS- HORIZONTALS (5 items) 	0.346049237	0.967649284
CNC MILLS-VERTICALS & WIRE EDM (27 items) 	0.406049747	0.183155667
OUTSIDE SERVICES - SUB CONTRCT (28 items) 	0.153759217	0.076246399
SHEETMETAL (5 items) 	0.438929624	0.468706028
SHIPPING (47 items) 	0.793828876	0.317907666

Summaries for BURRING DEPT-TUMBLE-SAND BLAST												
Company	Job	OpenJobValue	OpenJobTarget	OpenJobQty	Asm	Part	NextOp	Req. By	Start	Due Date	Date08	Status
VTAERO	0.411170598	0.881702973	0.72536385	0.798061844	0	0.738204863	100	12/19/2022	3/22/2023	3/24/2023	3/24/2023	Late
VTAERO	0.982028102	0.535926313	0.517035586	0.040715577	0	0.559562819	160	3/1/2023	3/21/2023	4/12/2023	3/31/2023	Late
VTAERO	0.329288593	0.630012925	0.78852581	0.170548699	0	0.704225714	20	5/10/2023	5/1/2023	5/10/2023	3/31/2023	On Time

Note: I just stuffed a bunch of rands in there to obscure our data.

I appreciate your time!
Nate

jkane · March 22, 2023, 2:18pm

I would create the other type of BAQ report (not a “BAQ Report”). Create the RDD and add both BAQs as data sources. No joins. This will get both BAQs in the SSRS db as separate tables. Then, in the rdl, edit one of the dataset queries to be a CTE that combines the two queries together as an outer join.

dr_dan · March 22, 2023, 2:20pm

If you’re in for a ride, check out the documentation on Advanced Group By. @LarsonSolutions presented on this at our last EUG meeting. It was pretty cool. You get the best of both worlds. I don’t know it well enough to teach it but if you look around maybe there are some posts on here. I’ll reach out and see if I can get the notes from that EUG meeting. If I can, I’ll share.

NateS · March 22, 2023, 3:33pm

I have the two BAQs in the RDD without joins. and I can pull in the values into the report. It is spitting out the right data. Now I just need to figure out that next step of:

Can you say how to do this? I am not very good with CTEs. Do I setup a third Dataset that joins the two existing datasets?

My two datasets have these expressions:
OpenJobValues:

="SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate]
 FROM 
OpenJobValues_" + Parameters!TableGuid.Value + " T1"

OpenJobTargets:

="SELECT  T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobTarget], T1.[JobHead_Company], T1.[JobHead_JobNum]
 FROM 
OpenJobTargets_" + Parameters!TableGuid.Value + " T1"

jkane · March 22, 2023, 3:35pm

Sure thing. Give me a few minutes.

jkane · March 22, 2023, 3:57pm

Here you go. I would just choose one of the datasets (does not matter which one) and edit the query definition. I did not remove any duplicate fields or define the join fields as I was not sure what it would be, but you can edit as needed. Just make sure to have that ; in the beginning, it won’t work without it.

=";WITH CTE (Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum)

AS
(
	SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum]
 FROM 
OpenJobValues_" + Parameters!TableGuid.Value + " T1
OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " T2
ON T1 = T2
)

SELECT Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum
FROM CTE"

NateS · March 22, 2023, 4:57pm

Hmmm seems there is a syntax error in there near T1. This sure is strange syntax! I replaced the query text in the dataset for the OpenJobValues. I also added the field OpenJobTarget to the Dataset field list as a query field.

But I get this error when running the report:

Program Ice.Services.Lib.RunTask when executing task 462531 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 'OpenJobValues'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near 'T1'.)
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.897</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.897</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.897" 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.897" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'OpenJobValues'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>Incorrect syntax near 'T1'.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning[]& warnings, String[]& streamIds) in C:\_releases\ICE\ICE4.2.200.11\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 154
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_releases\ICE\ICE4.2.200.11\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\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Core.RptTaskBase`1.RunDynamicCriteriaReport(IEnumerable`1 criteriaParameters, IEnumerable`1 criteriaMappings) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 163
   at Ice.Lib.DynamicCriteria.DynamicCriteriaReport.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\DynamicCriteria\DynamicCriteriaReport.cs:line 44
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\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.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\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

NateS · March 22, 2023, 5:02pm

Where would you define a join in here? I want to join on company, department, and job num.

jkane · March 22, 2023, 5:05pm

Updated

=";WITH CTE (Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum)

AS
(
	SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum]
 FROM 
OpenJobValues_" + Parameters!TableGuid.Value + " T1
OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " T2
ON T1.[JobHead_Company] = T2.[JobHead_Company] AND T1.[JCDept2_Description] = T2.[JCDept2_Description] AND T1.[JobHead_JobNum] = T2.[JobHead_JobNum]
)

SELECT Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum
FROM CTE"

NateS · March 22, 2023, 5:09pm

Same error on my end.

jkane · March 22, 2023, 5:12pm

Put a FULL in front of outer join

NateS · March 22, 2023, 5:15pm

Similar error: Incorrect syntax near the keyword ‘JOIN’.)

Program Ice.Services.Lib.RunTask when executing task 462538 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 'OpenJobValues'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'JOIN'.)
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.897</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.897</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.897" 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.897" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'OpenJobValues'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>Incorrect syntax near the keyword 'JOIN'.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning[]& warnings, String[]& streamIds) in C:\_releases\ICE\ICE4.2.200.11\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 154
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_releases\ICE\ICE4.2.200.11\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\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Core.RptTaskBase`1.RunDynamicCriteriaReport(IEnumerable`1 criteriaParameters, IEnumerable`1 criteriaMappings) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 163
   at Ice.Lib.DynamicCriteria.DynamicCriteriaReport.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\DynamicCriteria\DynamicCriteriaReport.cs:line 44
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\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.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\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

Thank you for taking your time to help me!

jkane · March 22, 2023, 5:25pm

No problem. I’m not seeing anything that looks off. Only thing I can think of is to add AS between the table and the alias.

OpenJobValues_" + Parameters!TableGuid.Value + " AS T1
FULL OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " AS T2

NateS · March 22, 2023, 6:02pm

Unfortunately I am still getting a join error. Here is the syntax I am using:

=";WITH CTE (Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum)
AS (SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum]
FROM OpenJobValues_" + Parameters!TableGuid.Value + " AS T1
FULL OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " AS T2
ON T1.[JobHead_Company] = T2.[JobHead_Company] AND T1.[JCDept2_Description] = T2.[JCDept2_Description] AND T1.[JobHead_JobNum] = T2.[JobHead_JobNum])
SELECT Calculated_Status, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_MinOp, Calculated_OpenJobQty, Calculated_OpenJobValue, JobHead_Company, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum FROM CTE"

I removed all the controls from the report just to be safe. But I am still getting this error:

Program Ice.Services.Lib.RunTask when executing task 462578 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 'OpenJobValues'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'JOIN'.)
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.897</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.897</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.897" 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.897" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'OpenJobValues'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>Incorrect syntax near the keyword 'JOIN'.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning[]& warnings, String[]& streamIds) in C:\_releases\ICE\ICE4.2.200.11\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 154
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_releases\ICE\ICE4.2.200.11\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\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\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.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\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

NateS · March 22, 2023, 6:06pm

I removed all the line breaks, and got a new error at least! I am not sure why it cares if JCDept is in there twice.

Program Ice.Services.Lib.RunTask when executing task 462579 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 'OpenJobValues'. ---> System.Data.SqlClient.SqlException: The column 'JCDept2_Description' was specified multiple times for 'CTE'.)
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.897</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.897</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.897" 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.897" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'OpenJobValues'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>The column 'JCDept2_Description' was specified multiple times for 'CTE'.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning[]& warnings, String[]& streamIds) in C:\_releases\ICE\ICE4.2.200.11\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 154
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_releases\ICE\ICE4.2.200.11\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\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\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.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\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

NateS · March 22, 2023, 6:18pm

I removed the “duplicate” fields referenced in the first CTE, and in the last SELECT statements. Now that there are a unique list of fields, I get a new error about the number of fields not matching. I am pretty sure they match!

=";WITH CTE (Calculated_Status, Calculated_MinOp, Calculated_OpenJobValue, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum) AS (SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum] FROM OpenJobValues_" + Parameters!TableGuid.Value + " AS T1 FULL OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " AS T2 ON T1.[JobHead_Company] = T2.[JobHead_Company] AND T1.[JCDept2_Description] = T2.[JCDept2_Description] AND T1.[JobHead_JobNum] = T2.[JobHead_JobNum]) SELECT Calculated_Status, Calculated_MinOp, Calculated_OpenJobValue, JobHead_Date08, JobHead_DueDate, JobHead_JobNum, JobHead_ReqDueDate, JobHead_StartDate, JCDept2_Description, JobAsmbl2_AssemblySeq, JobAsmbl2_PartNum, Calculated_OpenJobQty, Calculated_OpenJobTarget, JobHead_Company, JobHead_JobNum FROM CTE"

Error:

Program Ice.Services.Lib.RunTask when executing task 462582 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 'OpenJobValues'. ---> System.Data.SqlClient.SqlException: 'CTE' has more columns than were specified in the column list.)
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.897</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.897</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.897" 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.897" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Query execution failed for dataset 'OpenJobValues'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>'CTE' has more columns than were specified in the column list.</Message></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />)
   at Ice.Core.SsrsReportService.ReportExecutionService.Render(String format, String deviceInfo, String& extension, String& mimeType, String& encoding, Warning[]& warnings, String[]& streamIds) in C:\_releases\ICE\ICE4.2.200.11\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 154
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_releases\ICE\ICE4.2.200.11\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\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 0
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 422
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.11\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.200.11\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\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.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\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450

jkane · March 22, 2023, 6:19pm

I made all the fields unique

=";WITH CTE (OJV_Calculated_Status, OJV_JCDept2_Description, OJV_JobAsmbl2_AssemblySeq, OJV_JobAsmbl2_PartNum, OJV_Calculated_MinOp, OJV_Calculated_OpenJobQty, OJV_Calculated_OpenJobValue, OJV_JobHead_Company, OJV_JobHead_Date08, OJV_JobHead_DueDate, OJV_JobHead_JobNum, OJV_JobHead_ReqDueDate, OJV_JobHead_StartDate, OJT_JCDept2_Description, OJT_JobAsmbl2_AssemblySeq, OJT_JobAsmbl2_PartNum, OJT_Calculated_OpenJobQty, OJT_Calculated_OpenJobTarget, OJT_JobHead_Company, OJT_JobHead_JobNum)
AS (SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum]
FROM OpenJobValues_" + Parameters!TableGuid.Value + " AS T1
FULL OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " AS T2
ON T1.[JobHead_Company] = T2.[JobHead_Company] AND T1.[JCDept2_Description] = T2.[JCDept2_Description] AND T1.[JobHead_JobNum] = T2.[JobHead_JobNum])
SELECT OJV_Calculated_Status, OJV_JCDept2_Description, OJV_JobAsmbl2_AssemblySeq, OJV_JobAsmbl2_PartNum, OJV_Calculated_MinOp, OJV_Calculated_OpenJobQty, OJV_Calculated_OpenJobValue, OJV_JobHead_Company, OJV_JobHead_Date08, OJV_JobHead_DueDate, OJV_JobHead_JobNum, OJV_JobHead_ReqDueDate, OJV_JobHead_StartDate, OJT_JCDept2_Description, OJT_JobAsmbl2_AssemblySeq, OJT_JobAsmbl2_PartNum, OJT_Calculated_OpenJobQty, OJT_Calculated_OpenJobTarget, OJT_JobHead_Company, OJT_JobHead_JobNum FROM CTE"

LarsonSolutions · March 23, 2023, 11:20am

I will post a seperate thread with attachments

NateS · March 23, 2023, 1:25pm

I don’t understand how you made each field unique. I see that you added OJV, and OJT to the beginning of the field name, but shouldn’t you have to define those tables using the same prefix? The fields don’t have those prefixes on them, so there is no way for the SQL to know which fields you are asking for. Right?

This code does not produce errors, but returns a blank report with no data.

jkane · March 23, 2023, 1:29pm

@NateS , I was thinking about this this morning and I definitely over thought the solution. You should not have to do the CTE and should be able to get away with just combining them in the query definition.

="SELECT  T1.[Calculated_Status], T1.[JCDept2_Description], T1.[JobAsmbl2_AssemblySeq], T1.[JobAsmbl2_PartNum], T1.[Calculated_MinOp], T1.[Calculated_OpenJobQty], T1.[Calculated_OpenJobValue], T1.[JobHead_Company], T1.[JobHead_Date08], T1.[JobHead_DueDate], T1.[JobHead_JobNum], T1.[JobHead_ReqDueDate], T1.[JobHead_StartDate], T2.[JCDept2_Description], T2.[JobAsmbl2_AssemblySeq], T2.[JobAsmbl2_PartNum], T2.[Calculated_OpenJobQty], T2.[Calculated_OpenJobTarget], T2.[JobHead_Company], T2.[JobHead_JobNum]
FROM OpenJobValues_" + Parameters!TableGuid.Value + " AS T1
FULL OUTER JOIN OpenJobTargets_" + Parameters!TableGuid.Value + " AS T2
ON T1.[JobHead_Company] = T2.[JobHead_Company] AND T1.[JCDept2_Description] = T2.[JCDept2_Description] AND T1.[JobHead_JobNum] = T2.[JobHead_JobNum]"

That should work just fine. I was thinking that combining the two BAQs together in one BAQ would work, which is where I thought of doing the CTE. Then I carried the thought of doing the CTE over to the SSRS report, which was not needed.