Transaction Detail to Excel Reports

With the Transaction Detail to Excel Reports function, you can create customized reports to pull extensive transaction detail from each patient account. The reports can be printed to Microsoft Excel via MyReports, where you can sort or calculate the data to meet your needs. After you create a report, you can save the report definition for future use, so you do not have to re-define it each time you want to print it. This function is also available on the Reports, Transaction Journals menu.


New Report

Upon accessing the function, any previously saved report definitions are listed. You can select an existing report to Print or Copy it by clicking anywhere in the row, or you can click the New Report Action Column button to create a new report.



If you click New Report, a blank report definition screen displays. You can name the report, provide a description of the report, and set up two customized Headings to print on the report. Using the Excel Column fields, you can select from the drop-down lists to include data elements from posted procedures and the patient's account. The available data elements display in alphabetic order, and all the procedure-based data elements start with Proc-, for example Proc-Chg Acct Date.

The report supports three sets of Excel Column groupings: AA–AZ, BA–BZ, and the original column range, giving you room to include many data elements on a single report. Individual field labels match the Excel column letter (AA, AB, AC, etc.) so you can map the report layout directly to the spreadsheet output.

If you select either of the Encounter DX Codes or Encounter DX Descriptions data elements, 12 additional columns are added to the report for each (for example, Encounter DX Code 1 through Encounter DX Code 12).

If you select the Patient Social Security # data element, you can control whether the full number prints or is asterisk-filled using the Report Integration function (System, Database Maintenance Menu, Integrations, Report Integration).



Print Report

To Print a report, select an existing report from the list.



When the report definition screen displays, click Print Report in the Action Column.



Sorting and filter options are provided to assist in narrowing down the results. Complete the applicable fields and click Save. Use the Clear Fields Action Column button if you want to reset all entered filter values and start over.



Data Field Information
Prompt Response Req Len
From Patient Type the patient account number you want to begin with, or leave the field blank to start with the first account number in the system.   10
Through Patient Type the patient account number you want to end with, or leave the field blank to end with the last account number in the system.   10
From Performing Dr Type the code you want to begin with, click the magnifying glass to search the table, or leave the field blank to start with the first code in the table.   4
Through Performing Dr Type the code you want to end with, click the magnifying glass to search the table, or leave the field blank to end with the last code in the table.   4
From Location Type the code you want to begin with, click the magnifying glass to search the table, or leave the field blank to start with the first code in the table.   10
Through Location Type the code you want to end with, click the magnifying glass to search the table, or leave the field blank to end with the last code in the table.   10
From Procedure Code Type the procedure code you want to begin with, or leave the field blank to start with the first code in the table. The field allows up to five characters and does not provide the magnifying glass table search feature, in order to allow you to include procedure codes with modifiers. For example, if you type 99999 in From Procedure Code and 99999 in Through Procedure Code, the report contains all procedure codes that start with 99999, including codes that have modifiers (99999, 9999950, 99999LT, etc.).   5
Through Procedure Code Type the procedure code you want to end with, or leave the field blank to end with the last code in the table.   5
From Date Type the date you want the report to begin with or click the calendar icon to select a date.   10
Through Date Type the date you want the report to end with or click the calendar icon to select a date.   10
From Diagnosis Code Type the diagnosis code you want to begin with, click the magnifying glass to search the table, or leave the field blank to start with the first diagnosis code in the table.    
Through Diagnosis Code Type the diagnosis code you want to end with, click the magnifying glass to search the table, or leave the field blank to end with the last diagnosis code in the table.    
Billing Group Type the billing group code you want or click the magnifying glass to search the table to limit the report to a specific billing group.    
Print By Select Accounting Date to gather data based on the accounting date of each transaction, or select Service Date to gather data based on the date of service. 1
Include Negated Charges If you want the report to include negated charges, select this check box.   1

You can print this report to Excel by selecting the Microsoft Excel via MyReports option in the Printers dialog box. If you select the Notify me when report is complete check box, you will receive a pop-up message when the report is complete.
 

 
You can select to view the report using the link in the pop-up or you can elect to retrieve the report from MyReports at a later time. For additional information about the available printing options, see Printing in CGM webPRACTICE.


Copy Report

When the report definition screen displays, click Copy Report in the Action Column. This allows you to easily create a new report by copying an existing report definition and editing it.



Enter a new Report Name and add or edit the remaining fields as needed. Click Save when all edits are complete.



Enhanced Options

An enhanced version of this report is available that adds payment (Pmt) and adjustment (Adj) data elements, so you can extract payment and adjustment detail along with the procedure and account data. To use the enhanced version, contact your Account Manager to have it activated.

Once activated, the additional payment (Pmt) and adjustment (Adj) data elements are available in the Excel Column drop-down lists when you define a report.





A few items to note:


All of the payment (Pmt) and adjustment (Adj) data elements provide data as long as the payment or adjustment date falls within the date range you select, with the exception of Pmt-Primary Carrier, Pmt-Secondary Carrier, Pmt-Tertiary Carrier, and Pmt-Patient. These data elements require that you also include Procedure (Proc) data elements, because their payment amounts print only in the row containing the procedure information they were applied toward, and they provide data only when the Procedure Date falls within the date range you select.



Any data element whose code starts with B, D, G, I, or P prints its data in every payment and adjustment row, so you can do additional sorting and filtering in Excel if needed: B = Billing elements, D = Database number, G = Guarantor elements, I = Insurance elements, and P = Patient elements.



Data Elements List

The following data elements can be selected in the Excel Column drop-down lists when you define a report. The Code column shows the data element's internal code.

Data Element Code
Adj-Acct DateJ9.0
Adj-Actual Date PostedJ9.0e
Adj-Amount $J9.3
Adj-Amt Posted to Adjusted TransactionJ9.12
Adj-CodeJ9.2
Adj-Code DescJ9.2a
Adj-DOS of Adjusted TransactionJ9.13
Adj-Proc Code of Adjusted TransactionJ9.11
Adj-Timestamp PostedJ9.0t
Adj-User CodeJ9.0u
Billing Account DescriptionB1.4
Billing Alert UserB1.2
Billing Benefits AssignmentB1.5
Billing Employer's Address Line 1B1.13
Billing Employer's Address Line 2B1.27
Billing Employer's CityB1.23
Billing Employer's StateB1.24
Billing Employer's Telephone NumberB1.15
Billing Employer's Zip CodeB1.22
Billing Employment StatusB1.19
Billing Erase Statement CommentB1.18
Billing Fee Schedule (Alternate Fee)B1.11
Billing Finance ChargesB1.16
Billing Group CodeB1.1
Billing Group DescriptionB1.1d
Billing Guarantor's EmployerB1.12
Billing Internal CommentB1.8
Billing Medicare PatientB1.10
Billing Print Aging MessageB1.17
Billing Release of InformationB1.28
Billing Report CommentB1.7
Billing Send StatementB1.3
Billing Statement CommentB1.6
Billing Suppress CollectionsB1.21
Billing Use Primary AddressB1.9
Database NumberD0
Encounter DX CodesE1.1
Encounter DX DescriptionsE1.2
Guarantor Address Line 1G1.4
Guarantor Address Line 2G1.5
Guarantor Birth DateG1.18
Guarantor CityG1.6
Guarantor Country CodeG1.19
Guarantor CountyG1.20
Guarantor E-Mail AddressG1.17
Guarantor First NameG1.2
Guarantor Last NameG1.1
Guarantor Middle InitialG1.3
Guarantor Middle NameG1.26
Guarantor Phone CellG1.24
Guarantor Phone HomeG1.9
Guarantor Phone SecondaryG1.16
Guarantor Secondary Address Line 1G1.11
Guarantor Secondary Address Line 2G1.12
Guarantor Secondary CityG1.13
Guarantor Secondary Country CodeG1.21
Guarantor Secondary CountyG1.22
Guarantor Secondary StateG1.14
Guarantor Secondary SubdivisionG1.29
Guarantor Secondary Zip CodeG1.15
Guarantor Social Security #G1.10
Guarantor State CodeG1.7
Guarantor SubdivisionG1.30
Guarantor SuffixG1.27
Guarantor Zip CodeG1.8
Patient Account NumberP1.0
Patient Address Line 1P1.4
Patient Address Line 2P1.5
Patient AgeP1.13a
Patient BalanceP8.4
Patient Birth DateP1.13
Patient Cell PhoneP1.24
Patient CityP1.6
Patient Class CodesP1.22
Patient Class DescriptionP1.22d
Patient Country CodeP1.30
Patient County CodeP1.31
Patient Date Account Last ReviewedP1.44
Patient Date of DeathP1.37
Patient Date of First VisitP1.17
Patient Default DXP1.11
Patient Default Location CodeP1.18
Patient Default Location DescriptionP1.18d
Patient EmailP1.23
Patient Ethnicity CodeP1.33
Patient Ethnicity Code - DescriptionP1.33d
Patient First NameP1.2
Patient Firstname LastnameP1.1b
Patient Gender IdentityP1.42
Patient Gender Identity DescriptionP1.42d
Patient Gender Identity OtherP1.43
Patient Home PhoneP1.9
Patient IdentifierP1.29
Patient LanguageP1.27
Patient Last NameP1.1
Patient Last Payment AmountP8.2
Patient Last Payment DateP8.1
Patient Last Statement DateP8.3
Patient Lastname, FirstnameP1.1a
Patient Middle InitialP1.3
Patient Primary DoctorP1.19
Patient Primary Doctor DescriptionP1.19d
Patient RaceP1.28
Patient Race DescriptionP1.28d
Patient Race OtherP1.34
Patient Race Other DescriptionP1.34d
Patient Referral CodeP1.15
Patient Referral First NameP1.15c
Patient Referral Last NameP1.15b
Patient Referral Middle InitialP1.15d
Patient Referral Printing NameP1.15a
Patient Referral SuffixP1.15e
Patient Referral TypeP1.32
Patient Rel to GuarantorP1.14
Patient Rel to Guarantor DescriptionP1.14d
Patient Responsible DoctorP1.16
Patient Responsible Doctor DescriptionP1.16d
Patient SexP1.12
Patient Sex DescriptionP1.12d
Patient Sex OrientationP1.40
Patient Sex Orientation DescriptionP1.40d
Patient Sex Orientation OtherP1.41
Patient Social Security #P1.10
Patient StateP1.7
Patient StatusP1.20
Patient Status DescriptionP1.20d
Patient SubdivisionP1.38
Patient SuffixP1.36
Patient ZipP1.8
Pmt-Acct DateC9.0
Pmt-Actual Date PostedC9.0e
Pmt-Amount $C9.3
Pmt-Amt Posted to Paid Transaction $C9.12
Pmt-CodeC9.2
Pmt-Code DescC9.2a
Pmt-DOS of Paid TransactionC9.14
Pmt-ICNC9.0i
Pmt-Ins Carrier CodeC9.15
Pmt-Ins Carrier DescC9.15a
Pmt-PatientC9.15e
Pmt-Posted Allowed Amt $C9.13
Pmt-Primary CarrierC9.15b
Pmt-Proc Code of Paid TransactionC9.11
Pmt-Secondary CarrierC9.15c
Pmt-Tertiary CarrierC9.15d
Pmt-Timestamp PostedC9.0t
Pmt-User CodeC9.0u
Primary Carrier Allowed AmountT9.76
Primary Carrier Auth RequiredI1.01
Primary Carrier CodeI1.1
Primary Carrier CopayI1.16
Primary Carrier Group NumberI1.5
Primary Carrier Last Verified ByI1.34
Primary Carrier Last Verified DateI1.33
Primary Carrier NameI1.0
Primary Carrier Plan CodeI1.14
Primary Carrier Plan Code DescI1.14a
Primary Carrier Policy NumberI1.6
Primary Carrier Spec CopayI1.24
Primary Carrier Verified By via CGM webVERIFYI1.29
Primary Carrier Verified Date via CGM webVERIFYI1.28
Proc-Accept AssignmentT9.21
Proc-Actual Date PostedT9.0e
Proc-All ModifiersT9.20
Proc-Batch #T9.32
Proc-Billing Group CodeT9.35
Proc-Billing Group DescT9.35a
Proc-Case #T9.28
Proc-Charge Amount $T9.3
Proc-Chg Acct DateT9.0
Proc-CPT CodeT9.2
Proc-CPT DescT9.2a
Proc-CPT Total Units (Units x QTY)T9.2c
Proc-CPT Units per CPTT9.2b
Proc-Date of Ill/Inj/LmpT9.28a
Proc-Date of ServiceT9.10
Proc-Delinquent FlagT9.18
Proc-Department CodeT9.36
Proc-Department DescT9.36a
Proc-DX 1T9.72
Proc-DX 1 DescriptionT9.72a
Proc-DX 2T9.73
Proc-DX 2 DescriptionT9.73a
Proc-DX 3T9.74
Proc-DX 3 DescriptionT9.74a
Proc-DX 4T9.75
Proc-DX 4 DescT9.75a
Proc-Encounter #T9.64
Proc-EncounterProcIDT9.64a
Proc-Fee Schedule Allowed Amt $T9.24
Proc-Ins BalanceT9.26
Proc-Ins Doctor CodeT9.31
Proc-Ins Doctor NameT9.31a
Proc-Ins Last Refiled/Denied DateT9.15
Proc-Ins Last Refiled/Denied ReasonT9.16
Proc-Ins Responsible Carrier CodeT9.14a
Proc-Ins Responsible Carrier DescriptionT9.14ab
Proc-Ins Responsible Carrier Last File AmountT9.14e
Proc-Ins Responsible Carrier Last Filing DateT9.14c
Proc-Ins Responsible Carrier Original Filing DateT9.14d
Proc-Ins Responsible Carrier TypeT9.14b
Proc-Ins StatusT9.14
Proc-Last Pmt ICNT9.99j
Proc-Last Pmt ICN Amount $T9.99k
Proc-Location CodeT9.11
Proc-Location DescT9.11a
Proc-Master AccountT9.0m
Proc-MED Allowable Amount $T9.99a
Proc-Modifier 1T9.20a
Proc-Modifier 2T9.20b
Proc-Modifier 3T9.20c
Proc-Modifier 4T9.20d
Proc-Patient Acct NumberT9.12
Proc-Patient NameT9.22
Proc-Performing Dr CodeT9.5
Proc-Performing Dr NameT9.5a
Proc-Performing Dr NPIT9.5b
Proc-Place of Service Code BCBST9.11e
Proc-Place of Service Code DMERCT9.11d
Proc-Place of Service Code HCFAT9.11b
Proc-Place of Service Code MEDICAIDT9.11f
Proc-Place of Service Code MEDICARET9.11c
Proc-Pmt Acct DateT9.0d
Proc-Pt BalanceT9.25
Proc-QuantityT9.17
Proc-RBRVS Fac MalpracticeT9.99f
Proc-RBRVS Fac PracticeT9.99g
Proc-RBRVS Fac TotalT9.99h
Proc-RBRVS Fac WorkT9.99i
Proc-RBRVS Non Fac MalpracticeT9.99b
Proc-RBRVS Non Fac PracticeT9.99c
Proc-RBRVS Non Fac TotalT9.99d
Proc-RBRVS Non Fac WorkT9.99e
Proc-Referring Dr CodeT9.23
Proc-Referring Dr NameT9.23a
Proc-RemarksT9.4
Proc-SuperbillT9.19
Proc-Timestamp PostedT9.0t
Proc-Total Adjustments $T9.0a
Proc-Total Bal $T9.13
Proc-Total Payments $T9.0p
Proc-Type of ServiceT9.2d
Proc-Type of Service DescT9.2da
Proc-User CodeT9.45
Secondary Carrier Allowed AmountT9.77
Secondary Carrier Auth RequiredI2.01
Secondary Carrier CodeI2.1
Secondary Carrier CopayI2.16
Secondary Carrier Group NumberI2.5
Secondary Carrier Last Verified ByI2.34
Secondary Carrier Last Verified DateI2.33
Secondary Carrier NameI2.0
Secondary Carrier Plan CodeI2.14
Secondary Carrier Plan Code DescI2.14a
Secondary Carrier Policy NumberI2.6
Secondary Carrier Spec CopayI2.24
Secondary Carrier Verified By via CGM webVERIFYI2.29
Secondary Carrier Verified Date via CGM webVERIFYI2.28
Tertiary Carrier Auth RequiredI3.01
Tertiary Carrier CodeI3.1
Tertiary Carrier CopayI3.16
Tertiary Carrier Group NumberI3.5
Tertiary Carrier Last Verified ByI3.34
Tertiary Carrier Last Verified DateI3.33
Tertiary Carrier NameI3.0
Tertiary Carrier Plan CodeI3.14
Tertiary Carrier Plan Code DescI3.14a
Tertiary Carrier Policy NumberI3.6
Tertiary Carrier Spec CopayI3.24
Tertiary Carrier Verified By via CGM webVERIFYI3.29
Tertiary Carrier Verified Date via CGM webVERIFYI3.28