Home › Forums › implementation › Implementation discussion ›

Sales report by payment type and month

Submitted by Canberravillage... on Mon, 13/03/2017 - 21:08
in

Hi there,

I plan to create a sales report that breaks down monthly sales by payment type. Any tips would be well received.

I have a workstation with Jaspersoft Studio connected to a MySQL database. However, my limited experience with JRXML and SQL is problematic. My language experience is basic Python and Bash. 

It would seem a sensible approach to start with the Other Payment Types Report in the online Resource Library. However, this report gives blank output on my OpenVPMS 1.8.1 installation. 

Tim G's recently improvements to the Till Balance report look promising. Expanding it to request and sum a date range would do the trick, but the source in that report is beyond me.

Any direct advice or online references will be much appreciated.

Thanks in advance

Damien
 

‹ report to give statistics for visit reason during a period Patient Summary Invoice Items missing ›

Comment viewing options

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

Re: Sales report by payment type and month

Submitted by tgething (not verified) on Tue, 14/03/2017 - 04:07.

Damien - long time no talk.  If you tell me exactly what you want in terms of input parameters and output, then I can knock up someting for you.

I suspect that the input parameters are: Date From, Date To, and Practice Location.

Output is: for each selected location shows the totals of each payment type (cheque, credit cards, eft, cash, other) for each month in the selected date range.

Show payments and refunds separately. Only include finalised transactions. No 'show details' option to show individual customer's payments.

Regards, Tim G

PS - I had a play with the Other Payment Types Report - I also got no output. Investigation shows that it was only looking at type 'other' and we don't have any of these.

Re: Sales report by payment type and month

Submitted by tgething (not verified) on Tue, 14/03/2017 - 06:18.

Damien - I had some spare time - have a play with the attached. Note that testing has been done under 1.9 but it is compiled for Jasper Reports 5.2 and hence should run under 1.8.  Also there are no 1.8/1.9 database structure changes that will affect this report.

Yell if you need changes.  When you are happy, I will put it in the Resource Library.

Regards, Tim G

Re: Sales report by payment type and month

Submitted by Canberravillage... on Tue, 14/03/2017 - 12:43.

Tim,

This report is precisely what I was looking for. It also runs fine in OpenVPMS 1.8.1.

Please add it to the resource library. I'm sure there are other clinics in Hong Kong that would be interested in the data generated by this report.

Many thanks for your efforts

Damien

Re: Sales report by payment type and month

Submitted by tgething (not verified) on Tue, 14/03/2017 - 15:24.

Final version (with some pagination tweaks)  is available at http://www.openvpms.org/customisation/monthly-payments-summary

Regards, Tim G

PS - the accountant at EIAH likes it so it is useful to others.

Syndicate content