CiviCRM Community Forums (archive)

I need to create a report that will allow me to graph the trend in our membership over time.  I want to be able to plot current member count by date, preferably for different membership types, so we can track the effect of diffferent membership drives.

For any historical date, e.g. 1 March 2010, I can see how many members I had using
select count(*) from civicrm_membership where "2010-03-01" between start_date and end_date;

So one option would be to put ths in a loop, either in a stored procedure or in php, and execute it substituting the first day of each month for the specified years. I'm not exactly sure how one would do this, but it seems plausible. But I'm also wondering whether there is a better way, or someone has already done something like this?  It seems like an obvious thing to want to do.  Is there a report type I have missed?

OK - so it looks like a new report.

I am unsure as to whether CiviReport allows you to / if there is a suggest method for looping SQL queries.

The query that you would need to loop would be something like the following (where date is the intervals that you want to measure).

SELECT '2011-01-01' INTO @date;
SELECT civicrm_membership_type.name, count(civicrm_membership.id)
FROM civicrm_membership
RIGHT JOIN civicrm_membership_type ON civicrm_membership_type.id=civicrm_membership.membership_type_id
WHERE start_date < @date AND end_date < @date GROUP BY membership_type_id;

Any suggestions?

JulieR says "BTW there was a typo in your SQL: WHERE start_date < @date AND end_date < @date - you need end_date > @date.  This is why I tend to use the more readable between operator: WHERE @date BETWEEN start_date AND end_date."

Agree that this is a better approach to writing this query

I've done part of this job, but not complete.  Written for v3.3.6.

I've put the report TPL and PHP here:
http://www.phdcc.com/download/civicrm/MemberCount.zip
Put these in your custom template and PHP folders CRM/Report/Form/Member/

You need to Register New Report Template, as described in here:
http://wiki.civicrm.org/confluence/display/CRMDOC40/Creating+A+Report+Template+Extension

It's hardwired as follows in two places:
  civicrm_membership_type.id=7 AND contact_type='Organization'
so you'll need to change/remove that.

The Frequency selection does work, but the date range criteria isn't acted on.

The mian work is in the select() function which
- creates a temporary table
- gets the first membership join date
- loops through all dates from then until now, inserting the member count at each date

I haven't been able to complete this report code yet, but thought it may be useful "as is".

Chris