Fresh eyes on an SSRS join?

Epicor ERP 10
1

Hey all,

I’ve added a join to the query in the Labor Edit report to get the actual labor hours from LaborDtl. Actually, on the subreport, EmpDetails.rdl.

LaborDtl on the RDD has two relationships for filtering and aren’t set to Output, and I tried adding a second instance of the LaborDtl table, which resulted in a “server error.” So I’m referring directly to Erp.LaborDtl in the code. But I’m doing something wrong and can’t see it.

The forms upload okay from Report Style, but when I try to preview, I get the dreaded “Error: Subreport could not be shown” message where the subreport output should be.

Care to take a look?

Thanks,

Joe

="SELECT T1.EmployeeNum,T1.DspClockInTime,T1.DspClockOutTime,T1.LaborHedSeq,T1.PayHours,T1.PayrollDate,T1.Calc_LunchIn,T1.Calc_LunchOut,LaborHours.Calculated_LaborHours as [Calc_LaborHours]
 FROM LaborHed_" + Parameters!TableGuid.Value + " T1 
 
 left outer join  (select 
	T2.Company as [LaborDtl_Company],
	T2.EmployeeNum as [LaborDtl_EmployeeNum],
	T2.LaborHedSeq as [LaborDtl_LaborHedSeq],
	T2.LaborDtlSeq as [LaborDtl_LaborDtlSeq],
	T2.PayrollDate as [LaborDtl_PayrollDate],
	(sum(T2.LaborHrs)) as [Calculated_LaborHours]
from Erp.LaborDtl T2 
group by T2.Company,
	T2.EmployeeNum,
	T2.LaborHedSeq,
	T2.LaborDtlSeq,
	T2.PayrollDate) as LaborHours on 
	T1.Company = LaborHours.LaborDtl_Company
	and T1.EmployeeNum = LaborHours.LaborDtl_EmployeeNum
	and T1.LaborHedSeq = LaborHours.LaborDtl_LaborHedSeq
	and T1.PayrollDate = LaborHours.LaborDtl_PayrollDate

 "
2

Did you try switching the joins from Definition to Output?

3

You should be able to try to preview just the subreport with the parameters. However, I don’t see where you say what the T2 table is.

4

Hi Jason,

My intention was to set T2 to Erp.LaborDtl here: from Erp.LaborDtl T2

I’ve also used: from Erp.LaborDtl as T2

And I’m editing the RDL on my local PC on a public cloud installation. I don’t think I have a way to preview the subreport, unless you know one, I hope. :slight_smile:

Thanks,

Joe

5

No public cloud option… :weary:
This may work better (no promises):

="SELECT T1.EmployeeNum,T1.DspClockInTime,T1.DspClockOutTime,T1.LaborHedSeq,T1.PayHours,T1.PayrollDate,T1.Calc_LunchIn,T1.Calc_LunchOut,T2.PayrollDate,sum(T2.LaborHrs) AS LaborHrs 
 FROM LaborHed_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN LaborDtl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company and T1.LaborHedSeq = T2.LaborHedSeq
 GROUP BY LaborHed_" + Parameters!TableGuid.Value + ".EmployeeNum,
 LaborHed_" + Parameters!TableGuid.Value + ".DspClockInTime, 
 LaborHed_" + Parameters!TableGuid.Value + ".DspClockOutTime, 
 LaborHed_" + Parameters!TableGuid.Value + ".LaborHedSeq, 
 LaborHed_" + Parameters!TableGuid.Value + ".PayHours, 
 LaborHed_" + Parameters!TableGuid.Value + ".PayrollDate, 
 LaborHed_" + Parameters!TableGuid.Value + ".Calc_LunchIn, 
 LaborHed_" + Parameters!TableGuid.Value + ".Calc_LunchOut"
6

John,

The labor edit report appears to use the relationships between LaborDtl and EmpBasic to filter LaborHed:

image
image706×495 20.6 KB

I’m thinking they wouldn’t work if I changed the relation type to output. ??

Thanks,

Joe

7

Jason,

Sadly, I still get the subreport error. Going to need to table it for a bit.

Thanks for your help!

Joe

8

Every field on the out of the box RDD is excluded for the LaborDtl table. I would just create a copy of the RDD and expose the fields that you want.

9

Since you are editing a standard report and adding small changes, you should be able to edit the report on a standard Epicor install and test the SSRS in your own environment (with your own SQL).

10

Well, that seems to make sense. :slight_smile:

Thanks, Jason.

Joe