BAQ with sums

Epicor ERP 10
1

Hi all! I was trying to create a new BAQ for the Intake Ordes by the Month.
So far I created a BAQ that displays me

Order Number Customer Number Order Date Customer Amount (DocOrderAmount)

The only paramater that i use is the ‘Customer.State’ that I need to separate all the markets.
But that’s not what I need.

I don’t need all the info of the orders, but only the total of all the ‘Customer Amount’ within the month of the last 3 years, like this:

State Sum of ‘DocOrderAmount’ of all of customer in here in Month '19 Sum of ‘DocOrderAmount’ …Month '18 Sum of ‘DocOrderAmount’ …Month '17

Is there a way to do this? Thanks!

2

A Pivot Query would do this.
https://epicweb.epicor.com/Education/OnlineHelpFeatureSummary/Epicor%20ERP%2010/10.2.200/enu/Standard/BusinessActivityQuery/UsePivot.html

3

If you only want the totals for one month of each year:

If you have two tables in your query, you can filter it to only include orders from the same month. Do this in Table Relations so you can use an expression on both sides. When I made this as a test, I joined the Customer table, and set the Customer field/expression to “Month(Getdate())” and the OrderHed field/expression to “Month(OrderDate)”.

In Display Fields, check “Group By” for State and remove the other fields from display. Create three calculated expressions:
SUM (CASE WHEN Year(OrderHed.OrderDate) = Constants.Year then OrderHed.DocOrderAmt else 0 END)
SUM (CASE WHEN Year(OrderHed.OrderDate) = (Constants.Year - 1) then OrderHed.DocOrderAmt else 0 END)
SUM (CASE WHEN Year(OrderHed.OrderDate) = (Constants.Year - 2) then OrderHed.DocOrderAmt else 0 END)

Notes: If you can’t filter out orders from other months in Phrase Build because you’re only using one table, you’ll have to include that condition in your calculated expressions. If you want to group by ShipByDate or other, replace OrderDate with it.

If you want EVERY month for the last three years, then either do a pivot query; output all the data to SSRS and use a matrix to get this format; or ask your higher-ups if an executive dashboard would be acceptable.

4

Hi Ashley, thanks again for your support.
Can you elaborate on the first paragraph? When I am in the Table Relation between Customer and OrderHed I don’t understand what you mean with “set the Customer field/expression … and the OrderHed to…”. How can I set like this in the Relation?

5

You can type freely in the Table Relations fields, so it’s one way to get around the limitations of Table Criteria and SubQuery Criteria.

Month(<date>) gets the integer value of the month of the given date, and GetDate() is the SQL function for getting today’s date. So we’re saying, only include orders placed in the same month as today, regardless of year.
image

6

Ok I understand now, thanks again Ashley. Always on top!