Open Dental Forum

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

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

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

By the looks of it, it's already summing the insurance payments for you. You should group by carrier.CarrierName instead of claimproc.ClaimNum.

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

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.