Goal: Generate 8 different monthly member reports for Califaep.org’s PayPlans-powered Joomla membership site.
Solution: I’ve chosen Fabrik to query the database, display member content, filter info, and generate CSV files.

Background: Califaep uses PayPlans to register members, and is using JomSocial’s custom fields to capture specific information during the registration process. Unfortunately, JomSocial’s custom fields data is not joinable, making it a pain-in-the-ass to query – if it worked at all. I replaced JomSocial with JSN Project’s Easy Profile (it works by extending the Joomla User Profile). Easy Profile also provides a nice Users List (which we also needed).

After creating custom fields with Easy Profile, freelance SQL developer (and fellow Toastmaster), Joe Shaw, wrote a script that populated Easy Profile’s custom fields with JomSocial’s custom fields data. I’m hugely in debt to Joe, as this isn’t a step that I knew how to do.

A) Create the first Report List

I used Fabrik to display membership data as Lists, filter data based on multiple “Chapter” locations, first-time and renewal payments received each month, and first-time members and renewal members, as well as expired members.

And instead of creating 8 Lists from scratch, I’ll create one List that has everything I need. Then use Fabrik’s nifty Copy List function to quickly create the remaining 7 Lists. Then, edit the remaining 7 Lists by adding/deleting/altering Pre-filters and Joins as necessary.

Creating a Fabrik List called “Report for Actual New Members” (eg: first-time members who paid during June 2016):

Go to: Joomla admin > Components > Fabrik > Lists

Details page:

1. Click green New button > Text tab: Report – Actual New Members
2. Filter tab > Filter trigger: Submit button; Filters: Above
3. Advanced tab > Alter field types: No; Cloak Emails: Yes

SAVE

Data page:

1. Data tab: Database table: users; ORDER BY: jsn_user.firstname ASC (must Save before ORDER BY is available)
2. Pre-filter tab: Click Add:
   1. WHERE payplans_plan.plan id IN 1,2,3,4,5,6 (Type: text)
   2. AND payplans_subscription.status = 1601 (Type: text)
   3. AND payplans_subscription.subscription_date >= DATE_SUB(NOW(),INTERVAL 1 YEAR) (Type: No quotes)
3. Joins tab: Click Add:
   1. LEFT JOIN: users.id = jsn_users.id (Repeatable: No)
   2. LEFT JOIN: jsn_users.id = payplans_subscription.user_id (Repeatable: No)
   3. LEFT JOIN: payplans_subscription.plan_id = payplans_plan.plan_id (Repeatable: No)
   4. LEFT JOIN: payplans_subscription.user_id = payplans_invoice.user_id (Repeatable: No)
   5. Display mode: Merge rows and reduce data

SAVE

Publishing page:

1. CSV tab:
   1. Heading format: Element label;
   2. Include filters: Yes;
   3. Which Elements: Selected;
   4. Elements: Click Select, click “+” and add elements to be Exported in CSV (this allows specific columns of data to be displayed in a specific order), then click “close”;
   5. Include raw data: No;
   6. File name: Report-Actual-New-Members.csv (this gives the CSV file a specific name, otherwise it’ll just be the name of the database table “users”)

SAVE

Access page:

1. Access tab:
   1. View list: Public
   2. Remaining drop-downs, set to: Special

SAVE & CLOSE

Go to: Joomla admin > Components > Fabrik > Elements

Filter: Select Group: Report – Actual New Members

There are 2 columns on the right side of the elements page: Show in List and Published. These control what is displayed in the List on the front-end of the site, and what is Published in the CSV. Select all the elements that you do NOT want displayed on the front-end, then click Remove from list. Then, select all the elements that you do NOT want published in the CSV file, and click Unpublish.

Note: Changing the element’s Label field changes the Header text in the CSV file. This happens because the Heading format was set to Element label in the CSV Publishing tab earlier.

Filter: Select Group: choose the remaining Group JOINS one-by-one, and go though the above process for all of their respective elements:

• Report – Actual New Members [jsn_users]
• Report – Actual New Members [payplans_invoice]
• Report – Actual New Members [payplans_plan]
• Report – Actual New Members [payplans_subscription]

For the Report – Actual New Members List, there are 2 elements that need to have Filters:

Filter: Select Group: Report – Actual New Members [jsn_users] > Click aep_chapter

1. Details page > Change Label field to: AEP Chapter
2. List view settings page > Filters tab > Filter type: Dropdown

Filter: Select Group: Report – Actual New Members [payplans_invoice] > Click paid_date

1. Details page > Change Label field to: Paid Date
2. List view settings page > Filters tab > Filter type: Range

B) Creating the remaining 7 Report Lists

Now that the first Report List has been created in full, it’s time to create the rest of the Report Lists.

Go to: Joomla admin > Components > Fabrik > Lists

1. Select the Report – Actual New Members List and click Copy
2. Replace all instances of Report – Actual New Members with Report – Actual Renewals

A few adjustments to the Pre-filter, Joins, and Element Filters sections of this new Report – Actual Renewals and it’s ready to go.

Continue to make Copies of the first Report – Actual New Members List, and make adjustments.

C) Menu Access

Create > Menu Item Type > Fabrik > List

D) Populate Easy Profile with JomSocial data

Finally, we need to populate the Easy Profile custom fields with the data that’s in JomSocial’s custom fields.

1. In Easy Profile, click Sync Users (this links Easy Profile with Joomla User IDs)
2. Run Joe Shaw’s custom MySQL script via phpMyAdmin.
3. PayPlans > Configuration page > Settings tab: switch Registration integration from JomSocial to Joomla
4. Unpublish JomSocial.