LaborEdit Report - Modifying RDD

Epicor ERP 10
, ,
1

Hello Gurus,

I’m trying to customize the LaborEdit report to include a few more fields (PartNum, Description, and an additional grouping by ResourceGroup). I’ve got a new Report Style and Report Data Definition made, and it runs, but the Report Totals at the bottom of the report don’t match with the original report style totals… and I’m struggling to figure out why.

I think it may have something to do with my modified query on the LaborDtl data set. I thought I did the joins correctly…but apparently not. Or something with my RDD?

What I’ve done so far:

Created new RDD (copied system RDD). New RDD includes JobAsmbl and ResourceGroup tables. Both tables are linked to the LaborDtl table on the PK_LaborDtl.

Created new Report Style linked to new RDD. Copied base SSRS report to use. In SSRS report builder, modified query on LaborDtl data set to include the new tables in the RDD.

I ran the new report before editing the query on the LaborDtl datasource to make sure it would run ok, and the Report Totals matched with the original report style’s.

But, once I edit the query to include the new tables/fields, there are duplicate rows that throw off the report total.

Here’s the original query in the SSRS report builder on the LaborDtl datasource:

="SELECT T1.AssemblySeq,T1.BurdenHrs,T1.Company,T1.Complete,T2.ExternalMES,T1.DspClockInTime,T1.DspClockOutTime,T1.EmployeeNum,T1.JCDept,T1.JobNum,T1.LaborHedSeq,T1.LaborHrs,T1.LaborQty,T1.LaborType,T1.OpCode,T1.OprSeq,T1.PayrollDate,T1.ResourceGrpID,T1.SetupPctComplete,T1.Calc_EffPct,T1.Calc_ErrorCond,T1.Calc_IndirMsg,T1.Calc_LaborType,T1.Calc_Notes,T1.Calc_WarningTxt,T1.JCDept_Description, T2.EmpID,T2.JCDept as EmpBasic_JCDept,T2.Name,T2.JCDept_Description as EmpBasic_JCDept_Description,T1.Calc_ClockWarnings
      FROM LaborDtl_" + Parameters!TableGuid.Value + " T1
      LEFT OUTER JOIN EmpBasic_" + Parameters!TableGuid.Value + " T2
      ON T1.Company = T2.Company AND T1.EmployeeNum = T2.EmpID"

And here’s my modified query to include the new tables and fields.

="SELECT T1.AssemblySeq,T1.BurdenHrs,T1.Company,T1.Complete,T2.ExternalMES,T1.DspClockInTime,T1.DspClockOutTime,T1.EmployeeNum,T1.JCDept,T1.JobNum,T1.LaborHedSeq,T1.LaborHrs,T1.LaborQty,T1.LaborType,T1.OpCode,T1.OprSeq,T1.PayrollDate,T1.ResourceGrpID,T1.SetupPctComplete,T1.Calc_EffPct,T1.Calc_ErrorCond,T1.Calc_IndirMsg,T1.Calc_LaborType,T1.Calc_Notes,T1.Calc_WarningTxt,T1.JCDept_Description, T2.EmpID,T2.JCDept as EmpBasic_JCDept,T2.Name,T2.JCDept_Description as EmpBasic_JCDept_Description,T1.Calc_ClockWarnings, T3.Description as JobAsmbl_PartDescription, T4.Description as ResourceGrp_Description
      FROM LaborDtl_" + Parameters!TableGuid.Value + " T1
      LEFT OUTER JOIN EmpBasic_" + Parameters!TableGuid.Value + " T2
      ON T1.Company = T2.Company AND T1.EmployeeNum = T2.EmpID
      LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T3
      ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum AND 
      T1.AssemblySeq = T3.AssemblySeq
      LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " T4
      ON T1.Company = T4.Company AND T1.ResourceGrpID = T4.ResourceGrpID"

I attempted to recreate the above query in a BAQ to try to get an idea for how the duplicate rows get introduced, but my recreated query maintained it’s row count after the new joins.

Any input would be greatly appreciated.

2

I ran the modified query in ssms and sure enough there are some duplicate rows coming in from somewhere. Adding a DISTINCT statement gets the Report Totals back to normal, but I am still wondering where the duplicate rows come from.

3

I know in our data base, you can have more than one op seq under an assembly sequence. But we tend to use Operations more than Assemblies. All the links I have done take LaborDtl to Job data by using JobNum, Assembly Seq and Op Seq. Not just the first two. Don’t know if that will fix your’s or not :slight_smile:

4

I see what you mean, most of our parts set up as are multi-operation / single assembly in Epicor as well. I’m currently pulling the job info from the JobAsmbl table, which doesn’t have an op sequence tied to it (One of the parents to JobOper table) so I don’t think that’s the issue unfortunately.