Login or Join the Community (free)[You only need to join if you want to post to our forums or subscribe or donate.]
Thanks to our sponsors: 
OpenVPMS is developed with the help of various tools including the Java profiler 
Thanks to our sponsors:
OpenVPMS is developed with the help of various tools including the Java profiler
Re: Generating an Active Client By Period List
Trevor - attach the report that you have and I will give it a hack. What client details do you want displayed?
Regards, Tim G
Re: Generating an Active Client By Period List
Hi Tim,
I'm after client name and full address. patient name not reqd.
Trev
Dr. Trevor Pavey
Principal
Lakeside Vet Centre
Re: Generating an Active Client By Period List
Trevor - what release of OpenVPMS are you running?
It just struck me that we are on 1.8 and Java 8 and I have switched to Jaspersoft Studio for report development. I think that I need to fire up an older machine to tweak the report for you because I suspect you are running 1.7. True?
Regards, Tim G
Re: Generating an Active Client By Period List
Jaspersoft Studio can build reports for version 3.7.5 in some cases Tim. It can be a bit hit and miss but I have had some success.
Re: Generating an Active Client By Period List
Hi Tim,
Are we through the beta 1.8 into the final release now? If so I was going to get Tony to update us anyway. Otherwise yes, we're on 1.7.
Trev
Dr. Trevor Pavey
Principal
Lakeside Vet Centre
Re: Generating an Active Client By Period List
Ben - I found it more trusty to fire up my old laptop which has 1.7 running.
Trevor - the report is attached. It is renovated to add a run date/time at the top, a Display Explanation checkbox, and a 'No data Found - check parameters' warning if it finds no output - you get:
You can see that I intentionally screwed the start date to be after the end date.
The line at the bottom is the explanatory text. Note that I adjusted the SQL to look for invoices.
Sample output is:
Don't be concerned about the funny names and addresses - this is coming off an anonymised test database.
By the way, if you run the report you sent me you will get slightly different numbers (in my case for the same Jan 2014 period as above, it shows 1629 clients). This is because it is looking through the invoice, counter sale and credit line items, and going from the patient to the owner. As indicated in the explanatory text, I am simply using the invoices.
Regards, Tim G
Re: Generating an Active Client By Period List
Great thanks Tim, works nicely.
Dr. Trevor Pavey
Principal
Lakeside Vet Centre
Re: Generating an Active Client By Period List
Actually Tim is there a way to have separate columns for each parameter. ie. client name, street address, suburb, phone number (though i dont need ph number for this report) as it will make mail merges easier.
Dr. Trevor Pavey
Principal
Lakeside Vet Centre
Re: Generating an Active Client By Period List
Ahah - the dreaded creeping featuritus and incompletely specified project.
Here is sample preview output from our Customer Marketing Export report (some details blurred to hide them):
The Explanation is:
Is this what you want? [By the way - I can see a bug - the times should not show on the start and end dates.]
Regards, Tim G
Re: Generating an Active Client By Period List
Trevor -please see attached export report. This is a backported version of my 1.8 version (which also allows selection by the customer's preferred practice location [a 1.8 addition]). It correctly handles cases where the customer has multiple contact locations - specifically, as it says in the explanatory text:
Regards, Tim G
Re: Generating an Active Client By Period List
Hi Tim...
I just ran this at home on a very limited, old practice database and will do a run on the production db tomorrow. I don't see that it is reporting email addresses however, which is what I really need. But it is catching some records at least. So I will see if I can merge these two reports somehow or using this as a guide, see where the problem lies in the previous report that catches 0 records.
Thanks. Getting there.
Sam
Re: Generating an Active Client By Period List
Sorry Sam - I screwed up. Try the attached.
This should do the trick. [Tick the 'Display Explanation?' box the first time you run it] Note that the Email Name field is fairly useless in 1.7 systems - in 1.8 system it can contain the text name of the addressee.
If you are running this on a system with a non-current database, ensure that you set the Sales From date back far enough to find some sales. In an active system, you can leave the Sales From date at its default of today and it will show you today's customers.
Regards, Tim G
Re: Generating an Active Client By Period List
Sorry Tim...
I seem to have posted this to the wrong thread as my original inquiry was here. But this report is still catching 0 records, even on our production db. My range for DOB is from 1/1/1980 to today, and sales from 1/1/2015 to today. Wide open for everything else. I've attached the exported .csv file (had to add an additional .txt ending for upload). It just has the report headings. The report must be freaking out on something.
Sam
Re: Generating an Active Client By Period List
Humm - OK - I ran it on my 1.7 system and out came stuff, and from the CSV file the parameters look good. So - debug needed. The approach I take is to fire up iReports and MySQL work Bench and copy the SQL code from iReports and paste it into Workbench. You will have to edit the code to replace the $P{xxx} parameters with actual values - ie '%' for the text selections and '2010-01-01' etc for the dates. [You can also do the much same thing in iReports using the data preview facility in its SQL editor but you have to get the parameter values preset and I prefer Workbench because I can quickly clone other queries for testing.]
Having done that it should produce no records (just like running the report). Now start commenting out parts of the selection code (using /* ... */) and see what happens.
My guess that that you will find that there is something flakey with the classification selection - ie the clause
l.name like concat(ifnull($P{Classification},""),"%") and
In our system every customer has an account type [and we have a specific report to look for customers who have not been given an account type]. However, the above selection clause does not allow for l.name being null (because null does not match "like '%'". Replacing the above by
ifnull(l.name,"-") like concat(ifnull($P{Classification},""),"%") and
will fix the problem (because if l.name is null, then we change it to "-" which will match the "like '%'".
Can you please verify that this is indeed the problem. If it is I need to update the 4 export reports I contributed to the 1.8 release.
Regards, Tim G
Re: Generating an Active Client By Period List
Hi Tim...
Thanks for all the effort there. Changing that line in the query does indeed catch records now and does exactly what we need for the moment. We don't use the Classification tag and I was wondering about that. I thought that the 'ifnull' in the original line took care of that, but I see now I was misreading it. Looking a bit more carefully, I see now that it was saying if the Parameter is null, then make the Parameter '%'. Also, good to know that % does not match null... I think I had read that somewhere else but had forgotten it.
Anyway, thanks very much for your help on this. It is a pretty powerful report isn't it?
Sam
Re: Generating an Active Client By Period List
Sam - thanks for the feedback. I will tweak the 1.8 report to allow for the 'some practices do not use the Account Type facility' problem. [We use it because it allows us - via the linked alert facility - to flag the house call business customers. We also use Platinum/Gold/Silver alerts (ie in top 5, top 25, top 100) to flag how important the customer is - so if the left panel shows:
Regards, Tim G
Re: Generating an Active Client By Period List
Hi Tim,
So I ran the report with the correction and copied the resulting email list to our email program, but then realized that not all of the addresses were being concatenated in the spreadsheet. There are 109 emails, but only 71 were making it to the concatenated list. I'm exporting to LibreOffice Calc so I thought that the issue was there (no), but eventually did track down the problem and thought I should pass it on.
The problem seems to come from the size of the AllEmailAddresses variable window in the template itself. Shrink the window and the email list in the spreadsheet gets shorter. Make it larger and all the emails appear in the concatenated list. It's a little tricky in iReports to get the page large enough to hold the results, but I made it poster-sized (not printing it anyway!) which then allowed for a larger window.
Tricksy...
Sam
Re: Generating an Active Client By Period List
Humm - I must admit that I thought that the truncation would not happen in the spreadsheet output - but I have just confirmed that it does occur. [Of 1000 email addresses in the list I only got the first 85 concatenated.]
I have just verified that with Outlook I can copy the complete column of 1001 addresses and paste them into the To slot - and the last one I can see is Ms Zhuo so Outlook at least swallowed the pasted column from the spreadsheet.
I will remove this feature. Thanks for the testing.
Regards, Tim G
Re: Generating an Active Client By Period List
That's such a useful feature, I would hate to see it disappear. I'll check as well whether Thunderbird will allow me to paste the data directly from the email column into Bcc and get back to you. I'm wondering if there might be some other workaround, such as making the font size in the template window really tiny, or seeing whether iReports has some ability to not "display" in the window, and yet still pass the data. I guess iReports isn't able to grow the window as required eh? In iReports, I see that the window property "Stretch with Oveflow" is checked and described as "Stretch the field vertically if the text does not fit in the element", so I'm surprised that doesn't fix the problem automatically.
Sam
Re: Generating an Active Client By Period List
Amazing what a little pressure does to developers. I fixed it by setting the font size to 1. It is now unreadable in the preview/printed version, but now our complete set of email addresses (some 3300) fits easily and my estimate is that you could fit well over 10,000. The summary (with Explain ticked) now looks like:
So adjust the font to 1 point and it should do the trick.
Thanks for pushing for a solution.
Regards, Tim G
Re: Generating an Active Client By Period List
Hey, there's always a way! Good job there! I'll make the change too.
Sam