SSRS Error when adding condition

Kinetic ERP
1

I modified the customer statement to only show Open Invoices but am getting an error “Query execution failed for dataset 'Customer_InvcHead_CashDtl_RptLabel” when I add the condition
AND T2.OpenInvoice = 1

If I remove the condition I get no errors.
=“SELECT
T1.Terms_Description,
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,
T2.OpenInvoice,
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,
T4.rptliteralslof,
T4.RptLiteralsLPIID,
T4.RptLiteralsLPIType,
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
AND T2.OpenInvoice = 1
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"

2

watch for whitespace at BOL or EOL especially for the lines you MUST have one or the other:

FROM Customer_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN InvcHead_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.CustNum = T2.CustNum
AND T2.OpenInvoice = 1
LEFT OUTER JOIN CashDtl_" + Parameters!TableGuid.Value + " T3
ON T2.InvoiceNum = T3.InvoiceNum
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
1 Like
3

InvcHead.OpenInvoice is a yes/no field in the data dictionary. Try “T2.OpenInvoice IS TRUE” or “T2.OpenInvoice = -1”?

4

You can’t add a condition “AND …” without previously using “where”. If you already have a condition (or more) then you can use ‘AND’ and add another one. But if you don’t, you start with WHERE. So try “WHERE T2.OpenInvoice = 1”

5

Try this:

="SELECT
 T1.Terms_Description,
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,
T2.OpenInvoice,
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,
T4.rptliteralslof,
T4.RptLiteralsLPIID,
T4.RptLiteralsLPIType,
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
 AND T2.OpenInvoice = 1
 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"
6

You can use conditionals in a Join statement.
It just becomes a criteria on the table.

7

Absolutely! You would need to make sure you add it in the correct join, and not just at the end of the query.

8

That worked! Thank you!

2 Likes
9

And whitespace was this issue all I did was ensure there was a space at the start of each line for your original post and put into code block.

1 Like