This documentation refers to an older version of CiviCRM (3.4 / 4.0). View latest version.

Skip to end of metadata
Go to start of metadata
This page refers to CiviCRM 3.4 and 4.0, current STABLE version.

Documentation Search


CiviCRM 3.4 and 4.0 Documentation

Support and Participation

Developer Resources


CiviCRM books!

Make sure to check out the FLOSS Manual Understanding CiviCRM as well! You can also support this project by ordering a hard copy.

Or support us by buying an eBook or hard copy of Using CiviCRM from Packt Publishing.

CiviMember offers a membership summary table and search capabilities via Find Members and Find Contacts. The details below outline a solution for implementing a variety of Membership Reports using DBQ - an open source extension for Joomla developed by Green Mountain Technologies. While the component used is specific to Joomla the SQL behind the reports relates to the data structures of CiviCRM v1.5 and can be used with other SQL based reporting tools.

***Disclaimer - the code shown works for me but may well not be the most elegant or efficient. Indeed, it may even deliver spurious results but it should give you a framework to work from ****

Background

I'm the membership secretary for an outdoor sports club with around 200 current members. The club has been in existence for over 75 years and has had well over a 1,000 members which we track as well for historical reasons.

To apply for membership you have to have been around for a while and get proposed and seconded by two full members before going up on a selection panel. On successful application you become a Probationary Member for a year, after which you become a full member. There are several types of membership as outlined below:

  • Ordinary membership
  • Joint Members - reduced fee for a couple
  • Probationary - anyone who has not completed twelve calendar months membership and has not been ratified
  • Life - on application after five full consecutive years membership completed at 1st October in the relevant year
  • Joint Members Life - on application after five full consecutive years membership completed at 1st October in the relevant year
  • Honorary Life - anyone who has performed outstanding services for the Club and is voted as such at a general meeting
  • Lapsed -  an ex-member.

Our club year runs from 1st October through to 30 September. Fees are payable during a grace period that runs from 1st October to the end of the calendar year. After this period membership is set to Lapsed until they pay up again.

I've added three custom fields, two of which relate to insurance details, while the third is a unique Membership Number. The Membership number is important as it is used in the duplicate matching rule as (custom_3 AND first_name AND last_name).

Reporting Requirements

While there may be many reports required by organisations the following reports where created:

  • Membership Stats
    Counts number of members by Membership Type. While CiviMember has a Membership Summary table this includes details of every member even if they are lapsed or deceased. This report was created to give a real life summary.
  • Address List
    Current Members Address List - Prints out all members primary address and contact details in a format which can be cut and paste into Word to create an address booklet
  • Historical Membership List
    Displays basic details of every member since the Club began
  • Paid this year
    Members who have already paid this year.
  • Paid last year but not this year
    Members who have not paid this years subs yet. This is the list to use for chasing up dues.
  • Current membership list
    Admin details of all current members
  • All members, name, email, membership num
    Can be used for importing on table keys
  • Address Labels
    Use the Advanced Search Mailing Label function (modified to handle specific Avery media)

Reporting Method, Tools and Approach

While CiviCRM handles all the backend data and basic manipulation routines it has no built-in reporting that you might expect. To complicate matters, the CiviCRM database structure consists of over 80 tables with numerous indexes, keys, foreign keys and cross table entities. How the heck do you write reports to extract information out of these tables? Presenting these reports via Joomla would require delving into not just SQL but also PHP, neither of which are listed as skills on my CV. Surely you'd need a degree in rocket science to get started here?

Wrong. Looking through the Joomla Extensions directory my eyes were drawn towards Database Query (DBQ) from Green Mountain Information Technology Consulting http://www.gmitc.biz.

DBQ looked like the answer to my problems but from the description had a steep learning curve.

I've installed a lot of Joomla 3rd party extensions. Many I've evaluated for a while and can't get to grips with and have uninstalled in favour of another solution. Others look great and then you realise that they are missing something important and development is completely dead. Sure, people's priorities change. When you decide to go with a codebase you need to evaluate some other factors than whether or not it does the task I initially want it to do. These include:

    * Is the project active?
    * Is its use documented?
    * Do questions in the support forums get answered?
    * Is feedback positive?

It was clear that DBQ was active from the SourceForge activity. Not only is there a user manual but there is also a getting started style tutorial. These are significant documents which include all the information you need and are brought up to date. Due to this, the postings on the forum tend to be pretty specific and are answered expediently. Feedback on DBQ is 100% positive with many users blown away by its flexibility.

I decided to take the plunge.

First Impressions

Installation, as with most Joomla Extensions, proved to be very easy. Often the problem comes when you first need to configure the component and start using it. Using the Introductory Tutorial configuration proved to be a doddle. Within minutes I was able to cut a basic report and display the results within my Joomla site.

Getting more out of DBQ requires some knowledge of SQL and your database structures. Luckily there are numerous tools out there to assist with this.

My initial thought was to find an Open Source report writer which I could use to interrogate my database tables and output SQL code.

Delving Deeper

After a couple of false starts I quickly discovered that a really good way to achieve this was to use a combination of phpMyAdmin to look at the data structures and since I was running on mySQL, the mySQL Query Browser. To get an understanding of how CiviCRM databases hang together check out the Entity Relationship Diagram.

Additionally I wanted to be able to work locally and then cut and paste the code into DBQ. To achieve this I loaded WAMP from wampserver which includes Apache, mySQL, PHP and phpMyAdmin out of the box. Loading the mySQL Query Browser locally empowered me to very quickly start build SQL queries against my data and see if they worked. At this stage I'd very little knowledge of SQL and was flying by wire. The Query Browser has inline help which describes the syntax for every function you need. That coupled with the numerous online SQL tutorial sites enabled me to very quickly prototype complex queries joining numerous tables.

Occasionally I'd get the syntax wrong or the code returned too few or too many results. This is not rocket science and can be learnt via trial and error quickly.

DBQ behind the scenes

Once I'd prototyped my SQL queries it was trivial to cut and paste into the DBQ component and present the results.

Back in DBQ there are extensive tools for working with variables and dynamically creating forms. All of this is well documented and follows a logical series of steps.

DBQ is incredibly flexible and as well as handling all the presentation issues has a fantastic set of debug tools. Where my code caused problems on the live site it proved easy to establish why things hadn't worked.

Conclusion

DBQ is without doubt one of the finest Joomla components I've ever had the pleasure to use. The component itself is very well structured, supremely flexible, well documented and supported. DBQ in itself is not complicated and does not have a steep learning curve. Period. Users without knowledge of SQL and database structures will have to work this out but they won't have to worry about encoding the SQL commands in PHP and presenting the results. There is a whole heap of settings and functions which I've yet to utilise which only lead me to believe that DBQ is incredibly powerful.

If you need to manipulate databases in Joomla you don't need to evaluate the other solutions, just use Database Query. Quite simply it is Excellent!

Labels
  • None