Report query
Hello - just wondering if anyone can help me out with 2 reports that I have run and have got very different results for the same time period.
Practice Clinician Sales Analysis Report
Clinician Sales Report
Just wondering where does open get the information from with each invoice - is it the Reference field or is it the clinician name against each product line?
The reports are out by several hundred thousand dollars - I suspect that the Practice Clinican Sales Analysis report is not accurate, else I will have to say goodbye to a number of vets - myself included!
Thanks Anna
Re: Report query
Anna: I have just run both reports and the outputs match. However, I am running what is functionally 1.9.1, and you don't say which version you are running.
Note that all the standard reports have a 'Display Explanation' option and checking this will explain what is shown by the report. Specifically that for the Practice Clinician Sales Analysis Report says:
This report shows sales data by clinician and location. It includes customer invoices, counter sales less credits between 00:00:00 on the specified From date and 23:59:59 on the specified To date.
It includes only finalised transactions. All amounts are tax exclusive.
You can limit the data by Location and Clinician. Both these selection fields have % added so that leaving them blank will find all, and entering say Location %branch will find all locations containing 'branch' in their name, and Clinician c will find all clinicians with names starting C. Note that the selections are case insensitive and that using '-' will find only those with no name set.
The Clinician selection uses the Last Name if set, else the Full Name field.
Three methods are used:
Item: includes all line items for the clinician irrespective of the clinician set for the transaction itself
The Amount is the sum of the line item amounts, the Count is that is the number of line items
Transaction: includes all transactions for the clinician irrespective of the clinician(s) set for the line items
The Amount is the sum of the transaction amounts, the Count is that is the number of transactions
Includes item: includes all transactions that include at least one line item with this clinician set
The Amount is the sum of the transaction amounts, the Count is that is the number of transactions
That for the Clinician Sales Report says:
This report shows sales data by clinician and optionally by location, product type, product and individual sales.
It includes invoices, credits and counter sales between 00:00:00 on the specified From date and 23:59:59 on the specified To date.
You can limit the data by Clinician, Location, Product and Product Type. All these selection fields have % added so that leaving them blank will find all, and entering say Product Name %vacc will find all products contain 'vacc' in their product name, and Location c will find all locations starting C. Note that the selections are case insensitive.
The 'Location Totals', 'Product Type Totals' and 'Product Totals' options control whether or not the location, product type and product totals are shown. Selecting 'Product Totals' will functionally select 'Location Totals' and 'Product Type Totals'; selecting 'Product Type Totals' will functionally select 'Location Totals'.
Individual line items are shown if the 'Show Details' option is selected.
The percentages shown are the percentage of that item of the Grand Total.
So the Practice Clinician Sales Analysis Report uses 3 different metrics to measure the clinician's output. This was done because the practice who originated the idea for the report wanted to measure what the clinician was contributing to as well as the items directly attributed to him/her.
The Clinician Sales Report looks only at the line items - and the total of these for the clinician does match that of the 'Item' metric on the Practice Clinician Sales Analysis Report.
Looking though my Sent email tray I see that I submitted updates for both reports on 11Aug2016 - and that my note to Tim A said:
Tim – the Financial.zip at XXXX is the reports/Reporting/Financial folder containing the updated versions of the Practice Clinician Sales Report & Clinician Sales Report [as a result of input from Adrian S].
Although the Clinician Sales Report contains only a minor tweak to its explanatory text (to say that the numbers come from the line items), the Practice Clinician Sales Report is a major rewrite. See http://www.openvpms.org/customisation/practice-clinician-sales-report if you want details. [Note that the 1.8 version in the resource library differs from this one in that a) it is set for JasperReports 5.5.2 (not 6.2) and b) it does not use the EVALUATE("user:format($F.clinician_id, 'short')") function
So Anna, I would suspect that you do not need to sack yourself. If you are running 1.8, grab the Practice Clinician Sales Analysis Report from the Resource Library. If you are running 1.9 then (provided you loaded the new report templates when you did the upgrade) you should have what are functionally the latest versions. [My 1.9.1 versions are tweaked to use the DejaVu fonts (to help with windows/linux compatability) but are otherwise identical to the 1.9 versions.]
Regards, Tim G
Re: Report query
Thanks Tim G but I still think there is something odd going on.
The report I meant was Practice Clinician sales analysis report - which is this one
and when I compare the totals of this to the Clinician Sales report I get very different figures.
I have also had a look at the other report Practice Clinician Sales Report which is this one - yet when you print out the report the title of the report is Practice Clinician Sales Analysis Report...
So this report that is PCS gives me similar figures to the Clinician Sales report - that is the Item on the PCS report for each clinician is similar to their total on the Clinician sales report.
However even when I read through the explanation of the Practice Clinician Sales report I still dont understand the difference between Item, transaction and Include Item
Item - is all line items made by the clinician but what does : irrespective of the clinician set for the transaction itself'? is this the vet that has loggin the computer or the clinician's name in the window on the invoice?
Transaction - is this all invoices written with the clinician's name in the top part of the invoice
Includes item: ? just dont understand this
So I think I have figured things out!
1. Clinician sales report = items on the Practice Clinician sales report (figures are not the same but are ball park)
2. Practice clinician sales analysis report = Transactions on Practice clinician sales report
but I still dont understand the meaning of the Transactions (does it mean Invoice) and Includes Items and how this can be used to measure a clinicians output?
Also whilst I am on the topic of vets charging the approach we take is that if you do a procedure or invoice an item then you put your name against this. We have in the past written the name of the vet who commenced the invoice at the top of the invoice but when it no longer was a required field we have skipped this field. Is this is how OPEN was designed to work?
Also on reports I am trying to add in the Estimates Report that has a sub report but I am not having much luck
so I am not sure why it is not working? I have not done many sub files in the past.
Thanks for your help.
Anna :)
Re: Report query
Anna - to deal with things out of order:
1) Estimate subreport not working: Subreport are linked to their parent report by their content name. Hence in the case of the standard 1.9.1 Estimates Report, its subreport content file name is "Estimates Report subreport.jrxml". The name of the subreport does not matter ("Estimates Report Items" in your example above) - but the content file name is critical - the "EstimatesReportItems_0.jrxml" in the above is incorrect.
Given the content name, I am confident that the report you are using is the one from the Resource Library at www.openvpms.org/customisation/estimates-report and its subreport content file name MUST be "EstimatesReportItems.jrxml".
I have just added a note to http://www.openvpms.org/resources-using to this effect.
Hence to fix the problem rename the file EstimatesReportItems_0.jrxml to EstimatesReportItems.jrxml and update the subreport's content form this file.
2) What version of OpenVPMS are you running? Note that 1.9.1 included a number of fixes to report calculations (see http://www.openvpms.org/openvpms-1.9.1 ). If you are running 1.9 and cannot be bothered upgrading to 1.9.1, then it would still be worth downloading the 1.9.1 package, and loading its report set. ie download the zip file, expand it, then use templateload - see http://www.openvpms.org/documentation/csh/1.9/topics/installing-openvpms...
3) How to measure a clinician's contribution to the practice? The clinician(s) associated with an invoice are recorded in two ways:
a) against the invoice itself; b) against each line item in the invoice.
[Note that the user entering the invoice is also recorded - but as the author, not the clinician. So we ignore who entered the invoice and its line items and focus on the clinician.]
[Note also that we need to take into account any reversed invoices (ie credits) and hence the use of the term 'transaction' - ie invoice or credit or counter sale.]
So we can look at the clinician recorded against the transaction. But what about the cases where Dr A does the initial consult, but Dr B does all the surgery? Here Dr A will be recorded against the invoice and the consult line item, but Dr B against all the surgery line items.
Hence, the need to count 'by line item'.
However, Dr A could argue that if he/she had not admitted the patient, there would have been no surgery income, and hence he/she should also get some of the credit for the income generated from Dr B's surgery work. Similarly Dr B could argue that without his/her availability to do the surgery, the consultation might not have happened, so he/she should get credit for the whole invoice.
Hence the need to count 'includes item'.
So if we have a $1,100 invoice recorded against Dr A with $100 worth of consult by Dr A, and $1000 worth or surgery by Dr B, then we have:
4) Practice Clinician Sales Analysis Report: your picture shows that this is an old 1.3 report. I did not write it. However, I have had a look at the code. It is counting 'transactions' - ie invoices plus counter sales minus credits. It is counting the ex-tax amounts. Here are its grand totals for one day:
Here is the 1.9.1 version's grand totals (and explanatory text) for the same day:
You will see that the Transaction totals match exactly.
5. You said "Also whilst I am on the topic of vets charging the approach we take is that if you do a procedure or invoice an item then you put your name against this. We have in the past written the name of the vet who commenced the invoice at the top of the invoice but when it no longer was a required field we have skipped this field. Is this is how OPEN was designed to work?"
OpenVPMS allows you to capture who does the work (by setting the vet against the line item). To go back to our Dr A/Dr B example - if you were Dr B wouldn't you want credit for the $1000 worth of surgery that you did?
Bottom line recommendation: even if you don't want to upgrade to 1.9.1, install its report set.
Regards, Tim G
Re: Report query
Thanks Tim G - I will go all through this tonight - we are on the version just before 1.9 - we wanted to upgrade but our hard & soft ware needed upgrading so that is done and next is upgrading to 1.9.
Thanks Anna