The Report Enhancer add-on goes beyond the existing SuiteCRM reporting module, enabling users to add custom SQL to reports and view the formatted SQL statement.
#5163 - How to use complex SQL
Hi,
I am brand new to this plugin. I just installed and I'm trying to learn how to use it.
I created a simple report and tested some basic sql and the plugin seems to be working.
I have this query;
SELECT COUNT() AS total_records, SUM(CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END) AS total_calls, SUM(CASE WHEN cc.connected_c = 1 THEN 1 ELSE 0 END) AS connected_calls, SUM(CASE WHEN cc.connected_c = 1 THEN 1 ELSE 0 END) / COUNT() * 100 AS percentage_connected, SUM(CASE WHEN cc.converted_c = 1 THEN 1 ELSE 0 END) AS converted_calls, SUM(CASE WHEN cc.converted_c = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS percentage_converted FROM calls c LEFT JOIN calls_cstm cc ON c.id = cc.id_c WHERE CONVERT_TZ(c.date_entered, '+00:00', '-08:00') >= '2023-01-01' AND CONVERT_TZ(c.date_entered, '+00:00', '-08:00') <= '2023-12-12';
I would love to be able to do this in SuiteCRM. I copied and pasted this into the the "extra sql" and it doesn't appear to do anything. Is a query like this beyond the scope of the plugin? Before purchasing, I saw a detailed screenshot of a complex query so that's what led to me to purchase. I'm usiing SuiteCRM 7.4, can you help me.
10 months ago
Hi Julio,
The plugin can only add sections to the "Where" clause. We have not interrupted the select statement at all, so the "extra SQL" is only the additional Where statements that might be needed that cannot be added via the interface. looking at your SQL above you are trying to amend the Rows returned as well (the select part of the SQL statement), which this plugin does not currently support unfortunately.
The second screenshot of the "complex query" is the SQL Analyser function that formats the resultant SQL in a legible format so you can find/see what will be run and work out table aliases etc to build your additional where statements.
Kind regards
Mark