I have been asked to create a dashboard that will show Mtl Part, summed required qty, and summed XYZ Bin Qty. BUT, it is based on a date range they enter into the dashboard. They only want one line per mtl prat number.
I can have the baq and it has the due due along with the other three fields.
How do I get the data range setup?
How can I get that info summed into one line on the dashboard?
What do you mean by required qty? The quantity of parts in open releases?
What do you mean by getting the total bin qty in a data range? Like when the stuff was added to the bin?
Required qty = sum(JobMtl.RequiredQty)
Bin Qty = sum(PartBin.OnhandQty)
For clarification on the overall process:
The end user opens the dashboard, inputs the date range they want, the info that is below is only based on the data range.
Date = JobHead.DueDate
To restate the problem: You want to know the total materials required, and total materials on hand (and in which bins) for a list of jobs that are due within a given date range.
Right?
Total materials required, and total materials on hand, and if that mtl part has anything in the bin XYX.
All based on the given date range.
Do you only want to know if the quantity on hand is in a specific bin, or do you want the sum of all those parts for each bin they are in?
Do you supply a bin number as a parameter?
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.
