They still don’t allow this? Even in Kinetic?
1 Like
What I would like is to get their email addresses. This would allow me to break route based on supervisor email. Doing that through a Calculated field would mean I would have to update every time someone shuffled around.
As of right now no. I still get the angry Epicor message:
Thank you both for the responses!
Yeah Dylan, I have also wanted to do this for quite some time as the sales rep is used on multiple tables and thus the need to join it in more than one way is needed.
I thought this was an epicor Idea at one point. I have to imagine that it has strong support.
Is this a BAQ report?
If not, an old Crystal trick may work. Relate to the table with just Company and then do the relations in the Dataset query inside SSRS.
3 Likes
Still not possible to my knowledge…
One caveat with that trick is, at least back in the Crystal days, is this trick pulls all the records of that table. The EmpBasic shouldn’t be too heavy of a table and with the report data now in the SSRS database it should be pretty quick if it works.
Another option is to build another data source into your SSRS and have it point to your database to get the data. I’ve not had to do this yet but IIRC there is a thread here about it.
@dgreenEA Ways that I think could possibly do this. All are a bit hacky, but would work.
- EmpBasic has PerConID and PerCon has ReportsTo
- Use a UD table to build Employee and supervisor records on update of employee.
- Add the supervisor info into UD fields in EmpBasic and let Epicor bring that to the report.
1 Like
Just include the table once with no relations. The RDD will create a copy of the EmpBasic table as EmpBasic_GUID. Then refernce it twice in the RDL’s query expression.
I’d only suggest doing this tables that you know are limited in size (Country, EmpBasic, etc …) Definitely don’t do it with PartTran.
2 Likes
However you can include the table twice if it exists twice.
I actually use JobAsmbl twice by simply including a different alias, yet it is the same DB Table behind the scenes. But before you do that, make sure you have exhausted the Linked Tables options first. Sometimes you can just use that in conjunction with an additional Relationship.
For example for JobHead I simply used the other one, it worked exactly like the regular one, since it was the same Schema.
Maybe the List one didnt work well, but for sure the other one if I recall.
I thought you could start mixing BAQs and RDDs now… @JeffLeBert maybe knows more of a same-table relationship trick 
2 Likes
https://epicor-manufacturing.ideas.aha.io/ideas/ERP-I-731 Looks like someone made an epicor idea but it doesn’t have many upvotes
2 Likes
This is a great idea but I think more votes are going to using BAQs for report sources which would also mitigate this problem.
1 Like
I voted for it Chance.
Would also vote for this.
It’s been a long time since I “owned” the reporting framework. I didn’t do much with this issue and so never had to find workarounds.
It looks like others in this thread came to the same conclusion that I did. Making the reporting framework allow mixing BAQs as data sources in “regular” reports would solve this problem. It would also give a bunch of other benefits.
That’s about all I can say. Try what @hkeric.wci suggested and vote for BAQ support in “regular” reports. I will continue to push for this as well.
5 Likes
I am not seeing the idea for that. However would love that feature as well! Anyone that could link it will get my vote.
1 Like
I dont have enough information to know what exactly your are trying to achieve, but I would suggest you change your approach:
- Select the EmpBasic and link it only by Company. This will return all the Employees (provided you dont have thousands, it should not affect the performance).
- In SSRS create two datasets both using the EmpBasic. the first one selects non-suppervisors employees, the other does.
- You can use a different tablix to pool the information from each table or
- you could use a formula using LOOKUP
Hope that helps.
1 Like
I finally got back to giving this a shot. Right now I am trying it with the Vendor and Vendor PP table as it’s a similar setup. The below code shows how the SSRS is set up:
<CommandText>="SELECT T1.RptLanguageID,T1.Company,T7.VendorID as T7VendorID, T24.VendorID as T24VendorID, T8B.FFAddress1 as T8BFFAddress1, T8B.FFAddress2 as T8BFFAddress2, T8B.FFAddress3 as T8BFFAddress3, T8B.FFCity as T8BFFCity, T8B.FFCountry as T8BFFCountry, T8B.FFState as T8BFFState, T8B.FFZip as T8BFFZip,T1.Consecutive,T3.LaborEntryMethod, T3.OpDesc, T5.JCDept,T10.BaseFileName ,T10.Company ,T10.XFileName ,T10.XFileRefNum, T9.Key4, T8.FFAddress1, T8.FFAddress2, T8.FFAddress3, T8.FFCity, T8.FFCountry, T8.FFState, T8.FFZip , T1.CurrentLevel,T1.IsHeader,T1.IsSubAssem,T1.ParentPartNum,T1.PartNum, T2.RptLanguageID as PartRev_RptLanguageID,T2.AltMethod,T2.Company as PartRev_Company,T2.PartNum as PartRev_PartNum,T2.RevisionNum,T2.RevShortDesc,T2.Calc_Consecutive,T2.Calc_SubAssembly, T3.RptLanguageID as PartOpr_RptLanguageID,T3.AddlSetupHours,T3.AddlSetUpQty,T3.BrkQty01,T3.CommentText,T3.Company as PartOpr_Company,T3.DaysOut,T3.EstSetHours,T3.EstUnitCost,T3.Machines,T3.OpCode,T3.OprSeq,T3.PartNum as PartOpr_PartNum,T3.PrimaryProdOpDtl,T3.PrimarySetupOpDtl,T3.ProdStandard,T3.QtyPer,T3.StdFormat,T3.SubContract,T3.Calc_Consecutive as PartOpr_Calc_Consecutive,T3.Calc_ProdHrs,T3.Calc_RequiredQty, T4.RptLanguageID as PartOpDtl_RptLanguageID,T4.CapabilityID,T4.Company as PartOpDtl_Company,T4.ConcurrentCapacity,T4.DailyProdRate,T4.OpDtlSeq,T4.OprSeq as PartOpDtl_OprSeq,T4.PartNum as PartOpDtl_PartNum,T4.ProdCrewSize,T4.ResourceGrpID,T4.ResourceID,T4.SetUpCrewSize,T4.SetupOrProd
FROM MethodsMasterTable_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T2
ON T1.Consecutive = T2.Calc_Consecutive AND T1.PartNum = T2.PartNum AND T1.RevisionNum = T2.RevisionNum
LEFT OUTER JOIN PartOpr_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.PartNum = T3.PartNum AND T2.RevisionNum = T3.RevisionNum
LEFT OUTER JOIN PartOpDtl_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.OprSeq = T4.OprSeq AND T3.PartNum = T4.PartNum AND T3.RevisionNum = T4.RevisionNum
LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.ResourceGrpID = T5.ResourceGrpID
LEFT OUTER JOIN VendPart_" + Parameters!TableGuid.Value + " T6
ON T6.Company = T3.Company AND T6.PartNum = T3.PartNum AND T6.OpCode = T3.OpCode
LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T7
ON T3.Company = T7.Company AND T3.VendorNum = T7.VendorNum
LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T24
ON T6.Company = T24.Company AND T6.VendorNum = T24.VendorNum
LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T8
ON T8.Company = T7.Company AND T8.VendorNum = T7.VendorNum
LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T8B
ON T8B.Company = T24.Company AND T8B.VendorNum = T24.VendorNum
LEFT OUTER JOIN XFileAttch_" + Parameters!TableGuid.Value + " T9
ON T9.Company = T3.Company AND T9.Key1 = T3.PartNum AND T9.Key2 = T3.RevisionNum
LEFT OUTER JOIN XFileRef_" + Parameters!TableGuid.Value + " T10
ON T9.Company = T10.Company AND T9.XFileRefNum = T10.XFileRefNum
"</CommandText>
In the RDD I reference the Vendor and Vendor PP table but give them no relationship. When I run the report I get no vendor information. Is this because I do not have a relationship listed for these two tables, or is this because of something else?
Thanks to everyone for the help thus far!
Add a UD field in EmpBasic to store the supervisor’s email and create a BPM to update it.
1 Like