Report System in Flask

Reports plays a major role in current business outlook for making critical and non critical decisions. Business people always like to play with reports in terms of analyzing the business flow and status. This inturn becoming a unavoidable component in the mind of 2K entrepreneurs & beyond.

When we are developing an application, the Reports module adds more value to the application, It doesn’t matter on scales of the(small/ large scale) application. Because Data always in demand and charm as well.

We spend a lot of time building the table report. The process includes:

  1. Define filters for each report.
  2. Write SQL query.
  3. Write API for each report.
  4. Set the color for data to make a better look.
  5. Build UI for it.

Implementing these steps in a web application is time-consuming and needs Dolo 650 to overcome it. I implemented Report System which enables you to create reports by writting SQL queries and configure filters by clicking the dropdown in UI. No need to write any code for it. We can merge this code with any other application.

we can make the above steps more modular with a database level configuration.

DB Structure

flask_parameter and flask_report tables are master table, flask_report_parameter table is an association table.

Implementing Step By Step Process

Defining the Parameters(Filter Options):

table: flask_parameter

columns: parameter_label,parameter_name,parameter_display_type, parent_id,parent_id,parameter_sql

I) Define all parameters that we are going to use in our app

It may be a Dropdown, Text, Date, Number types

The data will be stored in the flask_parameter table

Column Description:

parameter_label column stores the label value which can be used to show in the UI.

parameter_name column stores the name that is called from reports and other child parameters

parameter_display_type column stores the type of input for the parameter. If it is Dropdown means display_type will be dropdown where we can select data from the dropdown and the data will come from a database by executing the respective SQL query.

parent_id column is used to configure dependent dropdown specialty. If parent param gets changed then the child will be changed accordingly.

parameter_sql column will have the data if display type is “select”(dropdown type).

Configure the Reports:

table: flask_report

columns: name,sql_query,is_active, description,report_type,category

flask_report table configure like below one.

Column Description:

name column represents the name of the report.

sql_query column represents the SQL query for that report.

UI Screen looks like below one

The above image represents the screen of Add /Edit report.

In SQL text box — Write SQL query.(1)

The right side table shows available filter parameter details that will help users to add those in Reports.(4)

Adding filters to the reports(2,3)

In SQL query ${parameter_name} represents the filters, which automatically replaces with values while running the report. Whatever filters we mention in query with ${} symbol ,those filters should be selected from dropdowns

Configure color code while rendering the data:

In the above screen, we can configure color for data while rendering.

Column Description

Field Name represents column from SQL query.

Condition is used to set the condition that needs to follow javascript syntax.

Value is the default keyword that automatically represents a field that you mentioned in “Field name” box.

In the color field, you can mention the color or color code that you want.

After submitting the Report, Data will be stored in the reports table, and parameters are stored in the flask_report_parameter table.

Report Data in Table

Then it will show the reports in list view:

select the report which you want to run

Ex: Dependent Dropdown Report

In the above screen, the filter data changes dynamically based on selection. If you remembered parent_id in flask_parameter table.“ Select office” is parent dropdown, “Select Deparment” is child dropdown.

Whenever parent data changes then child data also changes according to the configuration.

Json data for configure the dependent dropdown

Options

Export report as EXCEL, CSV and Sorting also available.

All Set! Your report system is ready.

Attached the gif to demonstrate the full workflow.

This is opensource code,Can be easily integrated with any other code.

In project structure CSS,js, templates, API are maintaining as packages. The only thing you need to do is download and place it properly. Then Your report system is ready! Cheers!!!

Source code available on Github Repository along with examples(SQLite DB).

Installation setup is available in GitHub README.

Full-stack developer, DevOps, Python, SQL, Javascript, Flask, Django, Ubuntu, Freelancer. Well good at handling the data. Gmail: ramramesh1374@gmail.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store