DEV Community

Cover image for SQL Report Builder

Posted on

SQL Report Builder

The SQL Report builder stands as an indispensable component within the Wizard's Toolkit low-code development library. This in-depth video delves into the intricacies of crafting dynamic reports using:

  • mySQL
  • PostgreSQL

For developers who are adept at constructing SQL SELECT statements, this tool facilitates the generation of detailed reports with functionalities encompassing sorting, filtering, column alignment, totaling, and exporting. Seamlessly integrated with ChartJS for streamlined chart creation at a mere click.

How it Works

This low-code library is built using PHP, JavaScript, SQL, CSS and HTML5. All are merged into the low-code environment and any SQL developer can access the back office website to enter their SQL query and choose options from drop lists, checkboxes and enter a few fields regarding which columns need alignment or totaling. Checkboxes at the bottom determine whether charts are needed and which types of graphs to display.

Image description

This SQL Report tool stores your SQL query in a SQL table named wtkReports. A Report Viewer is provided to clients which allow them to see the list of reports based on their security level. Clicking on a report displays it. Based on user preferences they may or may not be allowed to export.

Charts and Graphs

Built on ChartJS, custom JavaScript has been written to allow switching from one type of graph to another without additional hits on the SQL database. Graphs include:

  • bar chart
  • line chart
  • area chart
  • pie chart

All the complexity of charting is handled internally. The SQL developer just needs to write the query, decide which (if any) columns should be suppressed from charting, and pick types of graphs to show.

Image description

SQL Reports reduce Deployment Times

Depending on your work environment, adding a new report can be a tedious hassle. The SQL developer writes the SQL query and sends it to the website developer. They integrate it into PHP or some other language. Then it goes to the DevOps person to deploy to staging for testing to make sure it doesn't break any other pages. Then it is finally deployed to production. If the client desires a minor change or enhancement it has to go back to the SQL developer, then the PHP coder, then DevOps. Using a SQL Report Builder streamlines this process. SQL developer writes the query and verifies it works with their SQL tools. Then logs into back office website and pastes the query into the SQL Report Wizard and chooses features needed. Done - no deployment "process" needed.

Top comments (0)