Yes, only one specific bin. The bin number is hard coded into the baq as it doesn’t change.
I’m in 10.2.700 so I am unable to load.
select
[JobMtl].[PartNum] as [JobMtl_PartNum],
[PartBin].[BinNum] as [PartBin_BinNum],
(sum(JobMtl.RequiredQty)) as [Calculated_TotReq],
(sum(PartBin.OnhandQty)) as [Calculated_TotalOnHand]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobMtl as JobMtl on
JobAsmbl.Company = JobMtl.Company
and JobAsmbl.JobNum = JobMtl.JobNum
and JobAsmbl.AssemblySeq = JobMtl.AssemblySeq
inner join Erp.PartBin as PartBin on
JobMtl.Company = PartBin.Company
and JobMtl.PartNum = PartBin.PartNum
and ( PartBin.BinNum = 'FLR' )
where (JobHead.DueDate >= @startDate and JobHead.DueDate <= @endDate)
group by [JobMtl].[PartNum],
[PartBin].[BinNum]
Oh, I forgot one part. They do not want to use a parameter. This will be added into an existing dashboard and they want to select the date range in the dashboard using a tracker. My issue they would be, how do I get the date ranges so they can use the tracker, and then how would i sum the info in the dashboard? Honestly, I am not sure it can be done.
Ugghh… Dang management and their “needs”!
Of course this is possible. I think you would use something like the BAQ I posted above. Instead of parametrizing the date, just leave the date wide open. Your BAQ will have to return everything. But in your dashboard, you will programmatically add parameters back in so that you don’t have to return the whole dang set of parts. In your dashboard, you have to create a customization if you haven’t already. Then take a look at this old series where I use data from my dashboards to update the BAQ that feeds a grid view.
Filtering epiUltraGrid with a BAQComboBox - ERP 10 - Epicor User Help Forum (epiusers.help)
The key here is that the base BAQ returns everything. But we never run that BAQ without first applying the parameters.
Good Luck!!
In your BAQ make two calculated fields for less than or equal and greater than for equal
Set the calculated fields to be equal to your real date field
In your filters on the dashboard, set the calculated fields as your filters for less than or equal etc.
If you’re slick, you only need one extra field.
I’m not sure I understand the sum thing. Finish your baq and filters and post a pic so we can see.
This is the BAQ. I was asked to use the original one as the data from it was perfect. So, I just simply added PartBin to get the OH Qty for Bin XYZ.
select
[JobHead].[DueDate] as [JobHead_DueDate],
[JobMtl].[PartNum] as [JobMtl_PartNum],
(sum(JobMtl.RequiredQty)) as [Calculated_RequiredQty],
[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
and ( JobOper.OpCode = 'KIT' )
inner join Erp.JobMtl as JobMtl on
JobOper.Company = JobMtl.Company
and JobOper.JobNum = JobMtl.JobNum
and JobOper.OprSeq = JobMtl.RelatedOperation
and ( JobMtl.IssuedComplete = 0 )
left outer join Erp.JobProd as JobProd1 on
JobMtl.Company = JobProd1.Company
and JobMtl.JobNum = JobProd1.TargetJobNum
and JobMtl.MtlSeq = JobProd1.TargetMtlSeq
inner join Erp.PartBin as PartBin on
JobMtl.Company = PartBin.Company
and JobMtl.PartNum = PartBin.PartNum
and ( PartBin.BinNum = 'rec' )
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
and ( JobProd.OrderNum > 0 )
left outer join Erp.OrderHed as OrderHed on
JobProd.Company = OrderHed.Company
and JobProd.OrderNum = OrderHed.OrderNum
left outer join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.JobPart as JobPart on
JobProd.Company = JobPart.Company
and JobProd.JobNum = JobPart.JobNum
and JobProd.PartNum = JobPart.PartNum
left outer join Erp.Part as Part1 on
JobHead.Company = Part1.Company
and JobHead.PartNum = Part1.PartNum
where (JobHead.JobComplete = 0 and JobHead.JobReleased = 1 and not JobHead.PartNum like 'BCOV%')
group by [JobHead].[DueDate],
[JobMtl].[PartNum],
[PartBin].[OnhandQty]
Here is a pic of the dispaly fields.
After i get the data I am looking for here, I need to show it in a dashboard like this:
Mtl Part Number, Required Qty (this is summed so that all Qty among the date range will ebb added in), OnHandQty (only need the first one of this as its the same for all parts in the list).
That would work, except for the adding stuff up. If you filter the dashboard with the tracker it filters this after the summing. You need a parameter to limit before the summing.
Are they wanting to use this like timephase? The parts on hand in that “REC” bin today may not match in a week or two when the job is due as the parts may get used by other orders.
From my understanding, since I am wanting the data being shown to not have any dates but have one line per part number, the required qty summed, but I need to use the date range for populating the data, a dashboard would not work, correct?
Basically, yes. They just want to have an easy way to look at mtl parts in a data range and see the OH qty along with the Req qty.
You can hide columns in the dashboard grid view but still have them in the BAQ to use for filtering.
Again, the OHQ in partbin at runtime of the dashboard may not match what the OHQ will be for the date they select on the dashboard.
That is what I was thinking. And yes, the OH qty will change.
He can do the summing in the dashboard instead.
You’d have to do that in code though.
Is there an easy way for someone with my current knowledge to get this done? Or can I just create a report with this baq, get it the way I want it, and then put the report in the dashboard?
Well, I can’t say I have a handle of what they’re really looking for to answer that question.
If they’re wanting the current partbin OHQ no matter what date they’re selecting then it’s easier to accomplish with a simple BAQ.
Since you’re saying they’re selecting a date in the dashboard, it sounds like they want a dashboard version of the Time Phased Mtl Requirements report. This is more complex.