BI SQL Editor
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.
Key Features
- Custom report creation via SQL queries
- Support for SQL Views and materialized views
- Access control for user groups
- Automated UI Creation
Custom Report Creation
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).
Enhanced Reporting Options
Create new menu options, actions, and graph views for improved data visualization, enabling customization of Cyllo reports by adding or removing fields as necessary.
Materialized View Support
Address performance issues with large datasets by utilizing materialized views, reducing display duration and enhancing overall query performance.
Access Control
Configure permissions for user groups to access and interact with reports, ensuring data security and confidentiality.
Accessing the Module
Navigate to Dashboard / Configuration / SQL Views
Defining your SQL Query
- Add a technical name and set a view order
- Enable check box "is materialized view" to create a materialized view and check "use context field" if you want to use a context column in the field list view
- Enter your SQL request in the provided text field, specifying the data you want to retrieve and manipulate.
Select Access Groups
Choose the group(s) that should have access to the view, ensuring appropriate data security.
Optional: Add a Domain
Optionally, define a domain to filter the data based on specific criteria, such as company IDs in a multi-company context.
Validate SQL Expression
Click on the "Validate SQL Expression" button to verify the correctness of the SQL request.
Field Mapping
- After validating the SQL request, the module analyzes the columns of the view and proposes field mapping.
- For each field, decide whether to create an index and specify its display settings for pivot graphs.
Create SQL Elements
Click on the "Create SQL Elements" button to generate the SQL elements. Note that this step may take some time for materialized views.
Materialized View Settings (if applicable)
If the view is materialized:
- A cron task is created to refresh the view. You can so define the frequency of the refresh.
- Review the size of the view and indexes displayed.
Create UI
Finally, click on "Create UI" to generate a new menu, action, graph view, and search view based on the SQL request.
Access SQL Reports
Navigate to 'Dashboards > SQL Reports' to view the list of available reports. Choose the desired report from the list to access its details.
View Report
Switch between 'Graph' or 'Tree' views as needed to analyze the report data effectively.
GNU Affero General Public License v3.0 This software and associated files (the "Software") may only be used (executed, modified, executed after modifications) if you comply with the terms of the GNU Affero General Public License, version 3.0, available at https://www.gnu.org/licenses/agpl-3.0.html. You may develop software that uses the Software as a library (typically by depending on it, importing it and using its resources), but without copying any source code or material from the Software. You may distribute those software under the terms of the GNU Affero General Public License, version 3.0. It is forbidden to publish, distribute, sublicense, or sell copies of the Software or modified copies of the Software without including the complete text of the GNU Affero General Public License, version 3.0, along with any applicable additional permissions. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
- spreadsheet_dashboard
- sql_request_abstract