Custom Employee Efficiency Report

NateS · January 11, 2023, 1:55pm

Good Morning!
Today I am trying to take the base Employee Efficiency report and customize it to show only selected departments. I see there is a BAQ zEmployeeEfficiency. I copied this, and filtered it to only show the departments I am interested in. The BAQ returns the correct results, however I want to run this into the stock Employee Efficiency report.

So I began looking into the Report Style, Data Definitions, and BAQ Reports. It looks like there is a report style, and a data definition and a BAQ Report for the base employee efficiency data.

I am a little confused by this. Normally I would copy the BAQ Report, point at the new BAQ, customize the SSRS if needed and reupload it, but my report keeps returning an error.

Program Ice.Services.Lib.RunTask when executing task 368255 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 'BAQReportParameter'. ---> System.Data.SqlClient.SqlException: Invalid column name 'DCDUserID'.)
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 'BAQReportParameter'.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>Invalid column name 'DCDUserID'.</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.200.8\Source\Shared\Lib\ReportingServices\ReportExecutionService.cs:line 189
   at Ice.Core.SsrsReporting.SsrsRendererBase.ConfigureReportServerForReport(String reportPath, ParameterValue[] parameters) in C:\_releases\ICE\ICE4.2.200.8\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.200.8\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.200.8\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.8\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.8\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.8\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 163
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_releases\ICE\ICE4.2.200.8\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.8\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 58
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_releases\ICE\ICE4.2.200.8\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 359
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_releases\ICE\ICE4.2.200.8\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_releases\ICE\ICE4.2.200.8\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 260
   at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.RunReportWithErrorHandling(ReportDatabaseConnectionHelper reportDatabaseConnectionHelper) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 70
   at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.WriteData() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 46
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.8\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.8\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

What is the right way to provide a permenant department level filter to the employee efficiency report so that my managers don’t have to click 8 different departments out of a dozen?

Thank you for your time!
Nate

Dragos · January 11, 2023, 4:20pm

From what I can see in my version, that BAQ doesn’t have anything to do with the SSRS report data definition. You could start with that BAQ and do a report from scratch. Or, the lazy way, you can filter the report in SSRS - from the group properties or in the SQL query definition for dataset EmpEfficiencyDtl with a where clause.

tsmith · January 11, 2023, 4:32pm

+1 to @Dragos suggestion.

If the filters are semi-permanent (aka, you won’t be asked to change these for the foreseeable future), I would personally copy the report style, name it something like “[Company] - Filtered”, and set a filter on the SSRS report dataset inside the RDL file itself. That’s a simple way to do the filtering.

Topic		Replies	Views
Using the same Parameters on more than one BAQ Kinetic ERP e10 , baq , ssrs , reports	22	2767	February 27, 2023
How do I pass BAQ Parameters into a custom SSRS report? Epicor ERP 10 e10 , baq , ssrs , reports	12	6207	August 24, 2020
How To Merge These into One Report? Kinetic ERP e10 , customization , baq , ssrs , reports	23	1794	March 28, 2023
BAQ Parameters Not Working on SSRS Report Epicor ERP 10 baq , ssrs , reports	11	3321	January 7, 2021
SSRS reports BAQ report designer and setting filters / sorts Epicor ERP 10 e10 , ssrs , reports	21	6628	March 19, 2020

Custom Employee Efficiency Report

Related topics