BAQ / Dashboard to show total number of open Cases

Epicor ERP 10
1

I have a dashboard to show Case status for a call group. I would like to be able to show total open cases for the group and total by Case Owner.

I think I’m ok with the filtering to get owner vs. group but how do I get the total? I tried a Calculated Field using Aggregate >Count but get errors referring to grouping.

Ideally I would like to show totals in a Chart or Gauge but would settle for showing in a new Grid View.

Thanks in advance,
Todd

2

You’ll need two separate BAQs. One that shows the details, and another the summaries. In your dashboard you can link the two so that when a row in the primary grid is selected, the secondary grid is filtered by a value in the selected row of the primary grid.

3

Consider BAQ OdrerSummary consisting of OrderHed joined to OrderDtl, with columns:

  • OrderHed.OrderNum (Group by)
  • Calc field LineCount = COUNT(OrderDtl.OrderLine)

A dashboard of that would show one row for each order, with a two columns.

A second BAQ (OrderDetail) is of the OderDtl table, showing OrderNum, OrderLine, PartNum, OrderQty.

This second BAQ is added to the dashboard, with filtering OrderDtl.OrderNum equal to the OrderHed.OrderNum from the primary BAQ.

4

Thanks for the quick reply Calvin it was very helpful. Obviously I’m a noob with lots to learn. I love this site!

I get the Primary / Secondary relationship and have been using that for a while in other dashboards.
I can’t say I fully understand why I have to group by OrderNum (would it not be unique anyway?) in order to count the lines on each order so I’m still getting an error and not able to set up my solution with Total Open Cases.

I am including screen shots with some notes/ labels on them that will hopefully clarify what I’m trying to do. I’m really looking for Summaries but I want the result in a cell instead of at the bottom of the column.

Thank you again!

image
image1022×470 23.2 KB

image
image1289×780 188 KB

image
image756×280 5.5 KB

5

As soon as a subquery uses an Aggregate function (COUNT(), SUM(), MAX(), etc…) all other non-aggregate fields in the subquery must be marked Group BY

edit

And there’s no requirement for the Summary BAQ to drive the details BAQ. You could have the Details BAQ as the primary one, and the Summary driven by it. But that doesn’t flow logically. The Summary would only show records for the matching row in the details

-----Details BAQ pane-----
CaseNum  Owner
=======  ========
    123  John
>   456  Paul  (selected row in primary pane)
    789  Ringo
    246  Paul
    810  Paul
   1214  Ringo
   1284  John
-----End of Details BAQ pane-----

-----Summary BAQ pane-----
Num Cases  Owner
=========  ========
        3  Paul
-----End of Summary BAQ pane-----

Selecting either John row in the upper pane changes the lower pane to

-----Summary BAQ pane-----
Num Cases  Owner
=========  ========
        2  John
-----End of Summary BAQ pane-----

Where as if the Summary was the primary BAQ, you’d see something like:

-----Summary BAQ pane-----
Num Cases  Owner
=========  ========
        2  John
>       3  Paul (selected row in primary pane)
        2  Ringo
-----End of Summary BAQ pane-----

-----Details BAQ pane-----
CaseNum  Owner
=======  ========
    246  Paul 
    456  Paul
    810  Paul
-----End of Details BAQ pane-----