Transcript Designing Financial Reports to Meet Your Needs Introducing
2015 User Conference Designing Financial Reports to Meet Your Needs Introducing Advanced Reporting Tools Utilizing Excel April 23/24/25 2015 Presented by: Fred Pytlak & Chris Becker
General Workshop
Exporting Financial Data Overview
▪ ▪ ▪ ▪ ▪ Requires OP version 14.0.4x Export OP data from Cloud or Client Server Explain New OP Data Sets Using the OP dbExporter Tool How to import data files into a Microsoft Excel workbook 2015 Office Practicum User Conference
Exporting Methods
There are two end-user methods for exporting Data ▪ Exporting from an OP Grid reports ▪ Exporting using the OP dbExporter T ool 2015 Office Practicum User Conference
Exporting Methods
OP data is exported into a file using the Comma Separated Values (CSV) format.
Example of a CSV file: first_name, last_name, birthdate, address_line1, address_line2, city, state, zip,, 2015 Office Practicum User Conference
What is a Dataset?
A dataset is a collection of columns and rows and the data contained therein, from one or more tables in OP. Tables are linked using common columns in each table. The datasets are constructed to lookup codes used in the main patient financial records table with definitions stored in other tables.
The four datasets discussed in this presentation have been designed to link tables in various ways in order to aggregate data for different purposes.
2015 Office Practicum User Conference
Why Custom Datasets?
Denormalized Data.
The data sets include code lookups.
Instead of seeing the only insurance code ‘B23’, the dataset shows ‘B23 Blue Cross Independence’
The datasets are constructed to make it easy to create and understand Excel reports.
2015 Office Practicum User Conference
OP Data Sets
▪ Billing_Transactions Shows all transactions. Similar to the Billing Analysis grid. Biggest difference is this dataset includes all fields in the table.
+
Shows the number (count) of CPT codes used
+
Calculates sum of charges, sum of payments, etc
+
Use for productivity reports that show counts and sums
x
Cannot determine what CPT was being paid or adjusted
x
Does not calculate reimbursement for CPT codes 2015 Office Practicum User Conference
OP Data Sets
▪ Billing_Chrgs_with_Paymnts Shows all charge transactions for dates specified with a summation of all insurance payments, insurance adjustments, patient payments, patient adjustment, etc. for each charge Payments, adjustments, etc not limited to a date range. Only shows fully paid charges. (unlike in OP Reimbursement Analysis form where there is a choice to show charges in various stages of being paid) Downloads of recent activity can go stale.
2015 Office Practicum User Conference
OP Data Sets
▪ Billing_Chrgs_with_Paymnts
+
Use to show reimbursement details of CPT codes that were fully adjudicated. For example, average reimbursement by insurance carrier or provider.
x x
Not for determining counts of all CPT codes Does not calculate sum of all payments for a specified date range
x
Not for productivity reports 2015 Office Practicum User Conference
OP Data Sets
▪ Billing_Paymnts_with_Chrgs Shows all payments for dates specified and shows the charge (CPT) that was paid. Can filter by charge date.
A similar report does not exist in OP.
+
Use to calculate sum of all payments for specified CPT codes
+
Use to calculate sum of all payments with carve out of CPT codes
+
Use for provider productivity reports.
x
Cannot use to determine the count of CPT codes. A CPT code charge is represented for each payment, and therefore a count would overstate exact number of occurrences. 2015 Office Practicum User Conference
OP Data Sets
▪ Billing_Receipts_for_Dep Revenue Collected. Same as the Receipts for Deposit tab in Billing Analysis. Combines all new money entered in patient credits or as direct payment. Matches the Daysheet report.
+
Dataset is exactly the same data as on the Billing Analysis form.
+
Use to show revenue collected per daysheet ID, daysheet date, per location
x
Calculation of revenue collected per provider is unreliable.
Credits entered into the system may be attributed to one provider but then attributed to a different provider when applied as a payment. 2015 Office Practicum User Conference
Let’s Get Started.
▪ Must be on OP version 14.0.40 or higher ▪ Configure Permission to access the dbExporter tool.
▪ Determine what will be your starting and ending Daysheet_ID 2015 Office Practicum User Conference
Configure Permission
In Security Administration, grant the permission ‘Tools_DBExporter’ to individuals or a group.
2015 Office Practicum User Conference
Open the Database Exporter
From the Tools menu item, click on ‘Database Exporter’ 2015 Office Practicum User Conference
OP Database Exporter
2015 Office Practicum User Conference
Understanding Parameters ( filters)
Incremental downloading using Daysheet ID Voids included in all downloads. Every transaction is downloaded.
In Excel, include voids for financial reporting, exclude voids for counting occurrences.
2015 Office Practicum User Conference
Using Report Filters in Excel
Always include Archive_Flag as a Report Filter.
2015 Office Practicum User Conference
Exporting Financial Data Conclusions
Exporting OP data to Excel gives expanded reporting capabilities.
Unlimited customized reports to suit the needs of your practice.
Share workbooks with other OP users.
Share your ideas with OP to further expand the use of Excel.
2015 Office Practicum User Conference
We want your feedback!
Handouts: OP DB Exporter Dataset Info 2015 Office Practicum User Conference