A/R Grouped by Month Query (like dashboard)

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

A/R Grouped by Month Query (like dashboard)

Post by KevinRossen » Wed May 21, 2014 11:58 am

I'm guessing I'll need to order a custom query for this, but I'd like be able to export the data for A/R for the past 12 months, grouped by month. Basically I'd like to export the data to CSV so I can play with it in Excel.

Anyone have something like that? I didn't see one in the query examples.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: A/R Grouped by Month Query (like dashboard)

Post by tgriswold » Fri May 23, 2014 3:40 pm

The AR report on the dashboard isn't so much "Grouping" by month, as it is running an AR report AsOf that month's date, and then plotting the values. You could do the same thing. You can either run an AR report (query examples #666, #718 or any other) with an AsOf date set at the top to be the first/last of the month. You would then run that report multiple times, once for each month you want data for.

Alternatively, if you just want to see exactly what the dashboard is showing, you can query the table that is storing those dashboard AR values (assuming they have been calculated already). A query to give you the values that the dashboard is using is as follows:

Code: Select all

SET @FromDate='2013-12-31', @ToDate='2014-04-30';
SELECT DateCalc, BalTotal, InsEst 
FROM dashboardar
WHERE DateCalc BETWEEN @FromDate AND @ToDate
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply