Compare Deliveries by City Dashboard

I am trying to create a dashboard to compare number of deliveries and value against city. For example 50 deliveries to London in October with a value of £10,000 compared to 40 in November with a value of £7000. I’m thinking I should use the OTSCustomer City from the OrderHed but how can I get the total deliveries to one city on a single line, ie London, 100 deliveries, Kent 50, Yorkshire 10 etc?
Thanks for any help.
Adrian.

Something like this to get you started? There are a few other ways to pivot the data to do months in columns with multiple value elements, but that is more like proper Pivot tables like Excel or Epicor EDA. It can be done with a Pivot BAQ I think as well, but I don’t like doing that sort of thing inside BAQs since they always want more analysis capabilities and Pivot tables work so much better for that. Plus you can connect EDA, Power BI or Excel directly to the BAQ or SQL DB and make the dashboard live… It’s easy since you are On_prem.

This one just uses the customer address, but you would need to alter the code to select the correct delivery address form the shipment records (or OTS fields, or ShipTo fields of course)

Use [YourDatabase]

select Country, City
	, COUNT(ordernum) as Orders
	, SUM(OrderAmt) as Value
	, sum(Case when MONTH(OrderDate) <= 3 then 1 else 0 end) as Q1_Orders
	, Sum(Case when MONTH(OrderDate) <= 3 then OrderAmt else 0 end) as Q1_Value
        -- repeat for other quarters or months
	, sum(Case when MONTH(OrderDate) > 9 then 1 else 0 end) as Q4_Orders
	, Sum(Case when MONTH(OrderDate) > 9 then OrderAmt else 0 end) as Q4_Value
from erp.OrderHed join Erp.Customer on OrderHed.company=Customer.Company and OrderHed.CustNum=Customer.custnum
where YEAR(OrderDate) = 2020 --and customer.Country='UNITED KINGDOM' 
group by Country, city
Order by Country, City
'''
1 Like

@MikeGross
Thank you very much Mike, that’s perfect, just what I needed.
The only part I struggled with was getting the 'YEAR(OrderDate) = 2020
I could not work out how to add it as a criteria in my BAQ.
I had to use ‘where (OrderHed.OrderDate >= ‘01/01/2020’)’

Super! Glad I could help!

And yes, it depends on where you are doing the calculation inside Epicor. Some places you can just type the formula even though it doesn’t look like you can. Other places you have to use the expression editor…