I add the Table InvcHead in the Data Definition,but SSRS report dose not show the invchead and it’s filed.why?
You shouldn’t have used the Sync Dataset option there. It will mess up the whole thing. You use that only for new BAQ or SSRS reports you create from scratch. I would start from the beginning and add the fields you want manually instead of using the Sync Dataset. Also, for the relation type I think you need Output and not Definition Only.
Yes, I usually use Output (LEFT JOIN) instead of Definition (INNER JOIN.
Yes, it’s not intuitive, here is a similar topic from earlier in the week that might help.
=“SELECT T5_XRefInvoiceNum as XRefInvoiceNum,T5_XRefContractDate as XRefContractDate,T1.Calc_CustName as ARDtls_Calc_CustName,T2.Calc_LegalNumber,T1.Calc_MulChildren as ARDtls_Calc_MulChildren,T1.Calc_ParentCustID as ARDtls_Calc_ParentCustID,T1.Calc_RptTitle2,T1.Calc_Summary, T2.InvoiceDate,T2.InvoiceNum,T2.PONum,T2.Calc_AgeBaseAll,T2.Calc_AgeCurAll,T2.Calc_AgeInvAmt1,T2.Calc_AgeInvAmt2,T2.Calc_AgeInvAmt3,T2.Calc_AgeInvAmt4,T2.Calc_AgeInvAmt5,T2.Calc_AgeInvAmt6,T2.Calc_AgeLbl1,T2.Calc_AgeLbl2,T2.Calc_AgeLbl3,T2.Calc_AgeLbl4,T2.Calc_AgeLbl5,T2.Calc_AgeLbl6,T2.Calc_BlockedFinChrg,T2.Calc_BlockedRemLetters,T2.Calc_Company,T2.Calc_ContPer,T2.Calc_ContPh,T2.Calc_crMemo,T2.Calc_curDesc,T2.Calc_CurDueDate,T2.Calc_CurrCode,T2.Calc_CustID,T2.Calc_CustName as Calc_CustName,CAST( T2.Calc_Index as nvarchar ) as Calc_Index,CAST( T2.Calc_MulChildren as nvarchar ) as Calc_MulChildren,T2.Calc_ParentCustID as Calc_ParentCustID,T2.Calc_PoDNNbr,T2.Calc_RptARAcctID,T2.Calc_RptUserID,T2.Calc_TierLevelNum,T2.Calc_TopCustID,CAST( T2.Calc_BasCurrency as nvarchar ) as Calc_BasCurrency, T3.TierLevelNum,T3.Calc_ARTotal1,T3.Calc_ARTotal2,T3.Calc_ARTotal3,T3.Calc_ARTotal4,T3.Calc_ARTotal5,T3.Calc_ARTotal6,T3.Calc_LevelCustID,T3.Calc_LevelDesc,T4.Calc_DecimalsGeneral,
(CASE WHEN EXISTS(SELECT TOP 1 * FROM ARPNHead_” +Parameters!TableGuid.Value + " WHERE Calc_InvoiceNum = T2.InvoiceNum) THEN 1 ELSE 0 END) AS [ARPromissoryNotesExist]
FROM ARPrnt_" +Parameters!TableGuid.Value + " T1
join Company_" +Parameters!TableGuid.Value + " T4
on T1.Calc_Company=T4.Company
LEFT OUTER JOIN RlsHead_" +Parameters!TableGuid.Value + " T3
ON T1.Calc_Company = T3.Calc_Company AND T1.Calc_CustID = T3.Calc_CustID
LEFT OUTER JOIN ARDtls_" +Parameters!TableGuid.Value + " T2
ON T1.Calc_Company = T2.Calc_Company AND T1.Calc_ParentCustID = T2.Calc_ParentCustID AND T1.Calc_CustID = T2.Calc_CustID and T1.Calc_RptARAcctID = T2.Calc_RptARAcctID LEFT JOIN InvcHead_" +Parameters!TableGuid.Value + " T5 ON T2.Calc_Company=T5.Company and T2.InvoiceNum=T5.InvoiceNum"
I think the problem is your SSRS join doesn’t match the RDL
this:… ON T2.Calc_Company=T5.Company
should be:… ON T2.Company=T5.Company
Here is a modified RDL file and a Word doc with 3 examples of the select statement
1 - section with the unmodified select statement
2 - section with a modified select statement - where I expanded text to make it easier to follow
3 - final section with the modified select statement - this is what I copied & pasted over the existing SSRS ds expression
These are from a 10.1.6 test system but I think it will be the same for later versions
AgdRcv.rdl (542.7 KB)
ARAgngR01 - DataSet Query.doc (31 KB)
Thank you.I did it your way.