Best Practice Question - BAQ / Dashboard

(Adam Szymanski) #1

I’m building an AR Customer dashboard that will have four quadrants: 1) Customers with open invoices > 45 (main area… click cust id to feed the next three): 2) Customer Contacts list, 3) AR Aging Summary, 4) Open Invoices.

Should I try to build as much as I can in one BAQ with a bunch of subqueries, or make a separate query for each quadrant?.. I guess my main question is how simple / complex should you allow BAQs to get for things like this? My BAQ list is growing for all sorts of simple trackers and I’m wondering how much I should work to combine queries into one.

Thank you.

(Randy Stulce) #2

I’d probably go with separate BAQs. At least two, one for the first panel –1) Customers with open invoices > 45 ]-- from the others. It would be more efficient to pull all the data for contacts, aging, etc separately from the BAQ that runs the first panel by using publish and subscribe.

(Adam Szymanski) #3

Thanks. Question on my aging need. I need to recreate the over 30, over 60, over 90 aging data. Should each of those be a separate subquery where I add date restrictions on the table criteria OR is there some way to add SUM() + WHERE() in a calculated field?

(Brandon Anderson) #4

If it were me, I would make a calculated field for each of the aging needs where you take the due date, plus the number you want, then check for less than today’s date. You can do that all in one query.

The way I did that query was to actually add the due date plus the terms on the vendor. A generic 30,60,90 doesn’t really mean much if the terms don’t line up with those numbers.

AgedRecievables.baq (20.2 KB)

AgedPayables.baq (20.7 KB)

(these come from 10.2.200 for anyone who wants to look at them)

(Randy Stulce) #5

Like @Banderson, I used calculated field on my Aging BAQ. The calculated field syntax is:

 When Datediff(dd, InvcHead.DueDate, GetDate()) between 0 and 30 then 'Current'
 When Datediff(dd, InvcHead.DueDate, GetDate()) between 31 and 60 then 'Over 30'
 When Datediff(dd, InvcHead.DueDate, GetDate()) between 61 and 90 then 'Over 60'
 When Datediff(dd, InvcHead.DueDate, GetDate())  between 91 and 120 then 'Over 90'
 else 'Over 120'