I’m trying to remove the filters from my sub-report properties designer page and have them in query instead. But it appears I’m not sure how to reference the parameters correctly. Can anyone help? Here’s my code:
…
=“SELECT T1.AssemblySeq,T2.AssemblySeq as SubAsmbl_AssemblySeq,T2.Company,T2.[Description],T2.IUM,T2.JobNum,T2.Parent,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCAsmSeq,T2.Calc_BCPartNum,T2.Calc_DispStatus,T2.RelatedOperation AS RelatedOperation_SubAsmbl,T3.AssemblySeq AS AssemblySeq_JobOper,T3.OprSeq,T4.MtlSeq,T4.PartNum AS PartNum_JobMtl,T4.AssemblySeq AS AssemblySeq_JobMtl,T4.Description AS Description_JobMtl,T4.JobNum As JobNum_JobMtl,T4.RequiredQty As RequiredQty_JobMtl,T4.RelatedOperation As RelatedOperation_JobMtl
FROM JobAsmbl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN SubAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Parent
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum AND T1.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T4 ON T1.Company = T4.Company AND T1.JobNum = T4.JobNum AND T1.AssemblySeq = T3.AssemblySeq
WHERE (T2.Company = @Pm_JobAsmbl_Company OR T2.Company IS NULL AND T2.JobNum = @Pm_JobAsmbl_JobNum OR T2.JobNum IS NULL AND T3.OprSeq = @Pm_JobOper_OprSeq OR T3.OprSeq IS NULL)"
…
Look at the table guid example in that same query. That is a parameter follow that exact same format
I have implemented the code format you advised but still get errors when I run the report. Here’s my updated code:
…
=“SELECT T1.AssemblySeq,T2.AssemblySeq as SubAsmbl_AssemblySeq,T2.Company,T2.[Description],T2.IUM,T2.JobNum,T2.Parent,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCAsmSeq,T2.Calc_BCPartNum,T2.Calc_DispStatus,T2.RelatedOperation AS RelatedOperation_SubAsmbl,T3.AssemblySeq AS AssemblySeq_JobOper,T3.OprSeq,T4.MtlSeq,T4.PartNum AS PartNum_JobMtl,T4.AssemblySeq AS AssemblySeq_JobMtl,T4.Description AS Description_JobMtl,T4.JobNum As JobNum_JobMtl,T4.RequiredQty As RequiredQty_JobMtl,T4.RelatedOperation As RelatedOperation_JobMtl
FROM JobAsmbl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN SubAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Parent
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum AND T1.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T4 ON T1.Company = T4.Company AND T1.JobNum = T4.JobNum AND T1.AssemblySeq = T3.AssemblySeq
WHERE (T2.Company = " + Parameters!Pm_JobAsmbl_Company.Value + " OR T2.Company IS NULL AND T2.JobNum = " + Parameters!Pm_JobAsmbl_JobNum.Value + " OR T2.JobNum IS NULL AND T3.OprSeq = " + Parameters!Pm_JobOper_OprSeq.Value + " OR T3.OprSeq IS NULL)"
…
You are also missing single quotes when filtering on characters fields.
Not accurate: T2.Company = " + Parameters!Pm_JobAsmbl_Company.Value + " OR
Accurate: T2.Company = ‘" + Parameters!Pm_JobAsmbl_Company.Value + "’ OR
Thanks for pointing out the errors. I’ve made the corrections and the query below is what I have now but when I try to run the report, the subassembly section says “Data retrieval failed…”
…
=“SELECT T1.AssemblySeq,T2.AssemblySeq as SubAsmbl_AssemblySeq,T2.Company,T2.[Description],T2.IUM,T2.JobNum,T2.Parent,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCAsmSeq,T2.Calc_BCPartNum,T2.Calc_DispStatus,T2.RelatedOperation AS RelatedOperation_SubAsmbl,T3.AssemblySeq AS AssemblySeq_JobOper,T3.OprSeq,T4.MtlSeq,T4.PartNum AS PartNum_JobMtl,T4.AssemblySeq AS AssemblySeq_JobMtl,T4.Description AS Description_JobMtl,T4.JobNum As JobNum_JobMtl,T4.RequiredQty As RequiredQty_JobMtl,T4.RelatedOperation As RelatedOperation_JobMtl
FROM JobAsmbl_“ + Parameters!TableGuid.Value + ” T1
LEFT OUTER JOIN SubAsmbl_‘“ + Parameters!TableGuid.Value + ”’ T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Parent
LEFT OUTER JOIN JobOper_‘“ + Parameters!TableGuid.Value + ”’ T3 ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum AND T1.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobMtl_‘“ + Parameters!TableGuid.Value + ”’ T4 ON T1.Company = T4.Company AND T1.JobNum = T4.JobNum AND T1.AssemblySeq = T3.AssemblySeq
WHERE (T2.Company = ‘“ + Parameters!Pm_JobAsmbl_Company.Value + ”’ OR T2.Company IS NULL AND T2.JobNum = ‘“ + Parameters!Pm_JobAsmbl_JobNum.Value + ”’ OR T2.JobNum IS NULL AND T3.OprSeq = ‘“ + Parameters!Pm_JobOper_OprSeq.Value + ”’ OR T3.OprSeq IS NULL)”
…
The quote characters (single and double one) are not the proper ones. Here is the adjusted query. Don’t use single quote for numerical value (T3.OprSeq = " + Parameters!Pm_JobOper_OprSeq.Value + " )
=“SELECT T1.AssemblySeq,T2.AssemblySeq as SubAsmbl_AssemblySeq,T2.Company,T2.[Description],T2.IUM,T2.JobNum,T2.Parent,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCAsmSeq,T2.Calc_BCPartNum,T2.Calc_DispStatus,T2.RelatedOperation AS RelatedOperation_SubAsmbl,T3.AssemblySeq AS AssemblySeq_JobOper,T3.OprSeq,T4.MtlSeq,T4.PartNum AS PartNum_JobMtl,T4.AssemblySeq AS AssemblySeq_JobMtl,T4.Description AS Description_JobMtl,T4.JobNum As JobNum_JobMtl,T4.RequiredQty As RequiredQty_JobMtl,T4.RelatedOperation As RelatedOperation_JobMtl
FROM JobAsmbl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN SubAsmbl_’" + Parameters!TableGuid.Value + “’ T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Parent
LEFT OUTER JOIN JobOper_’” + Parameters!TableGuid.Value + “’ T3 ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum AND T1.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobMtl_’” + Parameters!TableGuid.Value + “’ T4 ON T1.Company = T4.Company AND T1.JobNum = T4.JobNum AND T1.AssemblySeq = T3.AssemblySeq
WHERE (T2.Company = '” + Parameters!Pm_JobAsmbl_Company.Value + “’ OR T2.Company IS NULL AND T2.JobNum = '” + Parameters!Pm_JobAsmbl_JobNum.Value + “’ OR T2.JobNum IS NULL AND T3.OprSeq = " + Parameters!Pm_JobOper_OprSeq.Value + " OR T3.OprSeq IS NULL)”