This module extends Cyllo's reporting functionality by allowing users, primarily administrators, to create custom reports through SQL queries.
BI SQL Editor elevates Cyllo's reporting capabilities by enabling the creation of custom reports via user-written SQL queries. Admin users can craft their desired SQL query, map the retrieved data fields to corresponding Cyllo fields, and ultimately generate a new menu item, action, and graph view for the report.
Technically, the module creates an SQL View (or a materialized view for faster performance with larger datasets, which requires a cron job for data refreshes). By default, the "SQL Request / User" group can access all views, but you can specify additional groups for granular access control.
This module is designed for individuals with technical expertise within a company and for Cyllo integrators, as it requires a strong understanding of SQL syntax and familiarity with Cyllo models. Due to its technical complexity, it is not recommended for users lacking these skills, especially in a production environment.
Generate tailored reports via SQL queries to meet specific analysis needs, including technical requests not supported by the Cyllo framework (e.g., UNION with multiple SELECT statements).
Create new menu options, actions, and graph views for improved data visualization, enabling customization of Cyllo reports by adding or removing fields as necessary.
Address performance issues with large datasets by utilizing materialized views, reducing display duration and enhancing overall query performance.
Configure permissions for user groups to access and interact with reports, ensuring data security and confidentiality.
Navigate to Dashboard / Configuration / SQL Views
Choose the group(s) that should have access to the view, ensuring appropriate data security.
Optionally, define a domain to filter the data based on specific criteria, such as company IDs in a multi-company context.
Click on the "Validate SQL Expression" button to verify the correctness of the SQL request.
Click on the "Create SQL Elements" button to generate the SQL elements. Note that this step may take some time for materialized views.
If the view is materialized:
Finally, click on "Create UI" to generate a new menu, action, graph view, and search view based on the SQL request.
Navigate to 'Dashboards > SQL Reports' to view the list of available reports. Choose the desired report from the list to access its details.
Switch between 'Graph' or 'Tree' views as needed to analyze the report data effectively.