Advanced Reports for SuiteCRM/SugarCRM (using SQL)

Oct 21
2018

Download

I simply could not find a reporting tool for SuiteCRM/SugarCRM that had the power to report based on any database query, so I decided to build one.

If you know SQL and the CRM schema and you would like to use it to build reports available inside your CRM this tool is for you. If you don’t know SQL or the schema, this is not for you.

Installation

Simply install the package using the module loader as with any other module.
You have to run a quick repair after installation since it uses the extension framework for the menu links to work. (See below).

How To

To use this reporting tool, simply write a query that return the data you want, include some smarty style variable(s) (eg . {$city}) in your query and paste the query into a new record in this module.
The code will extract the variables and allow you to set the label, type, etc. for each variable found in your query.
It will also run the query and allow you to set the label, type, size, grouping, etc. for every column the query returns and voila! You have your first report.

Examples

Want to return all contacts for a specified city:

  1. SELECT first_name, last_name, phone_mobile FROM contacts
  2. WHERE primary_address_city='{$city}' AND deleted=0

How about all contacts for a state and a count per city?

  1. SELECT first_name, last_name, phone_mobile, primary_address_city
  2. FROM contacts
  3. WHERE primary_address_state='{$state}' AND deleted=0
  4. ORDER BY primary_address_city

Then select the city as group one field and set any other field to count.
Get the drift?

You can set any column to be the outer group, inner group, value,
count, average or sum in a subtotal or total line.

Unions, Views or Stored Procedures?

Want to use a really complex query with UNION(s) or database views or even a
hugely complex stored proc that calculate loads of stuff before returning rows?
Be my guest, just put

  1. call sp_name({$var1}...)
inside the query of a record in this module.
The power of what you can do is limited only by your imagination and SQL skils!

Templates and Style Sheets

Looks is not my strong point.
The module comes with one very basic look for reports that is built using smarty templates in modules/adrep_report/tpls/header|footer|row|css folders.
You can create your own templates or CSS by adding files (with a different name) to custom/modules/adrep_report/tpls/header|footer|row|css folders and then add the name(s) of your style(s) to the adrep_css_file_list dropdown.
You can create only the files you want to change, any missing file(s) will default to the built-in Basic one. If you only want one look just call your custom files Basic and they will get used instead of mine.

Menu Links

Something that I find really useful (for users) is to link reports directly to modules or even records all over the CRM by adding them to the menus.
Let’s sat you have a products module and every product has prices. Just build a price list report that has a variable {$product_id} and then link your report to the DetailView of your products module. Voila!  Now your users can run the
price list directly from any product’s DetailView. 🙂

Pagination and caching

The data returned by your queries are cached in the adrep_cache module to allow pagination or exporting without having to re-run the query every time.
The query is re-run every time a specific user runs the report from scratch, not for paging through results or exporting.
Reports can be downloaded/exported as CSV, HTML or PDF (on SuiteCRM).

Charts/Graphs

You will see in the schema and modules that I am adding charts/graphs to the reports using the cached data. That is a work in progress so watch this space.

Security notice!

Lastly – Be very careful who you give edit rights on this module!!
Since it queries the database directly a user could use this to extract any data from your database. Really only trusted admins should be able to edit reports!

I hope you enjoy the power this gives you.
Please let me know by email to crm _at_ mjvn.net if you use or try this tool.

Download

SuiteCRM MailChimp Sync

Oct 19
2018

I have just created a SuiteCRM (or SugarCRM) module that can export data from any module(s) to a MailChimp list(s).

It allows multiple records/exports to be set up and runs from the scheduler to do the export and sync.

Please give it a try and send feedback to crm at mjvn.net.  See the full README below.

You can download the module from the SuiteCRM Forum here.

This module can export data from any SuiteCRM (or SugarCRM) module to a MailChimp List.

After installation you need to:
1) Run a quick repair and rebuild to build the extended schedular
2) Create a scheduler to run the MailChimpSync schedular at least once a day
3) Create at least one chimp_export record that defines an export
4) Set the mailchimp API ID either in the individual record(s) or in the key
    $sugar_config['mailchimp_api_id'] in config_override.php to apply to
    all records that do not have an api_key filled in.
    This also allows you have a default api_id in the config and override it
    in individual exports if needed.

For 3) above you need to set the following:
1) The module to export from
2) The first name field of that module (normally first_name)
3) The last name field of that module (normally last_name)
4) 1 or 2 filter fields and values to limit the records exported to MailChimp. (eg. by city)

The scheduler will:
1) Create the MailChimp list(s) specified if it does not exist
2) Export all matching records from the module to the list
3) Keep the status of each record in the chimp_status table
4) Update the status if it changes on MailChimp
5) Delete the record from MailChimp if it no longer matches the filters

Every list created by this module has a merge field CRM_ID and each record has the id from
the module in that merge field.  This is useful for custom links in MailChimp templates.

Please notify me at crm at mjvn.net if you use (or try) this module!

Not unto the swift…

The race is not to the swift or the battle to the strong, nor does food come to the wise or wealth to the brilliant or favor to the learned; but time and chance happen to them all.
— Ecclesiates 9:11 (NIV)