Report Modification

[kristajenn21 (Krista Bartholomew)]

Hello all!

We are having trouble modifying the ReceiptInvMvmnt report. We have built the query in a baq and it functions properly. When we build the same query in Report Builder (from what we can tell), it is duplicating results of PO lines with multiple releases. We can’t figure out why it is behaving this way when it functions properly in the baq format.

Here is the BAQ Query
select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranQty] as [PartTran_TranQty],
[RcvDtl].[PONum] as [RcvDtl_PONum],
[RcvDtl].[POLine] as [RcvDtl_POLine],
[RcvDtl].[PORelNum] as [RcvDtl_PORelNum],
[PODetail].[CommentText] as [PODetail_CommentText]
from Erp.PartTran as PartTran
inner join Erp.RcvDtl as RcvDtl on
PartTran.Company = RcvDtl.Company
and PartTran.PackSlip = RcvDtl.PackSlip
and PartTran.PackLine = RcvDtl.PackLine
inner join Erp.PODetail as PODetail on
RcvDtl.Company = PODetail.Company
and RcvDtl.PONum = PODetail.PONUM
and RcvDtl.POLine = PODetail.POLine

Here is the query in Report Builder

=“SELECT T3.CommentText,T1.ActTranQty,T1.AsOfDate,T1.AssemblySeq,T1.AssetNum,T1.BurUnitCost,T1.Calc_DocNum,T1.Calc_InvoiceNum,T1.Calc_LineNum,T1.Calc_PrintedBy,T1.Calc_SNList,T1.Calc_VendorAddrList,T1.Calc_VendorID,T1.Calc_WareHouseDesc,T1.Company,T1.EmpID,T1.ExtCost,T1.InvAdjReason,T1.JobNum2,T1.JobSeq2,T1.LotNum2,T2.OrderLine,T2.OrderNum,T1.PackLine,T1.PackNum,T1.PackSlip,T1.PBInvNum,T1.PORelNum,T1.POUnitCost,T1.ProjProcessed,T1.SubUnitCost,T1.SysDate,T1.SysTime,T1.TranDocTypeID,T1.TranNum,T1.TranQty,T1.WareHouse2,T1.AdditionNum,T1.AsOfSeq,T1.AssemblySeq2,T1.BinNum,T1.BinNum2,T1.BinType,T1.Calc_Bin2Description,T1.Calc_BinDescription,T1.Calc_EntryPerson,T1.Calc_InvoiceDate,T1.Calc_ReasonDescription,T1.Calc_WareHouse2Desc,T1.Calc_Weight,T1.DisposalNum,T1.EntryPerson,T1.InvAdjSrc,T1.JobNum,T1.JobSeq,T1.LbrUnitCost,T1.LegalNumber,T1.LoanFlag,T1.LotNum,T1.MscNum,T1.MtlUnitCost,T2.OrderRelNum,T1.PartDescription,T1.PartNum,T1.Plant,T1.Plant2,T1.POLine,T1.PONum,T1.POReceiptQty,T1.PurPoint,T1.TranDate,T1.TranReference,T1.TranType,T1.UM,T1.WareHouseCode,T1.RptLanguageID,T1.MtlBurUnitCost,T1.PCID
FROM PartTran_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN RcvDtl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.PackSlip = T2.PackSlip AND T1.PackLine = T2.PackLine LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.PONum = T3.PONUM AND T2.POLine = T3.POLine"

[bordway (Bruce Ordway)]

@kristajenn21

Report Builder…duplicating…functions properly in the baq

Wonder if could be related to use of Inner joins in BAQ vs. LEFT OUTER JOINs in SSRS?

[gpayne (Greg Payne)]

@bordway That is part of it, but this report seems to be strong with Epiforces. As soon as you add a join to RcvDtl or PODetail you get multiple rows in the data built for the report. The way I would get around that would be to add PODetail or RcvDtl a second time to the RDD, but that is blocked by a Foreign Key Constraint.
I looked at the fields we have added to this report and they were all UD fields on RcvDtl, so either I was lucky or forgot about how this was done.

@kristajenn21 Here is how I would approach this. Add a POComment_c to RcvDtl and then populate it in Receipt Entry bpm on something like Update or GetDtlPOLineInfo. If you have not already for your report make a custom RDD and make sure the POComment_c field is not excluded. Add T1.POComment_c to the select and Fields, Then Epicor will add it to the data for the report for you.

[kristajenn21 (Krista Bartholomew)]

@gpayne Thank you so so much! This did the trick, you are a lifesaver. I’m just an accountant trying to tinker around in something I don’t really know much about so I really appreciate your expertise! I think I better take the rest of the week off now haha. Thanks again!!

[kristajenn21 (Krista Bartholomew)]

Here are some more details of what we did…

1. Added a new column called POComment_C to RcvDtl table

2. Create a new Post Processing directive on GetDtlPOLineInfo

3. add a set field box

4. Use this expression:

Db.PODetail.Where(r => r.Company == callContextClient.CurrentCompany && r.PONUM == ttRcvDtlRow.PONum && r.POLine == ttRcvDtlRow.POLine).Select( r => r.CommentText).FirstOrDefault()