Home ›

Sales Data Export Report

Submitted by Guest on Tue, 29/09/2015 - 08:51
in
Category: 
Financial
Version: 
1.8+

** Updated 15Dec15 to fix a duplicate transaction selection problem

This report is used to export sales data for analysis - specifically for income by time of day, and/or day of week. Note that this works off the date/time the invoice/credit/counter sale was finalised. See also http://www.openvpms.org/customisation/export-sales-dayhour

Parameters:

Sample output (with the explanatory text and a very restricted selection):

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 06:56.

Hi Tim,

I came across this report yesterday.  Quite timely as I am being asked for this information now.  I had a quick run-through and one thing pops up.  I see that there are no day 6 invoices.  The explanation says that day 6=Saturday (we would normally have invoices) but with no 6 invoices, 6 must be Sunday (closed, no invoices).

I'll look though and make the code tweak on our end but thought I would pass my observation along.

Thanks for the report!

Sam

Kamloops, BC

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Thu, 10/12/2015 - 08:41.

Arrrggghhh - this problem came up while I developing the report. I have just looked at what we have installed in production and it works - here is 1 Nov thru 15 Nov (with a 24 hour bucket):

As you can see it is happily finding day 6 stuff. NOTE - as the explanatory text says "The DayNum column gives the weekday index (0 = Monday, 1 = Tuesday, … 6 = Sunday)."

I also downloaded the report from the resource library and looked at its source - it also has

So where are you getting the '6 = Saturday' bit?

Regards, Tim G

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 08:35.

Oh, I see that is how the MySQL weekday function works.  From the 5.5 online documentation:

  • WEEKDAY(date)

    Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

    mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
            -> 6
    mysql> SELECT WEEKDAY('2007-11-06');
            -> 1

Sam

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 09:16.

I got it from the explanation section of the .jrxml linked above, and from your first posting in this thread...

The DayNum column gives the day or the week, 0=Sunday, 6=Saturday.
The Hour column gives the decimal hour, eg for an invoice with time 10:15:00 this will be 10.250
The Clinician and Practice Location will show as '--None--' if these are not set.
The Suburb, Postcode and State will be blank if not set.

 

OK, I'll grab the release version instead.

Thanks.

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 11:48.

Tim,

I've looked through all the reports released with 1.8.1 and don't see the one linked above, Invoice Data Export Report. Could it be installed in production of 1.9 perhaps? If there is an newer version than the one listed here, could you please post a link to it.  I can use the one above if the only correction is in the notes.

Great report by the way.  Just what we need.

Thanks,

Sam

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Thu, 10/12/2015 - 13:35.

Sam - sorry - I got confused - the Invoice Data Export report was the first built.  I then (as a result of a request from AS) built the Export Sales by Date/Hour - see http://www.openvpms.org/customisation/export-sales-dayhour - and this was the one I was testing.

Invoice Date Export works off the invoices and thus the StartTime is set to the date/time that the invoice was finalised.  Adrian - because he has lots of multi-day bills from hospital stays, wanted to look at the invoice line item timestamps - which record when that line item is created. [Even then there will be bumps - because in most cases the vets will enter stuff after the event - ie a vaccination given during the consult will not be entered until the end of the consult when the bill is being prepared, ie line item timstamp does not equal time administered.]

Hopefully by the time you read this I will have updated the Invoice Data Export report to properly document the Monday=0, Sunday=6 behaviour.

And you are correct - the Invoice data Export report is not in the 1.8.1 (or 1.9) packages. Our (Tim A and Tim G) approach is to be careful about what is added as a report in the standard package - on the basis that pumping in every new report that I build leads to potential maintenance problems.  Tim A has a well developed regression testing suite for the actual code, but we do not automatic (or manual) testing of each report for each release.

Hence unless it fills a definite gap in the suite of standard reports, my approach has been to push new stuff into the Resource Library - which after its renovation, has reasonable search facilities. [That said, there is some old old stuff which should either be discarded or renovated.]

Regards, Tim G

 

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 14:14.

Thanks for the clarification.  In our case, we have very few multi-day patients and as you say, things like vaccinations are recorded just before finalization.  We don't have the need in our practice for any finer-grained time-stamping than what is provided in the current Invoice Data Export Report.  So if you are happy with how that report works, I will use it, as it is exactly what our practice consultant is asking for.  In that regard, it is a report that fills a definite gap in the suite. 

Thank you for taking a look at this again,

Sam 

Re: Invoice Data Export Report

Submitted by mckelvey on Thu, 10/12/2015 - 23:11.

Tony,

I think I may have found a minor bug in the report.  It appears in the exported .csv file.  It seems that when importing the .csv (specifying a comma as a separator)  any invoice over $1,000 is split into two columns - the thousands are placed in one column and the balance in a second. 

I suspect that the report is picking up the invoice total from a text field somewhere.  It seems that the comma in the invoice amount is making it through to the report output. 

Other than that, it looks great.

Sam

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Fri, 11/12/2015 - 04:26.

Sam - fixed.  Interestingly Excel can handle the comma in the amount field. However, I removed it (and also from the tax field - but if you have a tax amount of over $999.99 you must have a huge invoice ;-).

Now I need to go check the other export reports.

Regards, Tim G

Re: Invoice Data Export Report

Submitted by mckelvey on Fri, 11/12/2015 - 04:36.

It is probably just in the way I have set up the import parameters in LibreCalc... strictly on commas.  But I just tried the new report and all fixed.

Thanks!

Sam

Re: Invoice Data Export Report

Submitted by mckelvey on Mon, 14/12/2015 - 06:26.

Hi Tony,

I think I may have found another issue with this report.  It seems to not pick up on the fact that some invoices have been reversed. The reverse is not included, but the original invoice is reported. I noticed this when looking at this report in a spreadsheet and doing summations. The numbers I was getting were significantly higher than what I was obtaining in a sales report for the same period.  I went round and round with this but am convinced now that the reversed invoices are the issue.  I have hand-checked this report against a client who had reversals, and while the original invoices are reported here, the reversals are not.  This obviously skews the report to higher numbers in an unpredictable way. The more reversals you have, the farther the report is out.

Could you please take a look and perhaps spin some of your MySQL Black Magic to include reversals?  This is a pretty important report and I would love to see it 100% trustworthy.

Thanks

Sam

 

 

 

 

 

 

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Mon, 14/12/2015 - 08:05.

Sam - this was designed as a 'who are we doing business with and when' tool, not a precise 'how much business are we doing'.  Hence as the name and explanatory test suggest, it only looks at invoices and counter sales. It does not report any credits.

If you are worried about reversed invoices, does that mean that you need to not only report credits but also provide the reversal data so that you can seen that this credit reversed that invoice? If so we need to add both a Transaction ID column and a Reverses ID column.

Regards, Tim G

 

Re: Invoice Data Export Report

Submitted by mckelvey on Mon, 14/12/2015 - 10:45.

Hi Tony,

Ultimately, we're trying to get to the Average Transaction Value and this report seemed like it could help us get there as it offered a list of all the invoices and OTC's as well as the number of transactions.  I guess we could get our sales numbers from another report but I'm not aware of another way of getting to the number of transactions.  Here we are over-counting the transactions, as some of course were reversed. 

Just having the credits as line items in the report would be of great help.  I would be able to get a pretty good idea of the number of transactions by taking the number of invoices and OTCs and subtracting from that the number of credits.  Since a reversal essentially wipes out the original invoice, its issuance, reversal and re-issuance would only count as one transaction. And of course the total value would be a nice cross-check to our other sales reports.

For us at least, I can't see a real need for linking a particular credit to a particular invoice.  What would make the report more flexible in its application I think, is seeing the name or ID of the client.  If something catches our eye in the report, then having the date and client will allow us to much more easily track down the details on-screen.  And in keeping with the spirit of the 'who are we doing business with' objective, we did see from this report that there were quite a large number of invoices from a particular postal code.  Without the name or client ID, however, it took a bit of detective work with Google Maps to figure out who that valuable client was.  So if I had a vote, I would say that tying a credit to an invoice is perhaps not as helpful as knowing who the invoice or credit is tied to.

I'm sorry to say that even though I'm still struggling quite a bit with the entity-relationship MySQL query structure I tried to modify the code myself, but to no avail.  I'm trying to get to the point where I can look at these reports, say 'Hey, this is a great starting point for doing such and such...', making the changes and posting them back to the community.  But I'm not there yet.  So for the most part, I'm still stuck in "request mode".  Grrrr....  So I particularly appreciate your efforts here.  Awesome.

Thanks,

Sam 

 

 

 

 

 

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Mon, 14/12/2015 - 18:47.

OK Sam, have updated and renamed to the 'Export SALES Data Report'. As you will see I also included the Customer ID, so you can sum sales per customer.

Number of transactions & sales volume: The Practice Summary Report - the income by location & income by clinician sub-reports give you the amounts and counts of invoices, OTC & credits.

Act_Relationships: I will see if I can add something to http://www.openvpms.org/documentation/csh/1.9/admin/archetype#archetypeDef if/when I get a spare moment.

Regards, Tim G

Re: Invoice Data Export Report

Submitted by mckelvey on Tue, 15/12/2015 - 02:38.

Thanks Tim,

I see that you went the full mile on this one. It looks terrific.  Thanks loads.

I'm was totally baffled however, as to why this report is still not giving me the same numbers as I am getting in other sales reports of ours.  But I sorted by transaction number and see that the report is doubling up on some invoices.  In a run over one month, it's giving about 15 duplicate lines.  Once I manually removed them, it is reporting what I see in other sales reports.  Awesome!

This was quite an important exercise for me to pursue as it allowed me to really cross-check the results of my Canadian customized reports.  Its all good.

So thank you very, very much.  And sorry for calling you Tony up there... I was reading some other posts and got distracted.

Sam

Re: Invoice Data Export Report

Submitted by tgething (not verified) on Tue, 15/12/2015 - 05:02.

Sam - it is fixed. Was due to a) crappy SQL; b) customers having a location with multiple purposes.  I had to expand the test data set to 6 months (some 20K transactions) to find duplicated invoices, and this then allowed me to understand what was happening.

Apologies for the bug, and thanks for the QA work.

Regards, Tim G

Re: Invoice Data Export Report

Submitted by mckelvey on Sat, 19/12/2015 - 01:34.

Thanks Tim,

Sorry I was away for a few days and didn't get to test it but it works fine now and cross-checks with my other reports to the penny.  I really do think that this is a key report as it is really getting back to first principles... a list of invoices that make up income.  It provides a good basis with which to compare the validity of many others.

With that in mind, I will take a look at the KPI sub-report on sales by location that you referred me to above. It looks like it could short-circuit a lot of the spreadsheet analysis required here. 

Onward!

Sam

Syndicate content