Powerful Reports (Using any SQL)

Show and Tell
1

Hi everybody,

I’m back to sharing tools I create for myself with the community.

I could not find a reporting module that was powerful enough for advanced SQL users that want to use any SQL to build reports, so I have started building one. If I may say so myself I think it is pretty powerful and yet very easy to use.

It also allows admins to link reports directly to modules or even records inside the CRM that make it super friendly for users that need to use the reports. It also uses caching to make pagination and exporting more lightweight and can export as PDF, CSV or HTML.

If you can write a SQL query to fetch the data you want, you can now add it as a report inside your CRM using this module.

I am attaching the full README file FYI. I would really appreciate it if you would give it a spin and let me have some feedback. You can download it from my SuiteCRM Tools page.

2

Thanks for that, I am sure it will be very useful for many people.

I recently made a nice any-SQL Google Charts Dashlet, starting from a tip from someone else here in the Forums.

https://suitecrm.com/suitecrm/forum/developer-help/20227-how-to-add-other-charts-in-report-charts-for-ex-line-chart-bubble-chart-gauge-chart-funnel-chart-etc/

I don’t have any UI to tweak it, though, it’s just PHP code.

Google Charts have a LOT of interesting possibilities for us to explore…

3

Hi,

Thanks for sharing. I gave your module a try and install it on Sutiecrm 7.10.9. When page loads after installation we get this error:

Strict Standards: Declaration of adrep_report::create_new_list_query() should be compatible with SugarBean::create_new_list_query($order_by, $where, $filter = Array, $params = Array, $show_deleted = 0, $join_type = ‘’, $return_array = false, $parentbean = NULL, $singleSelect = false, $ifListForExport = false) in …\modules\adrep_report\adrep_report.php on line 5

Please advice,

Thanks

4

Hi,

What you are seeing is just a warning about strict compliance.

You really shouldn’t have “error_reporting = … ~E_STRICT” in your php.ini file, especially not on your production instance(s).

If you really need E_STRICT you could just edit the function declaration of the create_new_list_query function in modules/adrep_report/adrep_report.php to be

function create_new_list_query($order_by, $where, $filter = Array(), $params = Array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = NULL, $singleSelect = false, $ifListForExport = false)
5

Thanks for the quick response. I’m using on my test environment. I’ll give it a try and let your know.

Regards

6

I disabled error reporting on my PHP configuration file ans was able to install and test your module. Now Don’t know what else is missing because when I try to display information on HTML I get this error:

Warning: file_put_contents(/tmp/f477e065601fdc3f3e671bcb2c91d44d.html): failed to open stream: No such file or directory in …\modules\adrep_report\adrep_report.php on line 613

Warning: require(/tmp/f477e065601fdc3f3e671bcb2c91d44d.html): failed to open stream: No such file or directory in …\modules\adrep_report\DownloadReport.php on line 22

Fatal error: require(): Failed opening required ‘/tmp/f477e065601fdc3f3e671bcb2c91d44d.html’ (include_path=’…;…\include/…;\xampp\php\PEAR’) in …\modules\adrep_report\DownloadReport.php on line 22

Then I tried to download CSV file and it contained a list of error similar to the above.

Please advice,

Thanks

7

It seems that you are running your SugarCRM instance in Windoze.

You can try to change the temporary file path to something other than /tmp in modules/adrep_report/adrep_report.php on line 34, but unless you are using MySQL my modules will probably not work for you.

8

Hi mvnrsa,

I have tested the most recent version of your module and was able to install it on Sutiecrm 7.10.10 with no errors. Now when creating reports if I want to modify a column, it is not saving the changes. For example I created a report and selected columns ID, Name and save it and when running all was fine. Then I tried to modify columns by removing column ID and save it. The problem is that when I run the report again It shows columns I just removed. In this case ID. Not sure if it has something to do with the way the module handles the cache.

Please advice,

Thanks.

9

Hi,

I have also discovered the same bug a few days ago and yes, it has to do with columns being saved/cached and so they are still in the DB if the column is removed from the report.

I am busy doing a small patch and will post an update here and on my website within a couple of days.

10

This version should fix the problem with columns that were removed from the query.

https://mjvn.com/wp-content/uploads/2018/11/AdvancedReports-0.7.0.zip

1 Like
11

Thanks for your quick response. We have downloaded and installed. Will test it and get back to you.

Thanks,

12

First sorry for the delay. We have been testing your module and we noticed you uploaded a new version since our last post.
Here are our findings:

  1. We added a chart and saved. The report didn’t worked because we didn’t created the chart properly. We tried to modify the chart but there is no edit option. In order to restore the report we modify the chart’s table and marked the record as deleted

  2. We modified the chart panel view and were able to see the changes. Later we needed to run a Quick Repair and Rebuild on the system but it never completes. We needed to delete the vardefs files created under the custom folder to be able to complete the repair.

We’ll keep testing and will come back to you with our findings.

Thanks for your work.

13

I cannot believe this actually worked, initially it faced some issue regarding the environment, but after setting the default path, it worked, thanks for posting this, i learned a lot from this.

14

Hi mvnrsa,

We have been using version 0.7.2 of your module and found it works fine on the query and columns. We have found some issues when trying to edit charts as we mentioned on our previous post. Today we found another issue. We are no longer able to modify permissions via the Role management. At this point only users with admin rights can see and use the reports. Normal users won’t have access to the created reports even when access has been granted to their role from the individual reports.

Please advice,

Thanks,

AlxGr

15

Hi,
Thank you for testing this.

I will need a bit more info to be able to replicate and troubleshoot your problem.

Please try and locate the query that display the ListView of the reports from your suitecrm.log file.

Kind Regards,
Marnus

16

Hi Marnus,

I was checking my logs but was not able to get the query.

Questions: Is there any particular reason on why the modulo is no longer shown under role management? Do you have any idea on how we can share reports with specific non-admin users?

Thanks,

AlxPgr

17

Hi, I can see no practical reason for it.

Have you tried repairing the roles on admin?

Please increase the log level on admin to debug and send me the log file or extract via e-mail to suitecrm@mjvn.net.
Tx

18

Just for others to know. I can see the modules under roles management again after repairing the roles on admin.

I’ll keep an eye on this and will send you any find directly to your email.

Thanks for all the good work!!

19

Hi, where am I going to run it to create the reports
Or is suitecrm, I just run the report but I see you gave the example but how can I test it

Thank you

20

You need to create reports in the Advanced Reports module.

Then you can run them from there or link them to menus in other modules for convenience.

1 Like