Epicor 10 BAQs: Running Totals & More!

Ever need a BAQ or Dashboard with one or more running totals or perhaps running totals over different ranges or rows and just didn’t know how to do it, or worse were told it wasn’t possible?

With Epicor 10 running on a MS SQL database BAQs are much more powerful and most of the time you have the entire MS SQL feature set set to work with.  Unfortunately, most Epicor administrator and power users are used to the previous Progress database and are not familiar with the many MS SQL options.  In this post I’m going to go over a quick but useful way to get various running totals in your BAQ results.

When working with the Epicor 10 BAQ Designer, you have to create a calculated field to access many of the Functions needed to get to the data you need.  One of the first things I noticed when I first used the Epicor 10 BAQ Designer was that the Functions list looked very much like it did in Epicor 9 and many of the more advanced functions were not available.  I suspect this is because of two major reasons:

1. To keep the BAQ Functions list familiar to prior Epicor users.
2. To curb the improper use or ‘testing’ of new and unfamiliar functions.

These are very good reasons but if you are familiar with MS T-SQL or are running into perceived limitations with the BAQ Designer, you should know that most of the other SQL functions will work in the BAQ designer, you just need to manually enter them.

Usually if you need an Aggregate Sum(), Avg(), Count(), etc you would be doing these functions across the fields that you mark for Group By, but what if you need something more complex, say multiple groups, or multiple aggregates over different groups?  You may have tried using another subquery or Epicor’s ‘Summary’ table, and this may or may not have worked for you.  That is when the OVER() clause comes in.  The OVER() clause provides a way to perform various functions OVER different sets of data in different ways.  Instead of providing a technical guide to the OVER() clause, I will leave that to Microsoft here and instead provide a few examples:

Say you want a dashboard that will show the total Qty shipped each day with a monthly running total and a monthly Daily average.  This example should demonstrate how various functions can be mixed on the data set and still function independently.

1. Create a new BAQ and add the Part Tran table to the Phrase Build
2. Add a criteria where TranType = ‘STK-CUS’
3. You should also add a date range, start with the last 3 months, where TranDate >= Current Date -3 Months
4. Select the Display Fields and add the TranDate to the Display Columns list and enable Group By
5. Select the Sort Order tab and add TranDate and change the sort to decending by double click on the sort indicator icon.
6. Back on the Column Select tab click on the Calculated Field Editor button
7. Here create 4 calculated fields:
   1. Daily Sum – the sum of TranQty per day, per the Group By set earlier:
      1. Field Name: DailySum
      2. Data Type: Decimal
      3. Label: Daily
      4. Calculation: SUM(PartTran.TranQty)
   2. Month – In order to group the running totals and averages by month we will need a field that provides the month:
      1. Field Name: TheMonth
      2. Data Type: nvarchar
      3. Label: Month
      4. Calculation: Format(PartTran.TranDate, ‘MM/yy’)
   3. Monthly Running Total: Here is where we will use the OVER() clause with the Sum() function to get the monthly running total:
      1. Field Name: MonthlyRT
      2. Data Type: Decimal
      3. Label Monthly Running Total
      4. Calculation: SUM( DailySum ) OVER(PARTITION BY TheMonth ORDER BY PartTran.TranDate)
         • PARTITION BY TheMonth says that we want the sum to be over each month and the ORDER BY says that we want the running total to be ordered by the Transaction date.
   4. Monthly Running Average: Similar to the sum, but instead we want to know what our daily shipping qty average is.
      1. Field Name: MonthlyAvg
      2. Data Type: Decimal
      3. Label Monthly Daily Avg
      4. Calculation: AVG( DailySum ) OVER(PARTITION BY TheMonth ORDER BY PartTran.TranDate)
   5. Save and close out of the Calculated Field Editor
   6. Select the Analyze tab and then select the Analyze button in the lower left corner and make sure we get the ‘Syntax is OK’ to make sure we entered everything properly.
   7. If the syntax is OK, select the Test button to see the results. My test run took about 325ms on average but depending on how many records are in your PartTran dataset, it may take longer.  If you get concerned just click on the ‘X’ button that appears next to the Clear Grid button while a query is running.

As we can see we get exactly what we were looking for, all with a single table query and in my case it runs pretty quick too.  There are many uses for this and you may have thought of some already or perhaps there are old BAQ’s you could improve with this powerful SQL clause.  When you are asked to build a BAQ for a dashboard or BAQ Search, it’s not immediately obvious how to get there and at first it may seem complex, but the more familiar you are with the functions available the easier it becomes.  Here are a few ideas on where the Over() clause can be used:

• Daily, Weekly, Monthly, Yearly Shipping Metrics
• Released Job Material demand by part ordered by Material Req Date
• Daily, Weekly Monthly & Yearly Sales Metrics (by customer, salesman, territory, etc)
• Daily, Weekly, Monthly, Yearly Job Resource production qty.
• Part Transaction count by User or Employee ID
• Post any ideas you have in the comments!

If you need help implementing this or other Epicor solutions, contact me, I’d love to help you get more out of Epicor!  Be sure to like, share & comment!

“You Don’t Know, What You Don’t Know”