added ShipToCust as a Pick Link to InvcDtl:
added PhoneNum as a Description Field:
added ShipToCust_PhoneNum field to the Dataset:
added T2.ShipToCust_PhoneNum to the Query:
added T2.ShipToCust_PhoneNum to the Query:
Got it.
1 Like
I’m hoping you have your original report or query.
If so, share the original query here in full, and we can make sure the correct table is selected.
Ok, back to the rdd in the virgin/semi-virgin state, before all this mess:
On InvcDtl → Exlusions, uncheck ShipToCustNum and ShipToNum
Add the table “ShipTo” to the rdd.
On ShipTo → Exclusions uncheck Company, CustNum, and ShipToNum
Add a relationship, name it “InvcDtlShipTo”
Parent → InvcDtl
Child → ShipTo
Key → don’t put one
RelationType → Output
Add three relationship fields:
(Parent → Child)
ShipToNum → ShipToNum
ShipToCustNum → CustNum
Company → Company
Save it.
In the query, my last table before was T11, so my new table is T12.
Right before FROM InvcHead_" + Parameters!TableGuid.Value + " T1:
I added: , T12.PhoneNum
Right after
LEFT OUTER JOIN InvcDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
I added:
LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " T12
ON T2.Company = T12.Company AND T2.ShipToCustNum = T12.CustNum AND T2.ShipToNum = T12.ShipToNum
Then I added the query field:
(Field Name - > Field Source)
ShipToCustPhone → PhoneNum
I added the field to the report.
Saved.
Uploaded the report.
Ran it.
It worked.
Here is my query in full:
(need to put inside ="")
SELECT T1.RptLanguageID,T1.Company,T1.CreditMemo,T1.Calc_ExtPriceTotal,T1.CustNum,T1.DocDepositCredit,T1.DocInvoiceAmt,T1.Calc_TotalMiscChrg,T1.DocRounding,T1.DocumentPrinted,T1.Posted,T1.InvoiceComment,T1.InvoiceDate,T1.InvoiceNum,T1.InvoiceType,T1.LegalNumber,T1.PONum,T1.SoldToInvoiceAddress,T1.Calc_BillToAddressList,T1.Calc_BottomAddress,T1.Calc_CompanyAddressList,T1.Calc_CurrDocDesc,T1.Calc_CurSymbol,T1.Calc_CustContactName,T1.Calc_CustPartOpts,T1.Calc_DteOrdrd,T1.Calc_fFOB,T1.Calc_MulPackNum,T1.Calc_mulponum,T1.Calc_MulShipDate,T1.Calc_MulShipTo,T1.Calc_MulShipVia,T1.Calc_MultSoldTo,T1.Calc_MultTaxID,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_PackNum,T1.Calc_PrintBottomAddress,T1.Calc_SalesPerson,T1.Calc_SalesTerms,T1.Calc_ShipDate,T1.Calc_ShipToAddressList,T1.Calc_ShipToContactName,T1.Calc_ShipVia,T1.[Calc_Voucher-String] as Calc_Voucher_String,T1.CurrencyCode_CurrencyID,T1.CurrencyCode_DecimalsGeneral,T1.CurrencyCode_DecimalsPrice,T1.Calc_SEBankRef,T1.Calc_CustResaleID,T1.BranchID,T1.CustAgentTaxRegNo,T1.CHISRCodeLine,T1.Calc_CHBankAcctIBANCode,T1.Calc_CHBankAcctISRPartyID, T1.InvoiceRef, T1.CMReason,T1.Calc_DocDspTaxAmt, T1.RevisionNum AS InvcHead_RevisionNum, T1.RevisionDate AS InvcHead_RevisionDate, T1.Calc_IsDraftCopy, T1.FirstPrintDate, T1.Calc_ReverseChargeTax as Calc_ReverseChargeTaxTotal, T1.DocCopyNum, T1.PayDiscDays, T2.AdvanceBillCredit,T2.Company as InvcDtl_Company,T2.DocAdvanceBillCredit,T2.DocDiscount,T2.DocUnitPrice,T2.InvoiceComment as InvcDtl_InvoiceComment,T2.InvoiceLine,T2.InvoiceNum as InvcDtl_InvoiceNum,T2.PackLine,T2.PackNum,T2.DropShipPackSlip,T2.PartNum,T2.POLine,T2.PricePerCode,T2.RevisionNum,T2.SalesUM,T2.SellingOrderQty,T2.SellingShipQty,T2.ShipDate,T2.XPartNum,T2.XRevisionNum,T2.Calc_ActDate,T2.Calc_BackOrdQty,T2.Calc_UnitPrice,T2.Calc_ExtPrice,T2.Calc_Duration,T2.Calc_GetNextLegalNum,T2.Calc_InvcComment,T2.Calc_InvoiceDisplayLine,T2.Calc_IsKitParent,T2.Calc_DspDocLineTax,T2.Calc_JobNumber,T2.Calc_LabDur,T2.Calc_LabMod,T2.Calc_Labor,T2.Calc_LineDesc,T2.Calc_LineSoldToAddressList,T2.Calc_MatDur,T2.Calc_Mate,T2.Calc_MatMod,T2.Calc_Misc,T2.Calc_MiscDur,T2.Calc_MiscMod,T2.Calc_Modifier,T2.Calc_NextLegalNumID,T2.Calc_NumLineByInv,T2.Calc_ponum,T2.Calc_PSLegalNum,T2.Calc_Reference,T2.Calc_SerialNumber,T2.Calc_SerialNumber2,T2.Calc_SerialNumber3,T2.Calc_SerialNumber4,T2.Calc_SerialNumber5,T2.Calc_SerialNumber6,T2.Calc_SerialNumber7,T2.Calc_ShipToAddressList as InvcDtl_Calc_ShipToAddressList, T2.Calc_ShipToContactName as InvcDtl_Calc_ShipToContactName,T2.Calc_ShipToShipVia,T2.Calc_StateTaxID,T2.Calc_WarrDesc,T2.Calc_WhseCode,T2.OrderLine_DisplaySeq,T2.OrderLine_KitFlag,ISNULL(T2.OrderLine_KitPricing,'') AS OrderLine_KitPricing,ISNULL(T2.OrderLine_KitPrintCompsInv,0)AS OrderLine_KitPrintCompsInv,T2.OrderLine_KitsLoaded,T2.OrderLine_OrderLine,T2.PartNum_PartDescription, T2.EpicorFSA, T3.CallComment,T3.CallQty,T3.PartNum as FSCallDt_PartNum,T3.RevisionNum as FSCallDt_RevisionNum,T3.XPartNum as FSCallDt_XPartNum,T3.XRevisionNum as FSCallDt_XRevisionNum,T3.Calc_CProb,T3.Calc_fCallLine,T3.Calc_fCallNum,T3.Calc_InvoiceLine,T3.Calc_InvoiceNum, T4.Calc_InvoiceNum as FSCallMt_Calc_InvoiceNum,T4.Calc_MatNum,T4.Calc_MtPaNum,T4.Calc_MtQty,T4.Calc_MtBillPrice,T4.Calc_MtExtPrice,T4.Calc_MtLinedesc,T4.Calc_MtRevNum,T4.ResReasonCode, T4.Calc_MtCount,T5.RptLanguageID as Label_RptLanguageID,T5.Calc_ActDate as Label_Calc_ActDate,T5.Calc_CreditMemo as Label_Calc_CreditMemo,T5.Calc_Duration as Label_Calc_Duration,T5.Calc_JobNumber as Label_Calc_JobNumber,T5.Calc_LabDur as Label_Calc_LabDur,T5.Calc_Labor as Label_Calc_Labor,T5.Calc_MatDur as Label_Calc_MatDur,T5.RptLiteralsLMaterial as Label_Calc_Mate,T5.Calc_Misc as Label_Calc_Misc,T5.Calc_MiscDur as Label_Calc_MiscDur,T5.Calc_MtExtPrice as Label_Calc_MtExtPrice,T5.Calc_SerialNumber as Label_Calc_SerialNumber,T5.DepositCredit as Label_DepositCredit,T5.DocUnitPrice as Label_DocUnitPrice,T5.InvoiceLine as Label_InvoiceLine,T5.PONum as Label_PONum,T5.ProbReasonCode as Label_ProbReasonCode
,T5.RptLiteralsLBckOrd,T5.RptLiteralsLBillTo ,T5.RptLiteralsLTWTotalTax ,T5.RptLiteralsLComeFrom ,T5.RptLiteralsLContinueInNext ,T5.RptLiteralsLCusPart , T5.RptLiteralsLDate ,T5.RptLiteralsLEMaila ,T5.RptLiteralsLExtPrice
,T5.RptLiteralsLFax ,T5.RptLiteralsLFOB ,T5.RptLiteralsLHdng ,T5.RptLiteralsLInvoice ,T5.RptLiteralsLLegNum ,T5.RptLiteralsLLine ,T5.RptLiteralsLLineRef ,T5.RptLiteralsLof ,T5.RptLiteralsLOurPart ,T5.RptLiteralsLPackSlp ,T5.RptLiteralsLPage ,T5.RptLiteralsLPartDesc ,T5.RptLiteralsLPartRev ,T5.RptLiteralsLPhone ,T5.RptLiteralsLPONum ,T5.RptLiteralsLAULessDeposit ,T5.RptLiteralsLQty ,T5.RptLiteralsLQtyOrd ,T5.RptLiteralsLRev ,T5.RptLiteralsLLinesSubtotal ,T5.RptLiteralsLRounding ,T5.RptLiteralsLMiscChgs, T5.RptLiteralsLSalesKit ,T5.RptLiteralsLSeeBelow ,T5.RptLiteralsLShipTo ,T5.RptLiteralsLShpVia ,T5.RptLiteralsLSlsTxID ,T5.RptLiteralsLSoldToL ,T5.RptLiteralsLTotal ,T5.RptLiteralsLWarrantyA ,T5.RptLiteralsLWHCode ,T5.ShipDate as Label_ShipDate,T5.UnitPrice as Label_UnitPrice,T5.RptLiteralsLSEOCR ,T5.RptLiteralsLNOKID ,T5.RptLiteralsLDiscountPercent ,T5.RptLiteralsLVatNr ,T5.RptLiteralsLlessAdvBill, T5.RptLiteralsLFSAEquipmentPartNum, T5.RptLiteralsLFSAServiceOrderNum, T1.Calc_CustFax, T6.EMailAddress, T6.FaxNum, T6.ResaleID, T5.RptLiteralsLOrdered as Label_Calc_DteOrdrd,T5.RptLiteralsLSalsRep as Label_Calc_SalesPerson,T5.RptLiteralsLTrms as Label_Calc_SalesTerms, T1.Calc_CustEMailAddress, T1.Calc_DropShipPackSlip, T7.InvoiceComment as FSContDt_InvoiceComment ,T7.ContractQty,T7.PricePerUnit,T7.DocPricePerUnit,T7.Calc_ContPrice, T5.PricePerCode as Label_PricePerCode, T5.PartNum as Label_PartNum,T5.ExtPrice as Label_ExtPrice, T1.Calc_PELegalText, T1.Calc_DocInvoiceAmt, T5.RptLiteralsLAmt, T5.Calc_SEBankRef as Label_Calc_SEBankRef, T1.Calc_TaxMethod, T1.SEBankRef, T1.Calc_LessPrepaidDeposits, T5.RptLiteralsLLessPrepDeposits, T5.Calc_ReverseChargeTax as RptLiteralsLReverseChargeTax, T7.ContractLine, T7.PartNum as ContractPartNum, T7.PartNum_PartDescription as ContractPartDescription, T7.ContractNum as ContractNum ,(CASE WHEN EXISTS(SELECT 1 FROM InvcChrg_" + Parameters!TableGuid.Value + " Tmp WHERE Tmp.Company = T1.Company AND Tmp.InvoiceNum= T1.InvoiceNum AND Tmp.InvoiceLine = T2.InvoiceLine) THEN 1 ELSE 0 END) AS 'HasFinCharges', (CASE WHEN T8.Description IS NOT NULL THEN 1 ELSE 0 END ) AS 'HasMiscCharges', T8.[Description], T8.DocMiscAmt, T8.SeqNum, T5.RptLiteralsLMiscChgs, T5.[Description] AS Label_Description,(CASE WHEN T8.SeqNum = T9.MinSeqNum THEN 1 ELSE 0 END ) AS DisplayLineInfo, T5.RptLiteralsLCopy, T5.RptLiteralsLPrintedAt, T5.RptLiteralsLFirstPrinted, T5.RptLiteralsLDraftCopy, T5.RptLiteralsLInternalUse, T10.FSAEquipmentPartNum, T10.FSAServiceOrderNum,T5.Calc_PLWasteRegisterNum as Label_Calc_PLWasteRegisterNum, T8.MiscCode, T8.Calc_ChargeDesc,T5.Calc_CompanyEORINumber as Label_Calc_CompanyEORINumber,T1.Calc_CompanyEORINumber,T1.Calc_CustShipToEORINumber
,T11.PayerRef as BankAcct_PayerRef, T11.TypeCode as BankAcct_TypeCode, T2.OrderNum
, T2.OrderLine, T1.SalesRepList, T12.PhoneNum FROM InvcHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN InvcDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " T12
ON T2.Company = T12.Company AND T2.ShipToCustNum = T12.CustNum AND T2.ShipToNum = T12.ShipToNum
LEFT OUTER JOIN FSCallDt_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.InvoiceLine = T3.Calc_InvoiceLine AND T2.InvoiceNum = T3.Calc_InvoiceNum
LEFT OUTER JOIN FSCallMt_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.CallNum = T4.CallNum AND T3.Calc_fCallLine = T4.Calc_fCallLine AND T3.Calc_InvoiceLine = T4.Calc_InvoiceLine AND T3.Calc_InvoiceNum = T4.Calc_InvoiceNum
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5
ON T1.RptLanguageID = T5.RptLanguageID LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum AND T1.InvoiceNum = T6.Calc_InvoiceNum
LEFT OUTER JOIN FSContDt_" + Parameters!TableGuid.Value + " T7
ON T2.Company = T7.Company AND T2.InvoiceLine = T7.Calc_InvoiceLine AND T2.InvoiceNum = T7.Calc_InvoiceNum
LEFT OUTER JOIN InvcMisc_" + Parameters!TableGuid.Value + " T8
ON T1.Company = T8.Company AND T1.InvoiceNum = T8.InvoiceNum AND T2.InvoiceLine = T8.InvoiceLine
LEFT OUTER JOIN FSAExtData_" + Parameters!TableGuid.Value + " T10
ON T2.Company = T10.Company AND T2.SysRowID = T10.ForeignSysRowID
LEFT OUTER JOIN ( SELECT temp.Company, temp.InvoiceNum, temp.InvoiceLine, MIN(temp.SeqNum) as MinSeqNum
FROM InvcMisc_" + Parameters!TableGuid.Value + " temp
GROUP BY temp.Company, temp.InvoiceNum, temp.InvoiceLine) T9
ON T1.Company = T9.Company
AND T1.InvoiceNum = T9.InvoiceNum
AND T2.InvoiceLine = T9.InvoiceLine
LEFT OUTER JOIN (SELECT DISTINCT Company, BankAcctID, PayerRef, TypeCode FROM BankAcct_" + Parameters!TableGuid.Value + ") T11
ON T1.Company = T11.Company AND T1.OurBank = T11.bankacctid
ORDER BY T1.InvoiceNum, T2.Calc_NextLegalNumID, T2.InvoiceLine, T8.SeqNum
1 Like
Let me give that a try and let you know if it works for me
I added my query in full to my previous post at the bottom.
Note my query is missing the ="" around the sql, you’ll still need that!
So I got it to give me a value, but still I am getting the SoldTo phone number to show on the ShipTo when in fact the numbers should be different
Have you verified they are?
Because I am changing the number for my ship to, and it’s showing up in the report properly.
For testing, I brought it in via linked table to ShipToCust, and get the sold to phone number.
(I believe that is a bug, and we should report it.)
When I bring it in via adding the table and linking with relationships like I showed above, I get the correct ship to phone number.
If you start from scratch, and use the method of adding the table, and you are still getting incorrect
results, I’m not sure what to tell you!
Oh wait, I think I know what the problem is. The ShipToID can be blank which causes issues in the join. Do you always add a Ship To to every customer or do you use the “default” one that gets created with no ID?
That would be expected if that customer did not have a separate Ship To ID.
There is always a ship to, sometimes it can be the default but sometimes manually have to adjust to the correct shiptoid
It did.
I read that as shiptoyed 
1 Like
Yes, I know every Customer has a Ship To. I have had issues in the past with getting the correct Ship To because of the default one that gets created with a ShipToID of “”. I can’t remember the exact situation, but I remember I had to do a CTE in my rdl query to filter out the “” IDs to get what I was doing to work. It has to do with the combination of the CustomerNum, ShipToID, and ConNum if the ConNum is the same for a “” ShipToID and another ShipToID.
I got it to work. The issue was that both the field source for Sold To Phone Number and Ship To Phone Number was “PhoneNum” and the query was also calling it the same way. I just had to reference each one individually in the query and update the field source names
1 Like
That’s a good question.
Thanks Dave!
I knew something was wonky
1 Like
Hello All - I have been reading this one and can’t seem to find a re-solve to my issue…
What I need to do is add a Field to an AR Invoice….
This is what I have done….
- Started with Copied the RDD Files and calling it “ASLARFORMV3” in Pilot.
- Then I unticked the Field Name “ReturnableContainer” as this is the field I need to add to the AR Invoice. (Attached)
- Then I changed the “Data Definition” in the Report Style to the new RDD. (Attached)
- Then I logged onto the Server and opened the SSRS Report in Report Builder.
- With the ReturnableContainer as T1. ReturnableContainer,
- Then I added the Field to the “Data Set Properties” (Attached)
