Author Topic: How to report number of members by State/Province? (Read 824 times)

resga · May 11, 2011, 02:16:14 am

My goal is to create a report which lists number of members by State/Province, like in the 'Membership Report (Summary)', where you can select 'Membership Type' under 'Group by Columns':

Membership Report (Summary)
Report Criteria
Display Columns
[ x ] Membership Type [ x ] Amount Statistics
Group by Columns
Member since: x Membership Type

What I would like to do, is add the field 'State/Province' under 'Group by Columns', and create a report listing how many members there are in each State/Province, like this:
Group by Columns
Member since: Membership Type x State/Province

Which would give something like this:
Georgia 12
Kansas 23
Kentucky 230
Nebraska 3
Etc.

I had a look at the "Custom Reports - A guide for writing reports on contact and case data" page: http://wiki.civicrm.org/confluence/display/CRMDOC32/Custom+Reports+%28How+to%29

But I am hoping there is an easier way to accomplish what I am trying to do, Custom Report writing looks quite daunting... Perhaps I have overlooked something in the CiviCRM Reports interface which does what I am trying to do, in a more uncomplicated way?

resga · May 11, 2011, 06:42:55 am

In stead of generating a report through the CiviCRM system, I made this SQL, which does the job. I plan on putting it in a php file, which the users can then access via a link called "Membership report by State/Province". The users don't need anything fancy, just the State/Province and the numbers.

To update the Province/State table for Denmark to the users' liking, I deleted the old ones, where '1059' is the country id for Denmark ('DELETE FROM `civicrmdata`.`civicrm_state_province` WHERE `civicrm_state_province`.`country_id` = 1059;')

I then inserted new Province/States, here are the first two:
INSERT INTO `civicrmdata`.`civicrm_state_province` (`id`, `name`, `abbreviation`, `country_id`)
VALUES ('100000', 'Storkøbenhavn og Frederiksberg', 'KBHFR', '1059');
INSERT INTO `civicrmdata`.`civicrm_state_province` (`id`, `name`, `abbreviation`, `country_id`)
VALUES ('100001', 'Nordsjælland', 'NORDS', '1059');
...and so on.

To update the Province/State fields for all records based on the postal code, I used this SQL, here are the first Province/States updates:
UPDATE `civicrmdata`.`civicrm_address` SET `state_province_id` = '100000' WHERE `civicrm_address`.`postal_code` <= 2500 AND `civicrm_address`.`country_id` = 1059;
UPDATE `civicrmdata`.`civicrm_address` SET `state_province_id` = '100001' WHERE `civicrm_address`.`postal_code` > 2500 AND `civicrm_address`.`postal_code` <= 3000 AND `civicrm_address`.`country_id` = 1059;

To build the list of members grouped by Province/State I use the following SQL. This is work in progress, so a bit rough, but it seems to be doing the job correctly:

SELECT count(civicrm_address.id) as memberships, civicrm_state_province.name, state_province_id, civicrm_membership.status_id
FROM civicrm_address
LEFT JOIN civicrm_membership ON civicrm_address.id=civicrm_membership.contact_id
LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id=civicrm_state_province.id
WHERE state_province_id > 99999
AND (civicrm_membership.status_id = 1 OR civicrm_membership.status_id = 2 OR civicrm_membership.status_id = 3)
GROUP BY state_province_id
ORDER BY count(civicrm_address.id) DESC

CiviCRM Community Forums (archive)

News:

Author Topic: How to report number of members by State/Province? (Read 824 times)

resga

How to report number of members by State/Province?

resga

Re: How to report number of members by State/Province?