Code behind the saved reports

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Code behind the saved reports

Post by Tom Zaccaria » Tue Aug 06, 2013 5:41 pm

Can someone point me to the code behind the saved reports if possible?
I would like to modify the the Daily Payments report to group the insurance checks by carrier and also subtotal by carrier.

Thanks,
drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Code behind the saved reports

Post by jsalmon » Tue Aug 06, 2013 9:42 pm

OpenDental > Forms Reports > FormRpPaySheet.cs > butOK_Click.
Make sure you check the query examples page. Someone may have already asked for that and will save you time.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

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

Re: Code behind the saved reports

Post by Tom Zaccaria » Wed Aug 07, 2013 8:39 am

Did find this in the examples:

/*5*/ SELECT patient.ChartNumber,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS Name,
claimpayment.CheckDate,
carrier.CarrierName,claimpayment.CheckNum,
claimproc.ClaimNum,SUM(claimproc.InsPayAmt) as $Amt
FROM claimpayment,claimproc,insplan,patient,carrier
WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status = '1' OR claimproc.Status = '4')
AND claimpayment.CheckDate = curdate()
GROUP BY claimproc.ClaimNum

Modified it to be the current date.
Now just need to subtotal by CarrierName.
I think it has something to do with the sum() function.
Anyone have any ideas?
drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Code behind the saved reports

Post by jsalmon » Wed Aug 07, 2013 8:56 am

By the looks of it, it's already summing the insurance payments for you. You should group by carrier.CarrierName instead of claimproc.ClaimNum.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

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

Re: Code behind the saved reports

Post by Tom Zaccaria » Wed Aug 07, 2013 10:00 am

Yes I can do that but I wanted the subtotal of each carrier in the list
eg.

Patient 1...UCCI........100.00
Patient 2...UCCI........200.00
.........................300.00
Patient 3...MetLife....100.00
Patient 4...MetLife....200.00
Patient 5...MetLife....400.00
.........................700.00
Final total of all
insurance checks....1000.00

drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Code behind the saved reports

Post by jsalmon » Wed Aug 07, 2013 10:33 am

Without the subtotals you would group by patient and then by carrier. The subtotals are more complicated and would be a query request unless someone on here does it for you.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Post Reply