Home › Forums › Users › General User's Discussion ›

Active Clients by Period Report

Submitted by Guest on Thu, 17/11/2011 - 17:48
in

I can not get this report to work, I keep getting an errow message (Failed to generate Report: Errow executing SQL statement for: Product Sales by Month). The other reports (by month & by year) work fine.

Glen

‹ Linked Dispensing Fees How to set up a Phone reminder list. .... ›

Comment viewing options

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

Re: Active Clients by Period Report

Submitted by tanderson on Fri, 18/11/2011 - 17:00.

The version at http://www.openvpms.org/fileuploads/Active%20Clients%20by%20Period.jrxml works for me.

Can you attach the one you are using?

Re: Active Clients by Period Report

Submitted by greenvalevet (not verified) on Thu, 24/11/2011 - 13:33.

I have tried a few different ways to get this report to work but keep getting the same message. I have attached the new provided & same error message came up that appeared previously.

Where do I go from here. The "By Year" & "By Month" reports work OK.

Glen

Re: Active Clients by Period Report

Submitted by tanderson on Thu, 24/11/2011 - 14:21.

Can you try running the following SQL in your MySQL browser?

Its the SQL that the report executes, with the parameters hardcoded.

 SELECT date_format(a.activity_start_time, '%d/%m/%Y') as date_formatted,
date_format(a.activity_start_time, '%b') as month_format,
date_format(a.activity_start_time, '%Y') as year_format,
COUNT(DISTINCT customer.entity_id) as cust_cnt
FROM `acts` a
LEFT JOIN `financial_acts` fa ON (fa.`financial_act_id` = a.`act_id`)
LEFT JOIN `participations` pet_p ON (pet_p.`act_id` = a.`act_id`AND pet_p.arch_short_name = 'participation.patient')
LEFT JOIN `entities` pet ON (pet.`entity_id` = pet_p.`entity_id` AND pet.arch_short_name = 'party.patientpet')
LEFT JOIN entity_relationships er ON (er.target_id = pet.entity_id AND er.arch_short_name = 'entityRelationship.patientOwner')
LEFT JOIN entities customer ON (customer.entity_id = er.source_id AND customer.arch_short_name = 'party.customerperson')
WHERE a.activity_start_time >= "2011-01-01"
AND a.activity_start_time <= date_add("2011-12-31", INTERVAL "23:59:59" HOUR_SECOND)
AND a.arch_short_name LIKE "act.customerAccount%item"
ORDER BY a.activity_start_time ASC

If it doesn't work, what version of MySQL are you running?

You can find this out by running:

 SHOW VARIABLES LIKE "version";

in the MySQL browser.

-Tim

Syndicate content