Report Builder - Customer Statements report

AprilGoodlin · October 21, 2025, 5:13pm

I have a report that my predecessor had altered already that I am attempting to adjust some more. We have a company that wants “Shipping Address” and “Buyer Email” added onto the Customer Statements report we have and are using currently. I have added several tables to get this to work. I tried multiple ways and I cannot get the Address or Email to populate on the report when it is run through Epicor.

I changed the RDD to include the tables and fields I need. I added a new report style that I am pulling from to get this to work.

Here is the query I’m running:

I’m just at a loss on what to try next.

estm8ben · October 21, 2025, 5:22pm

What edits to the Select statement in your screen shot did you add to bring in the new fields you want to display?

AprilGoodlin · October 21, 2025, 5:26pm

Here is the original:

I added the tables T5-T7 and the fields for email address, name, address1, address2, city, state, zip, and shiptonum from the ShipTo table

estm8ben · October 21, 2025, 5:34pm

Did you add the fields to the Report file?

AprilGoodlin · October 21, 2025, 5:38pm

I did:

And I attempted to concatenate some of them and it only shows the “,” on the report UI because it’s not pulling the db information.

estm8ben · October 21, 2025, 5:39pm

Looks like you are On Prem.

Did you test your SQL Select statement in SSMS?

AprilGoodlin · October 21, 2025, 5:40pm

I have and the SQL statement works and provides the expected results.

dcamlin · October 21, 2025, 5:49pm

I’m not sure if this is possible or not… I think I’ve tried it but with mixed results… but you’re joining a couple of your tables by linking them to multiple other tables.

From the RDD perspective, your relationships can only join tables 1-to-1. And RDL query should match whatever relationships you created in your RDD.

The first one should be an easy fix, just change T1.Company to T2.Company.

But the bottom join may be causing you fits.

kve · October 21, 2025, 5:51pm

That was my first thought. The other thing would be to confirm the joins in the RDD are correct.

AprilGoodlin · October 21, 2025, 6:00pm

Thank you! I’ve changed this so many times to try to get it to work, I missed those when I was going back. Even changing the bottom to simplify the joins and taking out tables that are not directly called, it fails to provide address information.

AprilGoodlin · October 21, 2025, 6:01pm

The joins in the RDD mirror the environment and the SQL joins listed.

dcamlin · October 21, 2025, 6:04pm

Sorry, I feel like I’m picking on your work… but its all we have to go on

Fields are going to be case sensitive, and these definitely seem wrong.

AprilGoodlin · October 21, 2025, 6:11pm

Please pick away! Sometimes, it’s hard to see the errors on your own! I wrote some of it in SSMS and transferred it over. I have corrected the case on these without results.

dcamlin · October 21, 2025, 6:18pm

Your report RUNS though, right? No errors.

… just, no address results?

AprilGoodlin · October 21, 2025, 6:22pm

Correct. The report runs, but the shipping address remains blank.

dcamlin · October 21, 2025, 6:24pm

You query uses

But your field is declared as:

AprilGoodlin · October 21, 2025, 6:27pm

I have ZipCode as well, above the Name. I threw both in there to be sure it was covered since it wasn’t populating.

dcamlin · October 21, 2025, 6:29pm

Maybe break your address expression into individual fields and see if ANY of them come through individually.

Or even delete all of your fields and add them back one at a time. See if you can get SOMETHING to pass through.

Also saw you have T7.ShipToNum called out twice:

dcamlin · October 21, 2025, 6:31pm

Can you paste your query text in here (not a screen shot)?

AprilGoodlin · October 21, 2025, 6:34pm

="SELECT distinct T1.RptLanguageID,T1.CustID,T1.EMailAddress,T1.FaxNum,T1.Calc_AgeLabel1,T1.Calc_AgeLabel2,T1.Calc_AgeLabel3,T1.Calc_AgeLabel4,
T1.Calc_AgeLabel5,T1.Calc_AgeLabel6,T1.Calc_BillToAddress,T1.Calc_BTContactName,T1.Calc_StatementComment,T1.Calc_StatementDate,
T1.SalesRep_EMailAddress,T1.SalesRep_FaxPhoneNum,T1.SalesRep_Name,T2.CustNum,T2.Company,T2.CurrencyCode,T2.DebitNote,T2.CreditMemo,
T2.DNCustNbr,T2.DocInvoiceAmt,T2.DocInvoiceBal,T2.DocWithholdAmt,T2.DueDate,T2.InvoiceDate,T2.InvoiceNum,T2.InvoiceSuffix,T2.PONum,
T2.Calc_CurrSymbol,T2.CurrencyCode_CurrDesc,T2.InvoiceType,T2.Calc_CheckRef, T3.Company as CashDtl_Company,T3.CheckRef,T3.DocTranAmt,
T3.InvoiceNum as CashDtl_InvoiceNum,T3.InvoiceRef,T3.TranDate,T3.TranType, T3.CustNum as CashDtl_CustNum,T3.Calc_DisplayAmount,
T3.Calc_Reversed,T3.Calc_IsCM,T3.Calc_DocDtlTranAmount, T3.Calc_DocDtlTranAmountSignAdjusted, 
ISNULL(T3.Calc_Charge,T2.Calc_Charge) as Calc_Charge, ISNULL(T3.Calc_CMemo, T2.Calc_CMemo) as Calc_CMemo, 
T3.Calc_TranType, T3.Calc_PayRef, T4.DueDate as RptLabel_DueDate,T4.RptLiteralsLBalanceDue,T4.RptLiteralsLCharge,T4.RptLiteralsLCheck,
T4.RptLiteralsLCredit,T4.RptLiteralsLDate,T4.RptLiteralsLEmail,T4.RptLiteralsLFax,T4.RptLiteralsLInvoice,T4.RptLiteralsLPage,T4.RptLiteralsLPO,
T4.RptLiteralsLSubTitle,T4.RptLiteralsLTotal,T4.SalesRepCode as RptLabel_SalesRepCode,T4.RptLiteralsLPayInv,T4.RptLiteralsLAdjust,
T4.RptLiteralsLCMemo, T4.RptLiteralsLPayInvReverse, T4.RptLiteralsLPhone, ISNULL(T7.EMailAddress, 'No Email') as BuyerEmail, ISNULL(T7.Name, '') as Name, 
ISNULL(T7.Address1, 'No Shipping Address') as Address1, ISNULL(T7.Address2, '') as Address2, ISNULL(T7.City, '') as City, ISNULL(T7.State, '') as State, ISNULL(T7.Zip, '') as Zip, 
T4.RptLiteralsLOF,T4.RptLiteralsLPIID,T4.RptLiteralsLPIType, T8.InvoiceNum, T8.ShipToNum, T7.ShipToNum, T7.CustNum,
T4.RptLiteralsLPIStatus,T4.RptLiteralsLPIDueDate,T4.RptLiteralsLPIAmount,T4.RptLiteralsLPIOutstandingAmt,
T1.Calc_ContactEmailAddress,T1.Calc_ContactFaxNum,
 (CASE WHEN EXISTS(SELECT TOP 1 ARPromNoteID 
 FROM [ARPNHead_" & Parameters!TableGuid.Value & "] WHERE CustNum = T2.CustNum AND CurrencyCode = T2.CurrencyCode) 
 THEN 1 ELSE 0 END) as PaymentInstrumentsExists 
 FROM [Customer_" & Parameters!TableGuid.Value & "] T1
 LEFT OUTER JOIN [InvcHead_" & Parameters!TableGuid.Value & "] T2 
 ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum
 LEFT OUTER JOIN [InvcDtl_" & Parameters!TableGuid.Value & "] T8
  ON T2.Company = T8.Company AND
  T2.Invoicenum = T8.Invoicenum
 LEFT OUTER JOIN [CashDtl_" & Parameters!TableGuid.Value & "] T3 
 ON T2.InvoiceNum = T3.InvoiceNum 
 LEFT OUTER JOIN [RptLabels_" & Parameters!TableGuid.Value & "] T4 
 ON T1.RptLanguageID = T4.RptLanguageID  
 LEFT OUTER JOIN [OrderHed_" & Parameters!TableGuid.Value & "] T5
  ON T2.OrderNum = T5.OrderNum  
 LEFT OUTER JOIN [CustCnt_" & Parameters!TableGuid.Value & "] T6
    ON T5.BTCustNum = T6.CustNum
 LEFT OUTER JOIN [ShipTo_" & Parameters!TableGuid.Value & "] T7 
     ON  T8.ShipToNum = T7.ShipToNum 
     AND T8.Company = T7.Company"```