Dashboard properties

If I have a dashboard and it is summarized. How can I customize the summary view. The end user wants the summary from the dashboard. See below:

to just report the summary no detail. They want a separate one for detail.

They do not want the detail in the summary.

Do I need to sum the totals prior in the query for this to give me what they are looking for.

Thanks.

Charles

I would suggest deploying the dashboard (deploy smart client i think its called), use it as an assembly (as opposed to a runtime) and you can place a customization on it.

Chris,

I do deploy it as a Smart Client but do not understand how you would use it as an assembly. Can you walk me through it?

Thanks,

Charles

Assembly is in the Menu Maint. After you do deploy the dashboard, you need to go to Menu Maint and set the dashboard up, using the Dashboard-Assembly in program Type.

Then you can make a customization like any other customization and, in menu maint, added the customization(its at the bottom).

2 Likes

Thanks. I can deploy the dashboards but not menu maint. It is deployed as an assembly. Now I just need to figure out how to just show the summaries in a column view. I’m in customization now and looking into it. Any suggestions on how I can do this?

If you only want the summary data, change the BAQ.

Brad Boes
bradboes@boosterpconsulting.com
231-845-1090

The BAQ to gather this information is very detailed. Not sure I can just do a summary. I’m pulling in job cost, part transactions, job details, etc.

Ok. I have managed to get this dashboard to work but now I’m having difficulty making it match the production detail report. If I post the BAQ can someone help me find out why some jobs are showing all cost and others not?

yes post the query phrase or BAQ

select 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	(SUM (IsNUll(Material_Cost.Calculated_STL,0))) as [Calculated_STLCost],
	(SUM (IsNull(Material_Cost.Calculated_PP, 0))) as [Calculated_PPCost],
	(SUM (IsNULL(Material_Cost.Calculated_PFS_Other,0))) as [Calculated_PFSCost],
	(SUM (ISNULL(Material_Cost.Calculated_MtlCost,0))) as [Calculated_MaterialCost],
	(JobAsmbl.LLAMaterialSubCost + JobAsmbl.LLASubcontractCost + JobAsmbl.TLAMaterialSubCost + JobAsmbl.TLASubcontractCost) as [Calculated_SubCost],
	(PPCost + PFSCost + STLCost + SubCost + LaborCost) as [Calculated_Subtotal],
	(JobAsmbl.LLALaborCost + JobAsmbl.LLAMaterialLabCost + JobAsmbl.TLALaborCost + JobAsmbl.TLAMaterialLabCost) as [Calculated_LaborCost],
	(JobAsmbl.TLABurdenCost + JobAsmbl.TLAMaterialBurCost + JobAsmbl.LLABurdenCost + JobAsmbl.LLAMaterialBurCost) as [Calculated_BurdenCost],
	(Subtotal +  MaterialCost + BurdenCost) as [Calculated_Total],
	[JobHead].[JobClosed] as [JobHead_JobClosed],
	[JobHead].[JobComplete] as [JobHead_JobComplete],
	(JobAsmbl.TLAProdHours + JobAsmbl.LLAProdHours) as [Calculated_Hours]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on 
	JobHead.Company = JobAsmbl.Company
	and JobHead.JobNum = JobAsmbl.JobNum
	and ( JobAsmbl.AssemblySeq = 0  )

left outer join  (select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[JobMtl].[MaterialMtlCost] as [JobMtl_MaterialMtlCost],
	(case when
   
   Part.ClassID = 'PP02' or
   Part.ClassID = 'PP03' 
 then JobMtl.MaterialMtlCost else 0 end) as [Calculated_STL],
	(case when
   
   Part.ClassID = 'PP01' or
   Part.ClassID = 'PP04' or
   Part.ClassID = 'PP05' or
   Part.ClassID = 'PP06' or
   Part.ClassID = 'PP07' or
   Part.ClassID = 'BS'  or
   Part.ClassID is null or
   Part.ClassID = 'PP08'
 then JobMtl.MaterialMtlCost else 0 end) as [Calculated_PP],
	(case when
   
   Part.ClassID = 'AV01' or
   Part.ClassID = 'AV02' or
   Part.ClassID = 'AV03' 
 then JobMtl.MaterialMtlCost else 0 end) as [Calculated_PFS_Other],
	(JobMtl.MaterialMtlCost - PP - STL - PFS_Other) as [Calculated_MtlCost]
from Erp.JobMtl as JobMtl
left outer join Erp.Part as Part on 
	JobMtl.Company = Part.Company
	and JobMtl.PartNum = Part.PartNum
group by [JobMtl].[Company],
	[JobMtl].[JobNum],
	[JobMtl].[PartNum],
	[JobMtl].[MaterialMtlCost],
	(case when
   
   Part.ClassID = 'PP02' or
   Part.ClassID = 'PP03' 
 then JobMtl.MaterialMtlCost else 0 end),
	(case when
   
   Part.ClassID = 'PP01' or
   Part.ClassID = 'PP04' or
   Part.ClassID = 'PP05' or
   Part.ClassID = 'PP06' or
   Part.ClassID = 'PP07' or
   Part.ClassID = 'BS'  or
   Part.ClassID is null or
   Part.ClassID = 'PP08'
 then JobMtl.MaterialMtlCost else 0 end),
	(case when
   
   Part.ClassID = 'AV01' or
   Part.ClassID = 'AV02' or
   Part.ClassID = 'AV03' 
 then JobMtl.MaterialMtlCost else 0 end),
	(JobMtl.MaterialMtlCost - PP - STL - PFS_Other))  as Material_Cost on 
	JobAsmbl.Company = Material_Cost.JobMtl_Company
	and JobAsmbl.JobNum = Material_Cost.JobMtl_JobNum
group by [JobAsmbl].[JobNum],
	[JobHead].[PartNum],
	[JobHead].[PartDescription],
	[JobHead].[ProdQty],
	(JobAsmbl.LLAMaterialSubCost + JobAsmbl.LLASubcontractCost + JobAsmbl.TLAMaterialSubCost + JobAsmbl.TLASubcontractCost),
	(JobAsmbl.LLALaborCost + JobAsmbl.LLAMaterialLabCost + JobAsmbl.TLALaborCost + JobAsmbl.TLAMaterialLabCost),
	(JobAsmbl.TLABurdenCost + JobAsmbl.TLAMaterialBurCost + JobAsmbl.LLABurdenCost + JobAsmbl.LLAMaterialBurCost),
	[JobHead].[JobClosed],
	[JobHead].[JobComplete],
	(JobAsmbl.TLAProdHours + JobAsmbl.LLAProdHours)

Great, Which field is not giving expected results? What results are you thinking it should be?

The’ jobmtl.materialmtlcost’ in the Production detail report show as $242K, however when I break it out into the STL, PP, PFS (based on part class), and remaining Materials, my totals are $239K. When I removed the calculated fields for the STL, PP, PFS and left just the materials, I still come up $239K. But on another job, the BAQ is exactly what shows on the production detail report. I am missing something.

How does this work? as your Material_Cost part of the table?

I am taking off for the rest of the day. Keep me updated. Post the BAQ if you run into trouble.

select JobMatCosts.JobMtl_Company, 
JobMatCosts.JobMtl_JobNum, 
JobMatCosts.JobMtl_PartNum, 
Part.ClassID,
SUM([JobMatCosts].[JobMtl_MaterialMtlCost]) as [JobMtl_MaterialMtlCost],
SUM	(case when Part.ClassID = 'PP02' or Part.ClassID = 'PP03' then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_STL],
SUM	(case when Part.ClassID = 'PP01' or Part.ClassID = 'PP04' or Part.ClassID = 'PP05' or Part.ClassID = 'PP06' or
   Part.ClassID = 'PP07' or Part.ClassID = 'BS'  or Part.ClassID is null or Part.ClassID = 'PP08' then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_PP],
SUM	(case when Part.ClassID = 'AV01' or  Part.ClassID = 'AV02' or  Part.ClassID = 'AV03' then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_PFS_Other]
/*SUM	(JobMtl.MaterialMtlCost - PP - STL - PFS_Other) as [Calculated_MtlCost]
*/
from
(select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	SUM([JobMtl].[MaterialMtlCost]) as [JobMtl_MaterialMtlCost]
from Erp.JobMtl as JobMtl
group by [JobMtl].[Company], [JobMtl].[JobNum], [JobMtl].[PartNum]) as JobMatCosts
inner join Erp.Part as Part on  JobMatCosts.JobMtl_Company = Part.Company and JobMatCosts.JobMtl_PartNum = Part.PartNum
group by JobMatCosts.JobMtl_Company, 
JobMatCosts.JobMtl_JobNum, 
JobMatCosts.JobMtl_PartNum, Part.ClassID

In Job Tracker - Job Details > Assemblies > Costs or Part Transaction History Tracker are there any burden or subcontract costs?

If you use SSMS query PartTran for the job you are reviewing and look for your delta.

I do pull sub contractor cost from the jobassm table as well as burden. Basically I have inner subquery calculating the columns for three separate part classes which roll into the material cost.

Greg,

What is a SSMS query PartTran?

SQL Server Mgt Studio.

Select top 100 * From PartTran
where jobnum = ‘your job’

You could do the same with JobMtl

gives you the raw data from the job’s transactions to peruse. Check the job detail report against the raw data for any clues. Epicor must be using another value sometimes which is why one matches and another doesn’t.

I’ll try and download it and see what it tells me.

I cleaned up the query a bit. Does this help? Run this in a sql editor to see if this is giving expected results. If you are still having issues, upload the BAQ to the thread.

select 
JobMatCosts.JobMtl_Company, 
JobMatCosts.JobMtl_JobNum, 
JobMatCosts.JobMtl_PartNum, 
Part.ClassID,
SUM([JobMatCosts].[JobMtl_MaterialMtlCost]) as [JobMtl_MaterialMtlCost],
SUM	(case when Part.ClassID IN ('PP02', 'PP03')  then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_STL],
SUM	(case when Part.ClassID IN ('PP01', 'PP04', 'PP05', 'PP06', 'PP07', 'PP08', 'BS')  or Part.ClassID is null then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_PP],
SUM	(case when Part.ClassID IN ('AV01', 'AV02', 'AV03') then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) as [Calculated_PFS_Other],
SUM	([JobMatCosts].[JobMtl_MaterialMtlCost] 
      - (case when Part.ClassID IN ('PP01', 'PP04', 'PP05', 'PP06', 'PP07', 'PP08', 'BS')  or Part.ClassID is null then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) 
      - (case when Part.ClassID IN ('PP02', 'PP03')  then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end) 
      - (case when Part.ClassID IN ('AV01', 'AV02', 'AV03') then [JobMatCosts].[JobMtl_MaterialMtlCost] else 0 end)) as [Calculated_MtlCost]
from Erp.Part as Part
inner join 
  (select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	SUM([JobMtl].[MaterialMtlCost]) as [JobMtl_MaterialMtlCost]
  from Erp.JobMtl as JobMtl
  group by [JobMtl].[Company], [JobMtl].[JobNum], [JobMtl].[PartNum]) as JobMatCosts 
    on JobMatCosts.JobMtl_Company = Part.Company and JobMatCosts.JobMtl_PartNum = Part.PartNum
group by JobMatCosts.JobMtl_Company, JobMatCosts.JobMtl_JobNum, JobMatCosts.JobMtl_PartNum, Part.ClassID

Ken,

I cleaned it up according to the query you presented and it works great. I ran through several jobs and it provided the totals found in the production detail. Thanks for the assist.

Charles

1 Like