PostgreSQL Dashboard an overview

PostgreSQL Dashboard is a powerful drag-and-drop utility for creating interactive and dynamic dashboards for PostgreSQL database. PostgreSQL Dashboard fetches data from the PostgreSQL database and creates dashboards without writing SQL queries.

PostgreSQL Database

What is a PostgreSQL dashboard?

PostgreSQL dashboards are pure dynamic and based on D3.js which is the most widely used JavaScript library for producing dynamic and interactive data visualizations in web browsers. Postgres dashboard uses the widely implemented SVG, HTML5, and CSS standards which can be created either static or real-time data coming from a variety of data sources.

Download PostgreSQL Dashboard

Create High Quality professional dashboards with PostreSQL database

PostgreSQL dashboard allows a non-technical user with the ability to connect with PostgreSQL database using PDO, the most common and widely used database connection to fetch data in open source platform such as Windows, Linux, and Ubuntu or any other platforms support Apache, Nginx, etc., and summarizes all the metrics data fetches from PostgreSQL database. Postgres dashboard uses advance D3.js framework which gives you instant visibility of dynamically maintain information about the connected component in a graphic layout.

PostgreSQL dashboard enables to customize dashboard layout with the most powerful drag-and-drop features to place each chart exactly where you want for optimal representation. This drag-and-drop layout is fully responsive and compatible with PC, mobile phone and tablets using the most powerful front-end open sources dashboard framework such as D3.js and Bootstrap framework.

PostgreSQL Dashboard Example

Why PostgreSQL Dashboard is important?

PostgreSQL Dashboard has the most powerful SQL query generator, it is the quickest and has a strong reputation of fatching data from PostgreSQL and generate SQL query for your data visualization.

Better Decision Making
Gain Competitive Advantage
Enhance Collaboration
Spotting potential problems
Merge with data from various data source.
Live SQL against database
No need for Data-warehouse or ETL
Leverage the speed and stability of your powerful database.
Auto geneated PHP source code for your dashboard.

Building attractive charts with Postgres database

PostgreSQL dashboards supports multiple charts in a single dashboard layout. Dashboard Postgres comes in a variety of chart types including 3D, line, bar, pie, donuts, bubble, stack, area, heatmap, histogram, sankey diagrams, sunburst and choropleth map. You can customize how chart is grouped, summarized, and displayed for each layout very easy. All charts built in PostgreSQL dashboard are in Scalable Vector Graphics (SVG) format, which is scalable, minimal file size and affects your site’s download speed. The user can also download these dynamic charts in PNG image format.

How to create PostgreSQL dashboard?

This how-to will show you how to connect with PostgreSQL database, fetch data and create dashboard using PostgreSQL dashboards step-by-step.

Postgresql data visualization reporting tool for Postgres

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. PostgreSQL database is designed to handle a huge range of workloads, from single machines to data warehouses or Web services with many concurrent users. The PostgreSQL database runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, macOS, Solaris, Tru64), and Windows.

Tip: PostgreSQL is also known as Postgres

Prerequisites

PosgreSQL Dashboard can be installed any platforms like Windows, Linux and Ubuntu or any other platforms support Apache, Nginx etc.

Requirements

PHP Version 7.2 or later
Apache 2 or later
PostgreSQL database
PDO and pdo_pgsql
Windows 7 or later /Linux 3 or later
Firefox 52, Chrome 57, IE
PostgreSQL Dashboard is available for downloading

Installation

There is a Free version of PostgreSQL dashboard available for trail bases,

You can download PostgreSQL dashboard code fromDownload PostgreSQL Dashboard
Place the files in a directory on the web server. e.g. …/www/yoursite/dashbboardbuilder-v3-FREE/
Unzip the file dashboard.zip using Extract Here option to the root folder of “dashboardbuilder”

Steps to build PostgreSQL dashboard

STEP-1: Connect with PostgreSQL Database

Run the web folder in your browser. e.g. http://localhost/dashboardbuilder following welcome screen will appear.

Following screen will appear.In order to connect with PostgreSQL and fetch data. Now, Select Database from data source tab. Select PosgreSQL from the drop down list of Database, enter your server host name and port number followed by “:” as shown in the image below. Enter your "database-name" in the DB name field and click Save changes &nbsp button.

PostgreSQL Dashboard - Select your Database

Tip: If your database is not in the public schema, then you can use your schema name in this manner 127.0.0.1:5432/Schema in the host field.

When you click the save changes, a green tick mark with Database will appear that shows that your database is successfully connected.

PostgreSQL Dashboard - Preference screen

Tip: To retrive data from PostgreSQL database, PDO driver ‘pdo_pgsql’ must be installed. For more details please have a look at our Documentation

STEP-2: Chart settings

After successfully connect to your PostgreSQL database, we will retrieve data from your database in our next step.
Select the gear icon.

PostgreSQL Dashboard - Database Connection

A pop-up screen will appear and a list of the tables from your PostgreSQL database will appear on the table list as shown in the picture below.

PostgreSQL dashboards have a powerful Query Builder, a smart SQL statement builder tool for quick and easy writing of complex SQL queries without manual code writing which is designed to simplify the retrieving of data from the database.

When you click on the table, a default SQL statement will appear on the SQL statement box, which you may modify as per your need.
You can add up to 10 SQL statements simultaneously to retrieve data from different tables.

STEP-3: pgSQL statements

In our example, we will be creating a simple PHP application for our sales management. This PHP dashboard will contain a chart where users can find the sales stats and maintain the data about the products.
Now, enter the pgSQL statement in text box in a following manner

SELECT strftime('%Y-%m',o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o where o.orderid = d.orderid group by strftime('%Y-%m',o.orderdate) limit 50

Now, we want to execute our query and fetch the data from the tables. Click the Run Query button

Query Result will appear. Now, we have to assign x-axis data to the x-axis column and y-axis data to the y-axis column. Select your x-axis data from the drop down list of X and y-axis data from the drop down list of Y .
Click Save Changes

Charts will be appeared on the screen as shown below.

STEP-4: Adjust the position

You may adjust the size and the position of the Chart Panel by dragging its corner. The size and position are fully responsive according to the Bootstrap framework

STEP-5: Generate PHP and Postgresql dashboard source code

So far we have successfully build our dashboard. In this step we are going to generate PHP dashboard source code we have just created. Click the Generate button to auto-generate PostgreSQL dashboard source code for our PHP application

PHP Dashboard - Chart Preference

PostgreSQL dashboard source code for the chart will automatically generate, you may copy past this script of dashboard to your PHP application.

PHP Source Code for PHP and PostgreSQL dashboard

<?php
/**
 * DashboardBuilder
 *
 * @author Diginix Technologies www.diginixtech.com
 * Support  - http://www.dashboardbuilder.net
 * @copyright (C) 2018 Dashboardbuilder.net
 * @version 2.1.7
 * @license: license.txt
 */

include("inc/dashboard_dist.php");  // copy this file to inc folder 


// for chart #1
$data = new dashboardbuilder(); 
$data->type =  "line";

$data->source =  "Database"; 
$data->rdbms =  "pgsql"; 
$data->servername =  "your-servername";
$data->username =  "userid";
$data->password =  "password";
$data->dbname =  "your-db-name";
$data->xaxisSQL[0]=  "SELECT strftime(^%Y-%m^,o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o  where o.orderid = d.orderid group by strftime(^%Y-%m^,o.orderdate) limit 50";
$data->xaxisCol[0]=  "xaxis";
$data->yaxisSQL[0]=  "SELECT strftime(^%Y-%m^,o.shippeddate) as xaxis, sum(d.quantity) as yaxis from `order details` d, orders o  where o.orderid = d.orderid group by strftime(^%Y-%m^,o.orderdate) limit 50";
$data->yaxisCol[0]=  "yaxis";
$data->name = "linechart";
$data->title = "Line Chart";
$data->orientation = "";
$data->xaxistitle = "x-axis title";
$data->yaxistitle = "y-axis title";
$result[0] = $data->result();

?>

<!DOCTYPE html>
<html> 
<head> 
	<script src="assets/js/dashboard.min.js"></script> <!-- copy this file to assets/js folder --> 
	<!--<link rel="stylesheet" href="assets/css/bootstrap.min.css"> Bootstrap CSS file, change the path accordingly --> 


</head>
<body> 

<div class="container">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading"></div>
    <div class="panel-body">
        <?php echo $result[0];?>
    </div>
</div>
</div>
</div>
</body>

The dash postgresql project also offers free and paid plans. The code for Dashboard Builder can be found on GitHub under this license

Conclusion

Now we have a PostgreSQL dashboard which can be used with any web application that was customized to fit the requirements of the web design. The project also offers free and paid plans. You can also download Free Dashboard PostgreSQL Dashboard version under this license.

Best of all, with our Professional and Premium licenses include 100% source code for you to enhance the functionality or incorporate the Postgres Dashboard into your web application, PHP software solutions/products, analytics/data science/data warehouse enterprise systems, or Joomla, WordPress or Drupal sites.

All editions of our PostgreSQL dashboard series are still fully functional, for sale, and supported and provide increasing levels of support to address the queries of users and developers.

Postgres dashboard can also be implemented using an online API where you don’t have to mix with a PHP script or PHP source code. The online API uses universal HTML code, which can be used on any platform. The data will be added or retrieved from the PostgreSQL database using the API. You may find more information about the Online Dashboard.