How about this report?

For users or potential users.
Post Reply
fishdrzig
Posts: 433
Joined: Tue Oct 07, 2008 12:46 pm

How about this report?

Post by fishdrzig » Mon Dec 14, 2015 4:59 pm

I have someone interested in looking at my practice and he needs just one report from Open Dental. How embarrassing when I can't produce it. Went through all the queries and even called tech support. Can anyone recommend a solution. He is coming in on Friday. I thought this would be so easy, but it doesn't look good!!! Frustrating.

Annual production reports for 3 years and year-to-date for the current year showing production, adjustments collections by provider by procedure (or by procedure by provider), this should be a report by ADA codes.

Can this be done with Open Dental??

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: How about this report?

Post by rhaber123 » Mon Dec 14, 2015 10:27 pm

Click on Reports / Production and Income / More Options
Here you can print REPORTS showing productions, adjustments, and collections , Annual production reports and year-to-date reports by provider if needed too
Last edited by rhaber123 on Tue Dec 15, 2015 9:59 am, edited 8 times in total.

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: How about this report?

Post by rhaber123 » Mon Dec 14, 2015 10:34 pm

top 100 procedures yearly
change the dates accordingly

SET @FromDate='2015-01-01' , @ToDate='2015-12-31';
SELECT Count(*),pv.Abbr, pc.ProcCode, pc.AbbrDesc, AVG(pl.ProcFee) as '$Ave. Fee' , SUM(pl.ProcFee) AS '$Tot. Prod' FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pv ON pl.ProvNum=pv.ProvNum
WHERE pl.ProcStatus = '2' AND (pl.ProcDate >=@FromDate
AND pl.ProcDate <= @ToDate)
GROUP BY pc.ProcCode
ORDER BY Count(*)DESC Limit 100

User avatar
Jandrew
Posts: 17
Joined: Thu Jun 11, 2015 2:45 pm

Re: How about this report?

Post by Jandrew » Tue Dec 15, 2015 8:07 am

If you haven't found anything that suits your needs, please feel free to submit a query request online outlining exactly what you need from your report. If you mention that you need the report by Friday, we will do our best to accommodate your needs (though expedited reports tend to be more costly). If you find a report that is similar to what you want and needs only minor changes, our team may even be able to do it free of charge. As always, please don't hesitate to call us if you have any questions, or even if you would like someone to step through the query request process with you.
Programmer (n.): A machine that turns coffee into code.

Andrew Jansen
Open Dental Software
Software Engineer
andrew@opendental.com
503-363-5432

User avatar
Jandrew
Posts: 17
Joined: Thu Jun 11, 2015 2:45 pm

Re: How about this report?

Post by Jandrew » Tue Dec 15, 2015 8:17 am

I was just looking through some of the query examples and found #1022. Is this close to what you want?
You can edit the dates at the top to what you want them to be.


/*1022 Procedure code totals by ProcCode and provider for completed procedures with completion date in date range.*/
/*Showing production, writeoffs, adjustments, and net production*/
/*Query code written/modified: 10/30/2014*/
SET @FromDate='2014-01-10', @ToDate='2014-01-10';
SELECT A.Provider,A.ProcCode,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode
Programmer (n.): A machine that turns coffee into code.

Andrew Jansen
Open Dental Software
Software Engineer
andrew@opendental.com
503-363-5432

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: How about this report?

Post by Tom Zaccaria » Wed Dec 16, 2015 3:17 am

I wasn't looking for it but I like this query very much.
Would be even better if we could pull AbbrDesc from Procedurecode. This way you wouldn't have to remember what the ADA codes represent.
Nice find!

drtmz

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

Re: How about this report?

Post by tgriswold » Fri Dec 18, 2015 2:41 pm

Added ProcDesc column for you.

Code: Select all

/*1022 Procedure code totals by ProcCode and provider for completed procedures with completion date in date range.*/
/*Showing production, writeoffs, adjustments, and net production*/
/*Query code written/modified: 12/18/2015*/
SET @FromDate='2014-01-10', @ToDate='2014-01-10';
SELECT A.Provider,A.ProcCode,A.AbbrDesc AS ProcDesc,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pc.AbbrDesc,
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum	
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum	
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum 
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode
Travis Griswold
Open Dental Software
http://www.opendental.com

fishdrzig
Posts: 433
Joined: Tue Oct 07, 2008 12:46 pm

Re: How about this report?

Post by fishdrzig » Fri Dec 18, 2015 2:55 pm

Travis

Yes, this does look like something that I can use. Can you show me where I need to enter info (Date, provider etc....) to make it work for me?

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: How about this report?

Post by Tom Zaccaria » Fri Dec 18, 2015 4:33 pm

Great. Thank you,
drtmz

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

Re: How about this report?

Post by tgriswold » Mon Dec 21, 2015 12:08 pm

fishdrzig wrote:Travis

Yes, this does look like something that I can use. Can you show me where I need to enter info (Date, provider etc....) to make it work for me?
To adjust the date range, change the @FromDate='YYYY-MM-DD' and @ToDate='YYYY-MM-DD' at the top of the query on the fourth line. For example:

Code: Select all

SET @FromDate='2015-12-01', @ToDate='2015-12-31'; 
Currently this query does not have a provider filter.
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply