Hey all,
I am trying to find a way to filter out “Expected Work” from the original Priority Dispatch Report. I cannot find what is being used to dictate the difference between “Expected Work” and “Current Work”. Has anyone had success with this in the past? Would I be better served creating a BAQ and making a BAQ report instead?
Thanks for the help!
Dylan
If you look in the RDL file you will see that this formula is how the report is identifying the "Expected Work " section.
=iif( Fields!RegionCode.Value=1 , "** " & First(Fields!RptLiteralsCurrentWork.Value, “RptLabels”) & Chr(160) &*
First(Fields!RptLiteralsCurHeading.Value, “RptLabels”) , *
iif( Fields!RegionCode.Value=2 , "* " & First(Fields!RptLiteralsAvailableWork.Value, “RptLabels”) & Chr(160) &*
First(Fields!RptLiteralsAvaHeading.Value, “RptLabels”) ,
"** " & First(Fields!RptLiteralsExpectedWork.Value, “RptLabels”) & Chr(160) &*
First(Fields!RptLiteralsExpHeading.Value, “RptLabels”)))
Once you take that fomula apart you will see that Expected work should be Region 3. You can filter it out in the RDL. Personally I would add a where clause the the Dataset, that would be much more efficient than filtering inside the RDL.
That is awesome! I was able to get it filtered down to 1 and 2 easy within the RDL, just like you said. But, now you have me intrigued. How and where would I add a where clause within the RDL?
I’m glad that worked for you! Just as a side note I always recommend doing as much processing as possible on the SQL side than in the reporting engine (SSRS or Crystal). To do this filtering on the SQL side is quite simple just open the main Dataset JCR65_ScheduledResources and add a where clause like the one below… Depending on the size of your report I would be curious if you see a time difference in the rendering.
=“SELECT CAST( T1.AssemblySeq as decimal ) as AssemblySeq,T1.[Description],T1.DueDate,T1.EstProdHours,T1.EstSetHours,T1.JobNum,T1.LaborEntryMethod,CAST( T1.Machines as decimal ) as Machines,T1.OpCode,T1.OprQty,CAST( T1.OprSeq as decimal ) as OprSeq,T1.PartNum,T1.ProdLoadHrs,CAST( T1.RegionCode as decimal ) as RegionCode,T1.SchedCode,T1.SetupLoadHrs,T1.StartDate, T2.ResourceGrpID,T2.ResourceID,T2.Calc_JCDeptDesc,T2.Calc_NexResGrp,T2.Calc_PrimaryProd,T2.Calc_PrimarySetup,T2.Calc_ResDesc,T2.Calc_ResGrpJCDept,T2.Calc_RGDesc, T3.AssemblySeq as JobMtl_AssemblySeq,T3.BackFlush,T3.BaseRequiredQty,T3.BuyIt,T3.Company,T3.[Description] as JobMtl_Description,T3.Direct,T3.IssuedQty,T3.JobNum as JobMtl_JobNum,CAST( T3.MtlSeq as decimal ) as MtlSeq,T3.PartNum as JobMtl_PartNum,T3.ReqDate,T3.RequiredQty,T3.WarehouseCode,T3.Calc_MUOM,T3.Calc_RelOpr,T3.Calc_UOM,T3.Calc_OnHandQty,T3.Calc_AllocatedQty,T3.Calc_MtlAllocatedQty, T4.DueDate as PORel_DueDate,CAST( T4.PONum as decimal ) as PONum,CAST( T4.PORelNum as decimal ) as PORelNum,T4.PromiseDt,T4.ReceivedQty,T4.RelQty, T2.NextResourceID
FROM JCR65_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN ScheduledResources_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Calc_Company AND T1.AssemblySeq = T2.AssemblySeq AND T1.JobNum = T2.JobNum AND T1.OprSeq = T2.OprSeq
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T3
ON T2.Calc_Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq AND T2.OprSeq = T3.Calc_RelOpr
LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T4
ON T3.AssemblySeq = T4.AssemblySeq AND T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.MtlSeq = T4.JobSeq WHERE T1.RegionCode <> 3 "
Adding the where statement is pretty nifty! It seems to move a little faster than the filter but not extremely noticeable. Thank you for your help Alex!