Powerful Reports (Using any SQL)

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.

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

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

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

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

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!!

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

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

I installed it but I get errors and it does not let me run it to create a report.
I really need to create a nice reports with graphs but no luck. I saw the code in how to create the report but where do I modify?

Thank you for your response

I will need a lot more details to be able to help you.
Use the e-mail address in the README file and send me details of the errors you get and the version of SuiteCRM you are running.

1 Like

Hi,
I sent you an email yesterday.
Thank you

Does this Allow us to connect to another DB for calling a Stored Procedure? our Sage Server is different which has SQL DB and we need to connect and run SP on that server and show a matching criteria for the resulted records.

Hi,

You can query tables in a different database on the same database server using the database.table notation in queries, but unfortunately you can not connect to a different database server because the modules use the existing connection in the CRM.

It should be easy to make a second database connection, but for that you will have to modify the code.

Hello, I am trying your tool, it seems to me an excellent improvement
but I can’t generate the pdf from a simple query like the following

SELECT an.name, an.description
FROM accounts an, accounts_cstm ac
WHERE an.id = ac.id_c
AND an.deleted = 0
AND an.description != ''
LIMIT 10

the report appears blank

Does that query show anything if you try it directly in phpMyAdmin or mysql console?

try this one instead:

SELECT an.name, an.description
FROM accounts an
LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c 
WHERE an.deleted = 0
AND an.description != ''
LIMIT 10

That query doesn’t work like this, but it does like this:

SELECT *
FROM accounts an
LEFT JOIN accounts_cstm ac ON an.id = ac.id_c 
WHERE an.deleted = 0
AND an.description != ''

in MySQL Workbench
but in the report this comes out empty

That looks pretty similar to mine, except for minor details… Just curious, what exactly was the error with my query?

Is your query returning any rows?

Any way, I don’t know how this is handled in the plugin, maybe @mvnrsa can help?

For the tables’s alias, I think
yes, they are minor details
I hope he reply
Thank you

the link is broken, could you upload it again? Thank you

You’re right, the site is down due to some database problems. I expect they’re temporary and @mvnrsa will fix them soon…

Meanwhile, you can browse the page in the wayback machine and download the package through a direct link.