I am trying to add a Lot number Column to the Raw materials sub report on the job traveler report.
I started by copying the Report data definition. then Updating the job traveler to use the new report data definition. I added a PartTran table. removed Company, JobNum, AssemlbySeq, PartNum fields from the excluded list. then created a relationship between Jobmtl and PartTran tables. After that I modified the Query on the JobMtl Dataset on “Job Traveler raw materials subreport RDL” so that the PartTran table is joined to the JobMtl table. but this will break the report. I get a pdf output but the subreport section shows “subreport could not be shown” error.
If there is someone who knows modifying report definition to join an extra table, could you please help?
Mr. Dilanka: Could you paste your Select statement from the raw materials rdl?
The syntax for ssrs is very unforgiving - a simple space can “break” the subreport.
yes, that I agree with. And my other difficulty learning SSRS report customization was the number of places that I had to change things for a fairly simple change.
However, after joining the PartTran table in the RDD, I ran the SSRS report without updating the sub report query and it worked. so, it’s got to be my query changes that’s breaking my sub report. below is the query.
=“SELECT T1.JobNum, T1.Calc_MultiJobParts, T2.AssemblySeq, T2.BackFlush, T2.Company, T2.Description, T2.IssuedComplete, T2.IssuedQty, T2.IUM, T2.JobNum as JobMtl_JobNum, T2.MfgComment, T2.MtlSeq, T2.Ordered, T2.PartNum, T2.RelatedOperation, T2.RequiredQty, T2.WarehouseCode, T2.Calc_BCMtlSeq, T2.Calc_BCPartNum, T2.JobNum_PartDescription, T3.PartNum as TTJobPartMtl_PartNum, T2.Calc_InventoryQty, T2.Calc_InventoryUOM, T4.Lot FROM JobHead_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum LEFT OUTER JOIN TTJobPartMtl_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.MtlSeq = T3.MtlSeq LEFT OUTER JOIN PartTran_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.JobNum = T4.JobNum AND T2.AssemblySeq = T4.AssemblySeq AND T2.PartNum = T4.PartNum"
Hi Dave, sorry about the delays in reply as I believe we are in the opposite sides of the world
Yes I had some errors corrected and fixed my Syntax, but the actual error was a weird character somewhere in the query. Still dont know what it is…
Because on the sql server I can get the results on my PartTran table. But the SSRS report would throw the error for my subreport. I had to rewrite the query again.
Below code worked for me:
=“SELECT T1.JobNum,T1.Calc_MultiJobParts, T2.AssemblySeq,T2.BackFlush,T2.Company,T2.[Description],T2.IssuedComplete,T2.IssuedQty,T2.IUM,T2.JobNum as JobMtl_JobNum,T2.MfgComment,T2.MtlSeq,T2.Ordered,T2.PartNum,T2.RelatedOperation,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCMtlSeq,T2.Calc_BCPartNum,T2.JobNum_PartDescription, T3.PartNum as TTJobPartMtl_PartNum, T2.Calc_InventoryQty, T2.Calc_InventoryUOM, T4.LotNum FROM JobHead_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum LEFT OUTER JOIN TTJobPartMtl_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.MtlSeq = T3.MtlSeq LEFT OUTER JOIN PartTran_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.JobNum = T4.JobNum AND T2.MtlSeq = T4.MatNum AND T2.AssemblySeq = T4.AssemblySeq"
