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”

Let’s Get Started:

Schedule A Phone Call

7 Comments to Epicor 10 BAQs: Running Totals & More!

  1. This is very insightful and opens up a whole new set of thinking about BAQ’s. Do you have an example of a draw down (Inventory aging)? In this case we would place the Inventory onhand to last receipts and reduce as we went from receipt to receipt?

    • Rick says:

      John,
      I do not have an example of a draw down like that, but it should be possible.
      Another option for Inventory Aging would be to leverage Epicor’s ‘Stock Provision Report Format’ to specify the aging buckets and then use the BAQ to Pivot on those values.
      Kinda tricky but it can be done.

  2. Ellen H-J says:

    Great article and example. Thank you.
    Please correct <= in Step 2, ‘TranDate <= Current Date -3 Months’ to be ‘TranDate >= Current Date -3 Months’

    • Rick says:

      Ellen, I’m glad you found it helpful!
      Thanks for pointing out the error in Step 3 😉
      I have made the correction.

  3. Todd Wooten says:

    Outstanding!!
    I have been tasked with developing a dashboard to show CSR totals by Rep. per day/wk/mo/yr. I believe this is the key that just unlocked the door for me.
    Thanks you Rick!

  4. Dorothy says:

    I’m not sure if this will help my situation.
    I’m trying to total each type of labor.dtl_OpCode by type of hour (OpCode) for each open job number. Everything displays, but OpCode is one column only so I don’t know how to calculate each type separately.
    I have only a basic understanding of BAQ’s and have never used a calculation field.

    Could you help me?

  5. cheap iptv hack

    Enjoyed every bit of your article post.Really looking forward to read more. Awesome.

Leave a Reply

%d bloggers like this: