AR Invoice SSRS

ypatelops · September 28, 2023, 4:41pm

Is there a quick way to add the Ship To Phone Number field to the AR Invoice SSRS report? I have tried adding the ShipToCust table as a Linked Table to the InvcDtl and picked field PhoneNum and included that in both the Dataset Field in SSRS report as well as to the Dataset Query but I keep getting the Sold To/ Bill To PhoneNum value instead

jkane · September 28, 2023, 4:52pm

Include the below field on the InvcDtl table.

ypatelops · September 28, 2023, 5:24pm

I included that column but when I print the report get an error: Invalid column name ‘ShipToContactPhoneNum’.

jkane · September 28, 2023, 5:27pm

You need to also add it to the rdl query and as a field in the report fields.

ypatelops · September 28, 2023, 5:30pm

I did include that to the query as well as add it as a field to the dataset in the rdl file

ypatelops · September 28, 2023, 5:32pm

ypatelops · September 28, 2023, 5:33pm

jkane · September 28, 2023, 5:58pm

You would have to include the whole query text for us to review.

ypatelops · September 28, 2023, 6:03pm

=“SELECT
T1.RptLanguageID,
T1.DueDate,
(Select T12.CustID from Customer_” + Parameters!TableGuid.Value + " T12
WHERE T1.Company = T12.Company AND T1.CustNum = T12.CustNum ) AS CustID,
T1.Company,
T1.CreditMemo,
T1.Calc_ExtPriceTotal,
T1.CustNum,
T1.DocDepositCredit,
T1.DocInvoiceAmt,
T1.Calc_TotalMiscChrg,
T1.Calc_TotalAdvBillCred,
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.CHISRCodeLine,
T1.Calc_CHBankAcctIBANCode,
T1.Calc_CHBankAcctISRPartyID,
T1.Calc_DocDspTaxAmt,
T1.Calc_IsDraftCopy,
T1.FirstPrintDate,
T1.Calc_ReverseChargeTax as Calc_ReverseChargeTaxTotal,
T1.DocCopyNum,
T1.PayDiscDays,
T2.Calc_TrackByAttribute,
T2.AttributeSetShortDescription,
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.ShipToContactPhoneNum,
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_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_WarrDesc,
T2.Calc_WhseCode,
T2.OrderLine_KitFlag,
ISNULL(T2.OrderLine_KitPricing,‘’) AS OrderLine_KitPricing,
ISNULL(T2.OrderLine_KitPrintCompsInv,0)AS OrderLine_KitPrintCompsInv,
ISNULL(T2.OrderLine_KitShipComplete,0)AS OrderLine_KitShipComplete,
T2.OrderLine_KitsLoaded,
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_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.DocUnitPrice as Label_DocUnitPrice,
T5.InvoiceLine as Label_InvoiceLine,
T5.PONum as Label_PONum,
T5.ProbReasonCode as Label_ProbReasonCode,
T5.RptLiteralsLDescription as Label_Reference,
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.RptLiteralsLAttributeSet,
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,
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,
T2.ServiceSource,
T5.RptLiteralsLServiceSource
,T11.PayerRef as BankAcct_PayerRef, T11.TypeCode as BankAcct_TypeCode, T1.OrderNum, T6.BTPhoneNum, T6.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 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, T3.Calc_fCallLine, T4.Calc_MtCount

jkane · September 28, 2023, 6:16pm

Nothing jumps out at me. Are you sure you saved your RDD after making the change?

ypatelops · September 28, 2023, 6:17pm

yes I did save for sure, tested it out multiple times by reuploading rdl back into Epicor

ypatelops · September 28, 2023, 6:42pm

Anything else I should be doing with this field?

Mark_Wonsil · September 28, 2023, 7:00pm

Since you’re on prem, can you check the InvoiceDtl_< whatever your GUID is > table in the SSRS database and see if the field actually exists in the extract?

ypatelops · September 28, 2023, 7:17pm

We are actually on Cloud

jkane · September 28, 2023, 7:24pm

I can’t think of anything else to try.

Mark_Wonsil · September 28, 2023, 7:26pm

May want to update your profile.

Mark_Wonsil · September 28, 2023, 7:26pm

John,

Isn’t there a way to download the Report Data in the cloud now?

ypatelops · September 28, 2023, 7:26pm

Will do! I just noticed that was incorrect on my profile

klincecum · September 28, 2023, 7:41pm

All you should have to do is this on the InvDtl field, then save.

Download the report, and add the field on T2 to the query.
Add the fields.
Reupload.

If you have anything else you added to that query, start over.

ypatelops · September 28, 2023, 7:46pm

Field Name and Field Source should both be ShipToContactPhoneNum, and the query should be T2.ShipToContactPhoneNum? If so, that is exactly the steps I followed and I still seem to get the error of the column being invalid when I print the AR Invoice