Dashlets and reports with custom SQL query

💬 General Discussion
1

I would need to run reports and show dashlets based on the custom SQL query.

How this can be achieved within the SuiteCRM?
Do you have plans for implementing such feature in your reports module?

Many thanks,
Igor

2

Hi Igor,

AOR is in early development and for future releases we plan to develop charts & dashlets.

Thanks,

Will.

3

Great.
Currently, reports based on the (user) query is something that only ZuckerReports supports. And ZR was the only who was able to show dashlets based on the user query. It’s a pity that ZR is not in active developments anymore.

Many thanks,
Igor

4

Hi Igor,

Can you give an example of what you require exactly i.e. screen shot/link to example.

Thanks,

Will.

5

Scenario/example would be the following:

  1. admin (or reports power user) creates any SQL query for SuiteCRM DB. This query can join built-in modules as well as custom ones. Report design user is responsible to provide full SQL elements (e.g. alias for column names). Such query is saved/configured for simple HTML output. Example query would be like the following:
SELECT
hr_rfq.`name` AS RFQ,
users.`user_name` AS 'Assigned to',
hr_rfq.`rfq_status` AS 'RFQ status',
ifnull(hr_rfq.treat_as, hr_rfq.type_of_order) AS 'Type of order',
DATE_FORMAT(hr_rfq.`request_date`, '%d/%m/%Y') AS 'Request date',
DATE_FORMAT(hr_rfq.`cp_date`, '%d/%m/%Y') AS 'CP date',
hr_req_stat.`name` AS 'Next Step',
DATE_FORMAT(hr_rfq.`next_step_date`, '%d/%m/%Y') AS Deadline,
accounts.`name` AS 'Requested by',
hr_contract.`name` AS FC
FROM
`hr_rfq` hr_rfq LEFT OUTER JOIN `accounts` accounts ON (hr_rfq.`account_id_c` = accounts.`id` and accounts.deleted = 0)
LEFT OUTER JOIN `hr_req_stat` hr_req_stat ON (hr_rfq.`req_proc_status_id_c` = hr_req_stat.`id` and hr_req_stat.deleted = 0)
INNER JOIN `hr_contract_hr_rfq_c` hr_contract_hr_rfq_c ON (hr_rfq.`id` = hr_contract_hr_rfq_c.`hr_rfq_id` and hr_contract_hr_rfq_c.deleted = 0)
LEFT OUTER JOIN `users` users ON (hr_rfq.`assigned_user_id` = users.`id` and users.deleted = 0)
INNER JOIN `hr_contract` hr_contract ON (hr_contract_hr_rfq_c.`hr_contract_id` = hr_contract.`id` and hr_contract.deleted = 0)
WHERE
hr_rfq.`deleted` = 0
and ifnull(hr_rfq.treat_as, hr_rfq.type_of_order) != 'TM'
and hr_rfq.`urgent` in ( 'Low', 'Normal', 'High' )

ORDER BY
hr_rfq.`name`

  1. Report module should allow such Query report type to produce simple HMTL output (in order to embed it in the existing page).

  2. standard users can add that saved query on their Home page as dashlet (or as a web page) which will display the output of the predefined query.

Thanks,
Igor