Report Builder has two different ways to gather, classify, and analyze your revenue stream.
Report Builder has many datasets to choose from. For the purpose of reporting on your revenues, two datasets in particular are useful.
Dispatch-Centric Revenue Reporting
In the category of "Dispatches", there are three datasets used for revenue analysis:
- Dispatches-Overview: for trip data like service provided, priority, pickup location, and tags
- Dispatches-Financial: for all dollar amounts
- Dispatches-MedicareRates: for comparing your receipts against Medicare's rates
- Dispatches-Workflow: for dates, times, and postprocess delays
(Of course you can also pull in other datasets if you need additional details or filters.)
These datasets allow you to subdivide and aggregate monetary amounts according to the characteristics of the dispatch record.
Amounts received from insurance are displayed according to the insurance policies on-file for the patient on the date of service. That means you can analyze your revenue according to which insurance carrier was primary, or which facility was obligated.
To see some examples of dispatch-centric revenue reports, check out these walkthroughs:
What you cannot do in a dispatch-centric report is analyze all revenues from a particular carrier regardless of primary/secondary/tertiary. This is because the dispatch-centric datasets report display the revenues from primary, from secondary, and from tertiary as separate columns. Instead, you must use payor-centric revenue reporting...
Payor-Centric Revenue Reporting
The category "Payment Events" provides the datasets needed for a fine-grained analysis of insurance activity:
- PaymentEvents-Nonzero pulls all balance-affecting payment event records.
- PaymentEvents-Zero pulls all other payment event records -- stuff like claims, appeals, and preapprovals -- that do not represent a monetary amount.
Because these datasets pull from your payment event records, rather than from your trip records, they make it easier to see all activity for each counterparty type, or for each carrier type, or for each payor ID, regardless of primary, secondary, or tertiary... an analysis that would be difficult to perform using the dispatch-centric datasets.
To see some examples of payor-centric revenue reports, check out these walkthroughs:
Payor-Centric Revenue by Counterparty
Pulling the associated EOBs
Another neat thing that the payor-centric datasets can do, is let you pull any associated EOB document. Simply find the "EOB Document ID" column in the grid, right-click it to open the popup menu, and select "Go to This EOB".
Limitation of insurance payor IDs
Unfortunately, about half of all X12.835 EOBs fail to specify the carrier's payor ID, so when AngelTrack stores an EOB as a payment event, it must often leave the payor ID field blank.
When Report Builder later pulls that data, if a payment event record has a blank payor ID, then AngelTrack will auto-substitute the payor ID locked in the patient's file, on the assumption that the patient's file is correct.
This could be inaccurate, if the locked payor ID is wrong, but they are usually correct and so they are preferable to blank rows.
This could also bifurcate your data, in the case of a carrier who adjudicates under a different payor ID than it uses for receiving claims. For example, if you file your claims against 04412, but its EOBs come back from 65532, then all the payment events created from EDIs will be reported as 65532 (from the 835s), but any payment events created from paper EOBs will be reported under 04412 (from the patient record).
To aid with grouping your rows, if there is no payor ID locked in the patient's file, then Report Builder will display the value "?????". If the payment event is not an insurance event, i.e. the counterparty is instead a facility or affiliate or patient, then Report Builder will display the value "-----".
Limitation of insurance policy types
X12.835 EOBs always return a policy type indicator (such as MB, MC, CI), but a paper EOB does not. When returning payment events created from paper EOBs, Report Builder will therefore auto-substitute the policy type from the patient's file.
These values will often be incorrect, so keep this in mind when performing any sort of analysis using the policy-type column.
To aid with grouping your rows, if the policy type in the patient's file is "[Not recorded]", then Report Builder will display the value "??". If the payment event is not an insurance event, then Report Builder will display the value "--".
Limitation of insurance provider names
When a payment event dataset reports the "Counterparty Name" for a monetary amount received from insurance, for consistency it always returns the carrier name listed in the patient record... even if the particular payment event record says it came from a different payor ID, as often happens with carriers to receive claims at one payor ID but adjudicate under a different payor ID.
While the carrier name in the patient's file could be wrong, AngelTrack does this to simplify the task of gathering together each carrier's financial activity.
AVG() Function Ignores NULLs
When using the AVG() function to tally up charges or payments, be aware that it ignores rows where the target cell contain no value. Therefore it may produce unexpected results in situations like this:
- You use COUNT(Dispatch ID) to count up the call volume;
- You use something like AVG(Price Quote) to see a per-call average;
- Your dataset contains some rows where the averaged column (Price Quote in this example) contains some blanks
In that case, the AVG(Price Quote) result will only show the average of all columns where Price Quote is not blank, and therefore, the math will not match if you compare it to an average that you calculated using the count of all rows including blanks.
AVG() of a Percentage Column
Report Builder contains a few columns that show percentages; for example, the column "PR Pct of Price Allowed" contains the percentage of the Patient Responsibility versus the total Pricde Allowed by the carrier.
If you use the AVG() aggregation function to take an average of a percentage column, the result will show the average percentage; it will NOT show a recomputed average of the underlying data that was used to calculate the percentage. The result might be misleading.
For example, suppose you have this data in a row-group:
- Trip 1, Price Allowed $100, PR $50, PR Pct of Price Allowed = 50%
- Trip 2, Price Allowed $200, PR $60, PR Pct of Price Allowed = 30%
The aggregated raw average for all rows in the row-group is:
- Price Allowed total $300, PR total $110, PR Pct of Price Allowed = 37% average
However, the AVG(PR Pct of Price Allowed) column will calculate the average of the percentages, like this:
- PR Pct of Price Allowed 50% + PR Pct of Price Allowed 30% = 40% average
To work around this caveat and see a true raw average for a row-group, export the data to Excel and then add a calculated column.
That said, the AVG() function does perform weighting, so if you create a two-level row-grouping, the top-level average will show the proper weighted total of all the second-level averages. For example, you might have data like this:
- Medicare: 5 trips, total weighted average is $236
- Medicare Part B: 2 trips, average is $215
- Trip 1 $200
- Trip 2 $230
- Medicare Advantage/HMO: 3 trips, average is $250
- Trip 3 $230
- Trip 4 $230
- Trip 5 $290
- Medicare Part B: 2 trips, average is $215
You can see that $236 is the proper weighted average of the five grand-child rows.
As described above, the averages only become misleading when the data you are averaging is a pre-computed percentage column.