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

Leave a Reply

You must be logged in to post a comment.

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)