Creating Patient Balance Reports from EMedSys
By Boni J. Rychener/Programmer
THE PROBLEM:
When trying to create patient balance reports from the PracticeXperts EMedSys database with patient charges, payments, adjustments, and refunds you will encounter a problem with Crystal totaling the figures incorrectly. This is due to the fact that charges are associated with a charge ticket number whereas payments, adjustments, and refunds are associated with a date. Although the charge also has a date association, a patient can have duplicate charges and/or procedures on the same day. Thus, the levels at which the subtotals should be generated at are different for the two categories. In essence, it is necessary to create separate subreports that will generate the data for payment, adjustment, and refund information. This would entail three sub reports (one for payments, one for adjustments, and one for refunds) that will be linked to the main report. You will also need to create shared variables to pass from these sub reports into the main report and some manual running totals to summarize the shared variables. Here, I will explain how.
THE SOLUTION:
THE MAIN REPORT
Picking and Linking Tables
Starting with the Charge_History, Department, Patient, Physician, and Company_Procedure tables, create these links.
Charge_History.DepartmentID to Department.DepartmentID
Charge_History.PatientID to Patient.PatientID
Charge_History.CompanyID to Patient.CompanyID
Charge_History.PhysicianID to Physician.PhysID
Charge_History.ProcedureID to
Company_Procedure.ProcedureID
Creating Parameters
Next create a date range parameter that would allow the user to input a date range that would exclude data outside the range. Select Range Value(s) and click ok. You can also add parameters for any other fields you would like to exclude data on like Department or Physician.
Formatting the Date from DateTime
Because EMedSys tracks dates in datetime format you will need to create a new formula called Date to use with the Date parameter. This will allow for easier and more complete selection of appropriate data. Enter the following code:
Date({Charge_History.Post_FromDate})
Save and close.
Adding Groups
Group the report on Department first - use the Department.Department_Name field if you want to alphabetize the report or Department.Department_Number if you would like the report to sort departments numerically. Now you will need to add groupings on PatientID and HistoryID because one patient can have the same procedure more than once in a day and since HistoryID is the primary key of the Charge_History table this will allow for one record per procedure per patient.
Selecting Records
Using the Select Expert click on ‘Show Formula >>>” and then “Formula Editor” and enter the following code:
{@Date} in {?Start Date} to {End Date} and
Not ({CHARGE_HISTORY.STATUS} in [100, 99]) and
(ISNULL ({PATIENT.ACTIVE_DATE_END})) and
Not ({PATIENT.LAST_NAME} like '"ZZ*"') and
Not ({COMPANY_PROCEDURE.PROCEDURE_CODE} in [“00000”,
“999”, “99999”])
• The {@Date} is the formula field that we created to convert the datetime into a date which will exclude all records that do not match this date range.
• The Charge_History.Status screens out reversals and reversed transactions.
• The ISNULL(Patient.Active_Date_End) screens out inactive patient records.
• The Charge_History.Post_FromDates screens in only the appropriate date records using the date range parameters that you set up to input the appropriate values.
• The Patient.Last_Name screens out dummy patient data.
Adding Fields to the Report
Drag and drop (or however you would like to add) these fields to the Details section of your report - Post_FromDate, Procedure_Code, Charge_Amt from the Charge_History table. This will also add field headers for these fields, the text can be changed by double-clicking on the header. You will need to add four additional headers for “Payments”, “Adjustments”, “Refunds”, and “Balance”. To format the Patient name as “Last Name, First Name” insert another text box next to PatientID group, in this case group 2, and drag the following fields into it - Patient.Last_Name, Patient.First_Name. (Be sure to include the comma and the space between the two fields.)
Save as Main.rpt.
THE SUBREPORTS
Payments
Step 1:
Create a new blank report using the Credit_Audit table and add the Amount_Applied field from the Charge_History table. Select on Credit_Type = “P” in the Select Expert. Use the Summary icon to insert a sum in the Grand Total. Suppress all sections except the one that contains the summary amount. Next add a formula field titled “Shared Payment” and enter the following code:
WhilePrintingRecords;
Shared NumberVar Pay:=Sum of GT report Pay;
Pay
Be sure to write down what you called this NumberVar, in this case Pay, so that you can use it later! Now add a new report footer section below the Report Footer and insert the “Shared Payment“ formula. Format the font of the formula field to the color white. Do this by right-clicking the field and selecting “Format Field”. Then click on the “Font” tab and select the color.
Now you can suppress all sections of the report by right clicking the section and selecting “Suppress”.
Save this sub report as Payments.rpt
Step 2:
Insert a sub report into the main report using the existing report Payments.rpt. Click the links tab at the top and link on HistoryID.
Place this sub report into Group Header 3 and shrink it down as small as possible using the “Ctrl” and arrow keys. Format the sub report using the Format Editor “Border” tab to exclude the borders.
Highlight and drag all of the fields in the details section down into Group Footer 3 and then suppress the Details section. Using the Section Expert check the box next to “Underlay the Following Section” for Group Header 3.
Next create a new formula called “Payments” and enter the following:
WhilePrintingRecords;
Shared NumberVar Pay;
Pay
Save, close, and insert the formula into the main report next to the charges and beneath the Payment header.
Adjustments
Repeat steps 1 and 2 for an “Adjustments” sub report selecting for Credit_Type = “A” in the Select Expert. Add a formula field titled “Shared Adjustments” and enter the following code:
WhilePrintingRecords;
Shared NumberVar Adj:=Sum of GT report Adj;
Adj
Save this as Adjustments.rpt and create a new formula field called Adjustments in the main report and enter the following:
WhilePrintingRecords;
Shared NumberVar Adj;
Adj
Refunds
Duplicate steps 1 and 2 again to create a third sub report for “Refunds” selecting on Credit_Type =”R” and substituting Ref/Refund for Pay/Payment.
WhilePrintingRecords;
Shared NumberVar Ref:=Sum of GT report Ref;
Ref
Save as Refunds.rpt and add a new formula field titled Refunds and enter the code:
WhilePrintingRecords;
Shared NumberVar Ref;
Ref
Balance
Finally you will need to create a new formula called “Balance” in the MAIN report. Enter the following:
NumberVar Bal := {CHARGE_HISTORY.CHARGE_AMOUNT} –
({@Payments} + {@Adjustments} + {@Refunds});
Bal
Insert this field into your report underneath the Balance column on the same line as the charges, payments, adjustments, and refunds. This will give you the balance per HistoryID for each patient. Now we need to summarize the balance information.
THE SUMMARIES
Summarizing the Balances
To summarize the balances you will need three formulas for each group that needs summarization - one to initialize the variable, one to calculate the totals, and one to display the final value. In this case we will summarize on the patient, the department, and at the grand total levels so we will need nine new formulas.
Patient Balance Summary
Step 1:
Create a new formula called “Init_Patient” and enter the following formula:
WhilePrintingRecords;
NumberVar Pat: = 0;
Pat: = 0
Save and close.
Step 2:
Insert this formula in the report header and right-click the field. Click to format the field and open the “Common” tab. Now select “Suppress”.
Step 3:
The next step is to “Suppress Blank Section” in the Section Expert. Make sure that the “Report Header” is highlighted and check the box.
Step 4:
Next you will need to insert a new section below the group footer where your totals are placed. Insert “Init_Patient” in this section and repeat steps 1 and 2. Make sure that you are on the correct section in the section expert, in this case group footer 2b. While the section expert is still open, click on group footer 2a and select “Underlay following section”. This is done because the variable will be reset at each page if you have “Repeat Group Name” selected in the Group Editor.
Step 5:
Next create a new formula called “Calc_Patient” and enter the following formula:
WhilePrintingRecords;
NumberVar Pat;
Pat := Pat + {@Balance};
Insert this formula into the details section of your report.
Step 6:
Create your third formula called “Display_Patient” and enter the following:
WhilePrintingRecords;
NumberVar Pat;
Pat := Pat
Again, insert this formula into Group Footer 2a and align it with the “Balance” column header.
Department Balance Summary
Repeat steps 1 through 6 substituting “Dept” for “Pat” and “Department” for “Patient” to have summarizations at the Department level as well as the Patient level but in step 4, insert a new section beneath Group Footer 1 (instead of GF2) and insert the “Init_Department” formula there as well as inserting it into the report header.
Grand Total Balance Summary
Repeat steps 1 through 6 substituting “Tot” for “Pat” and “Total” for “Patient”
You will only need to insert the “Init_Total” into the report once, in the report header, in step 4. This is because the “Tot” variable does not need to be reset since it is a report total. Now you can view your new Patient Balance Report!
Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
800-783-2269
By Boni J. Rychener/Programmer
THE PROBLEM:
When trying to create patient balance reports from the PracticeXperts EMedSys database with patient charges, payments, adjustments, and refunds you will encounter a problem with Crystal totaling the figures incorrectly. This is due to the fact that charges are associated with a charge ticket number whereas payments, adjustments, and refunds are associated with a date. Although the charge also has a date association, a patient can have duplicate charges and/or procedures on the same day. Thus, the levels at which the subtotals should be generated at are different for the two categories. In essence, it is necessary to create separate subreports that will generate the data for payment, adjustment, and refund information. This would entail three sub reports (one for payments, one for adjustments, and one for refunds) that will be linked to the main report. You will also need to create shared variables to pass from these sub reports into the main report and some manual running totals to summarize the shared variables. Here, I will explain how.
THE SOLUTION:
THE MAIN REPORT
Picking and Linking Tables
Starting with the Charge_History, Department, Patient, Physician, and Company_Procedure tables, create these links.
Charge_History.DepartmentID to Department.DepartmentID
Charge_History.PatientID to Patient.PatientID
Charge_History.CompanyID to Patient.CompanyID
Charge_History.PhysicianID to Physician.PhysID
Charge_History.ProcedureID to
Company_Procedure.ProcedureID
Creating Parameters
Next create a date range parameter that would allow the user to input a date range that would exclude data outside the range. Select Range Value(s) and click ok. You can also add parameters for any other fields you would like to exclude data on like Department or Physician.
Formatting the Date from DateTime
Because EMedSys tracks dates in datetime format you will need to create a new formula called Date to use with the Date parameter. This will allow for easier and more complete selection of appropriate data. Enter the following code:
Date({Charge_History.Post_FromDate})
Save and close.
Adding Groups
Group the report on Department first - use the Department.Department_Name field if you want to alphabetize the report or Department.Department_Number if you would like the report to sort departments numerically. Now you will need to add groupings on PatientID and HistoryID because one patient can have the same procedure more than once in a day and since HistoryID is the primary key of the Charge_History table this will allow for one record per procedure per patient.
Selecting Records
Using the Select Expert click on ‘Show Formula >>>” and then “Formula Editor” and enter the following code:
{@Date} in {?Start Date} to {End Date} and
Not ({CHARGE_HISTORY.STATUS} in [100, 99]) and
(ISNULL ({PATIENT.ACTIVE_DATE_END})) and
Not ({PATIENT.LAST_NAME} like '"ZZ*"') and
Not ({COMPANY_PROCEDURE.PROCEDURE_CODE} in [“00000”,
“999”, “99999”])
• The {@Date} is the formula field that we created to convert the datetime into a date which will exclude all records that do not match this date range.
• The Charge_History.Status screens out reversals and reversed transactions.
• The ISNULL(Patient.Active_Date_End) screens out inactive patient records.
• The Charge_History.Post_FromDates screens in only the appropriate date records using the date range parameters that you set up to input the appropriate values.
• The Patient.Last_Name screens out dummy patient data.
Adding Fields to the Report
Drag and drop (or however you would like to add) these fields to the Details section of your report - Post_FromDate, Procedure_Code, Charge_Amt from the Charge_History table. This will also add field headers for these fields, the text can be changed by double-clicking on the header. You will need to add four additional headers for “Payments”, “Adjustments”, “Refunds”, and “Balance”. To format the Patient name as “Last Name, First Name” insert another text box next to PatientID group, in this case group 2, and drag the following fields into it - Patient.Last_Name, Patient.First_Name. (Be sure to include the comma and the space between the two fields.)
Save as Main.rpt.
THE SUBREPORTS
Payments
Step 1:
Create a new blank report using the Credit_Audit table and add the Amount_Applied field from the Charge_History table. Select on Credit_Type = “P” in the Select Expert. Use the Summary icon to insert a sum in the Grand Total. Suppress all sections except the one that contains the summary amount. Next add a formula field titled “Shared Payment” and enter the following code:
WhilePrintingRecords;
Shared NumberVar Pay:=Sum of GT report Pay;
Pay
Be sure to write down what you called this NumberVar, in this case Pay, so that you can use it later! Now add a new report footer section below the Report Footer and insert the “Shared Payment“ formula. Format the font of the formula field to the color white. Do this by right-clicking the field and selecting “Format Field”. Then click on the “Font” tab and select the color.
Now you can suppress all sections of the report by right clicking the section and selecting “Suppress”.
Save this sub report as Payments.rpt
Step 2:
Insert a sub report into the main report using the existing report Payments.rpt. Click the links tab at the top and link on HistoryID.
Place this sub report into Group Header 3 and shrink it down as small as possible using the “Ctrl” and arrow keys. Format the sub report using the Format Editor “Border” tab to exclude the borders.
Highlight and drag all of the fields in the details section down into Group Footer 3 and then suppress the Details section. Using the Section Expert check the box next to “Underlay the Following Section” for Group Header 3.
Next create a new formula called “Payments” and enter the following:
WhilePrintingRecords;
Shared NumberVar Pay;
Pay
Save, close, and insert the formula into the main report next to the charges and beneath the Payment header.
Adjustments
Repeat steps 1 and 2 for an “Adjustments” sub report selecting for Credit_Type = “A” in the Select Expert. Add a formula field titled “Shared Adjustments” and enter the following code:
WhilePrintingRecords;
Shared NumberVar Adj:=Sum of GT report Adj;
Adj
Save this as Adjustments.rpt and create a new formula field called Adjustments in the main report and enter the following:
WhilePrintingRecords;
Shared NumberVar Adj;
Adj
Refunds
Duplicate steps 1 and 2 again to create a third sub report for “Refunds” selecting on Credit_Type =”R” and substituting Ref/Refund for Pay/Payment.
WhilePrintingRecords;
Shared NumberVar Ref:=Sum of GT report Ref;
Ref
Save as Refunds.rpt and add a new formula field titled Refunds and enter the code:
WhilePrintingRecords;
Shared NumberVar Ref;
Ref
Balance
Finally you will need to create a new formula called “Balance” in the MAIN report. Enter the following:
NumberVar Bal := {CHARGE_HISTORY.CHARGE_AMOUNT} –
({@Payments} + {@Adjustments} + {@Refunds});
Bal
Insert this field into your report underneath the Balance column on the same line as the charges, payments, adjustments, and refunds. This will give you the balance per HistoryID for each patient. Now we need to summarize the balance information.
THE SUMMARIES
Summarizing the Balances
To summarize the balances you will need three formulas for each group that needs summarization - one to initialize the variable, one to calculate the totals, and one to display the final value. In this case we will summarize on the patient, the department, and at the grand total levels so we will need nine new formulas.
Patient Balance Summary
Step 1:
Create a new formula called “Init_Patient” and enter the following formula:
WhilePrintingRecords;
NumberVar Pat: = 0;
Pat: = 0
Save and close.
Step 2:
Insert this formula in the report header and right-click the field. Click to format the field and open the “Common” tab. Now select “Suppress”.
Step 3:
The next step is to “Suppress Blank Section” in the Section Expert. Make sure that the “Report Header” is highlighted and check the box.
Step 4:
Next you will need to insert a new section below the group footer where your totals are placed. Insert “Init_Patient” in this section and repeat steps 1 and 2. Make sure that you are on the correct section in the section expert, in this case group footer 2b. While the section expert is still open, click on group footer 2a and select “Underlay following section”. This is done because the variable will be reset at each page if you have “Repeat Group Name” selected in the Group Editor.
Step 5:
Next create a new formula called “Calc_Patient” and enter the following formula:
WhilePrintingRecords;
NumberVar Pat;
Pat := Pat + {@Balance};
Insert this formula into the details section of your report.
Step 6:
Create your third formula called “Display_Patient” and enter the following:
WhilePrintingRecords;
NumberVar Pat;
Pat := Pat
Again, insert this formula into Group Footer 2a and align it with the “Balance” column header.
Department Balance Summary
Repeat steps 1 through 6 substituting “Dept” for “Pat” and “Department” for “Patient” to have summarizations at the Department level as well as the Patient level but in step 4, insert a new section beneath Group Footer 1 (instead of GF2) and insert the “Init_Department” formula there as well as inserting it into the report header.
Grand Total Balance Summary
Repeat steps 1 through 6 substituting “Tot” for “Pat” and “Total” for “Patient”
You will only need to insert the “Init_Total” into the report once, in the report header, in step 4. This is because the “Tot” variable does not need to be reset since it is a report total. Now you can view your new Patient Balance Report!
Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
800-783-2269