Sales BAQ Report

Epicor ERP 10
1

I’m working on creating a BAQ and have run into a wall. I’m looking to create a sales report by employee that includes Date, Quote number, Quoted amount, Sales rep, Calculated Cost and the Calculated Profit Margin. I can find everything except the Calculated Cost and Profit Margin fields. They are not listed in the BAQ designer. Least not that I can see. Anyone have any advice on where I can find these fields or an easier way to generate such a report?

2

Can I just ask where do you Calculated Cost and the Calculated Profit Margin?

I think if they are Calculated field, meaning they are calculated on the fly, they are not stored in a database. To have those values, you have to calculate them too. Therefore, sometimes if you can use existing RDD, some of the calculation is done for you.

1 Like
3

That’s what I was wondering. Thank you!

4

Hello @CasterConcepts

Just wondering how you ended up doing this, I have been requested to do the same thing

5

@LBARKER @CasterConcepts

When I put together sales dashboards that I want to get revenue, cost, and margin - I have leveraged the InvcDtl (Invoice Line) table. It has the revenue and cost (broken down into the five elements). You will have to calculate the margin using calculated fields.

I have found also leveraging the advanced grouping in the BAQ so I can produce the totals by Salesperson, Customer, Product Group, etc.

Bruce

6

Are you asking about quoted costs and profit from the quote tracker worksheet screen, like below?

image

This is what I was referring to when I was working on this project. These are temporary calculated fields, so you’re not able to find them in the Query designer by default. What I did to get around this was to create new custom fields on the QuoteQty table (QtLabor, QtBurden, QtMaterial, etc) and then customized the quote entry screen to populate those fields when the “Quoted” checkbox becomes checked or the quoted qty is changed. Then I was able to use those fields in my query.

image

My function code to update fields in Quote Entry customization:

	public void UpdateCost()
	{
		EpiDataView qm = (EpiDataView)oTrans.EpiDataViews["QuoteQty"];
		EpiDataView qDtl = (EpiDataView)oTrans.EpiDataViews["QuoteDtl"];
		if (qm !=null && qm.Row > -1 && qDtl !=null && qDtl.Row > -1)
		{
		Decimal QtBurden = (decimal)qm.dataView[qm.Row]["TotalBurCost"];
		Decimal QtLabor = (decimal)qm.dataView[qm.Row]["TotalLbrCost"];
		Decimal QtMaterial = (decimal)qm.dataView[qm.Row]["TotalMtlCost"];
		Decimal QtSubContract = (decimal)qm.dataView[qm.Row]["TotalSubCost"];
		Decimal QtMtlBurden = (decimal)qm.dataView[qm.Row]["TotalMtlBurCost"];
		Decimal QtUnitPrice = (decimal)qm.dataView[qm.Row]["DocUnitPrice"];
		
		qm.dataView[qm.Row].BeginEdit();
		qm.dataView[qm.Row]["QtBurden_c"] = QtBurden;
		qm.dataView[qm.Row].EndEdit();

		qm.dataView[qm.Row].BeginEdit();
		qm.dataView[qm.Row]["QtLabor_c"] = QtLabor;
		qm.dataView[qm.Row].EndEdit();

		qm.dataView[qm.Row].BeginEdit();
		qm.dataView[qm.Row]["QtMaterial_c"] = QtMaterial;
		qm.dataView[qm.Row].EndEdit();

		qm.dataView[qm.Row].BeginEdit();
		qm.dataView[qm.Row]["QtSubContract_c"] = QtSubContract;
		qm.dataView[qm.Row].EndEdit();

		qm.dataView[qm.Row].BeginEdit();
		qm.dataView[qm.Row]["QtMtlBurden_c"] = QtMtlBurden;
		qm.dataView[qm.Row].EndEdit();

		oTrans.Update();

		}
		else
		{
		}
	
	}
1 Like
7

Yes, thats what I was asking about… only problem is, some of our quotes have Stock lines as well as Manufactured lines… on the Stock lines the worksheet is blank

8

Have you looked at the PartCost table to see if it has any costing data?

image