PostgreSQL Sales Analytics: Interactive Pivot Table Dashboard

Binding and Perform CRUD Operations with Interactive Sales Analytics Using PostgreSQL Data

PostgreSQL Pivot Table Dashboard is a full-stack business intelligence application that seamlessly integrates a React + TypeScript frontend with an ASP.NET Core backend connected to PostgreSQL database.

🔄 How It Works

The application uses Syncfusion DataManager to handle real-time CRUD operations (Create, Read, Update, Delete) on sales data through an interactive pivot table interface:

• React Client (Frontend): Displays an interactive Syncfusion pivot table component that allows users to visualize, analyze, and manipulate sales data without writing SQL
• DataManager: The DataManager component in React communicates with backend API endpoints, automatically sending HTTP requests for fetch, insert, update, and delete operations
• ASP.NET Core Backend: Provides REST API endpoints (/api/sales) that handle all CRUD operations via the SalesController
• PostgreSQL Database: Stores all sales transaction data with proper indexing for optimal query performance using Npgsql provider

The complete workflow: User interacts with pivot table → DataManager sends HTTP request → Backend processes via SalesController → PostgreSQL executes query → Response updates UI instantly.

---

📋 Table of Contents

• Quick Overview
• Key Features & Capabilities
• Technology Stack
• System Architecture
• Installation & Setup
• Configuration
• Getting Started
• Project Structure
• Troubleshooting
• Contributing
• License & support

---

🎯 Quick Overview

This application provides an intuitive interface for real-time sales data analysis and manipulation. Instead of complex SQL queries, users interact with an interactive pivot table to:

• Visualize Data: Organize sales data by country, product category, sales personnel, and time periods
• Perform CRUD Operations: Add new records, modify existing data, and delete entries directly through the pivot table interface
• Drill-Through Analysis: Click cells to view detailed records and edit them in-line
• Customize Views: Drag-and-drop fields to reorganize the pivot table layout
• Real-time Sync: All changes are immediately reflected across the application using DataManager

Why Choose This Stack?

• Type-Safe Frontend: TypeScript ensures fewer bugs in the React application
• Robust Backend: ASP.NET Core 10.0 provides enterprise-grade API performance
• Reliable Database: PostgreSQL offers ACID compliance and advanced querying capabilities
• Professional UI: Syncfusion components deliver production-ready pivot tables
• Seamless Integration: DataManager bridges frontend and backend with automatic HTTP handling

---

✨ Key Features

Core Capabilities

• Interactive Pivot Tables: Organize and summarize sales data by multiple dimensions with drag-and-drop field configuration
• Complete CRUD Operations: Create, read, update, and delete sales records directly from the pivot table editing UI.
• DataManager Integration: Automatic HTTP communication with backend for seamless data synchronization
• Drill-Through Analysis: Click detailed cells to edit individual records with real-time validation
• Sales Dimensions: Analyze data by country, region, product category, sales person, and time periods
• Responsive Design: Works seamlessly on desktop and tablet devices
• Real-time Updates: All changes reflect immediately across the application
• Date/Time Handling: Automatic formatting for temporal fields with calendar picker support

---

🛠️ Technology Stack

Frontend

Technology	Version	Purpose
React	19.2.5	UI framework and component library
TypeScript	~6.0.2	Type-safe JavaScript development
Vite	8.0.10	Lightning-fast build tool
Syncfusion EJ2	33.2.3	Professional pivot table component
React DOM	19.2.5	React rendering engine

Backend

Technology	Version	Purpose
.NET	10.0	Framework for web APIs
ASP.NET Core	10.0	Web application framework
Npgsql.EF Core	9.0.4	PostgreSQL data access
Syncfusion EJ2	32.2.3	Data management utilities
Swashbuckle	10.1.2	OpenAPI/Swagger documentation

Database & DevOps

Technology	Purpose
PostgreSQL	Primary relational database
Npgsql	.NET PostgreSQL provider

Development Tools

Tool	Purpose
ESLint	Code quality and style enforcement
TypeScript Compiler	Type checking and transpilation
Node.js	JavaScript runtime for frontend
.NET CLI	.NET project management

---

🏗️ System Architecture

Architecture Overview

┌──────────────────────────────────────────────┐
│   React Frontend (TypeScript/Vite)           │
│  ┌──────────────────────────────────────┐   │
│  │  PivotViewComponent + DataManager    │   │
│  │  (Syncfusion)                        │   │
│  └──────────────────────────────────────┘   │
└──────────────────────────────────────────────┘
              ↓
         HTTP/HTTPS REST API
              ↓
┌──────────────────────────────────────────────┐
│   ASP.NET Core Backend (.NET 10.0)          │
│  ┌──────────────────────────────────────┐   │
│  │  SalesController                     │   │
│  │  /api/sales endpoints (CRUD)         │   │
│  └──────────────────────────────────────┘   │
└──────────────────────────────────────────────┘
              ↓
         Npgsql Provider
              ↓
┌──────────────────────────────────────────────┐
│   PostgreSQL Database (salesdb)              │
│  ┌──────────────────────────────────────┐   │
│  │  salesdata table (orders, details)   │   │
│  └──────────────────────────────────────┘   │
└──────────────────────────────────────────────┘

Data Flow with DataManager

1. User Interaction: User manipulates pivot table in React (add, edit, delete, reorganize)
2. DataManager Request: DataManager automatically detects changes and sends HTTP request to backend
3. Backend Processing: SalesController receives request and routes to appropriate CRUD method
4. Database Query: Npgsql executes SQL against PostgreSQL using parameterized queries
5. Response: Backend returns JSON response with operation result
6. UI Synchronization: DataManager updates pivot table with fresh data, user sees changes instantly

Key Integration Points

Component	Role	Purpose
DataManager	Frontend Bridge	Manages HTTP communication, caching, and data synchronization
SalesController	Backend API	Handles POST/GET requests and routes to CRUD operations
Npgsql	Data Provider	Executes queries against PostgreSQL with connection pooling
Syncfusion PivotView	UI Component	Renders interactive pivot table with drill-through editing

---

📦 Installation & Setup

Prerequisites

• Backend: .NET 10.0 SDK, PostgreSQL 12+
• Frontend: Node.js 18+, npm/yarn
• System: 4GB RAM, 500MB storage, Ports 5432, 7086, 5173 available

Database Setup

1. Create Database

   psql -U postgres
   CREATE DATABASE salesdb;

2. Create Table - Execute the SQL schema to create salesdata table with columns: orderid, customername, region, country, productcategory, productname, orderdate, quantity, unitprice, totalamount, salesperson

3. (Optional) Seed Sample Data - Insert test records into the table for development

Backend Installation

Step 1: Navigate to Backend Directory

cd PivotTable_PostgreSQL.Server

Step 2: Restore NuGet Dependencies

dotnet restore

Step 3: Update Connection String

Edit appsettings.json with your PostgreSQL credentials:

{
  "ConnectionStrings": {
    "SalesDb": "Host=localhost;Port=5432;Database=salesdb;Username=postgres;Password=YourPassword;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

⚠️ Security Warning: Never commit appsettings.json with actual credentials to version control. Use environment variables or user secrets in production.

Step 4: Build the Backend

dotnet build

Step 5: Run the Backend

# Development mode with file watching
dotnet watch run

# Or standard run
dotnet run

Backend will start at: https://localhost:7086

Frontend Installation

Step 1: Navigate to Frontend Directory

cd pivottable_postgresql.client

Step 2: Install npm Dependencies

npm install

Step 3: Update API Endpoint (if needed)

Edit src/App.tsx to match your backend URL:

let oData: DataManager = new DataManager({
  url: 'https://localhost:7086/api/Sales',
  insertUrl: 'https://localhost:7086/api/Sales/Insert',
  updateUrl: 'https://localhost:7086/api/Sales/Update',
  removeUrl: 'https://localhost:7086/api/Sales/Remove',
  adaptor: new UrlAdaptor
});

Step 4: Start Development Server

npm run dev

Frontend will be available at: http://localhost:5173

Step 5: Build for Production

npm run build

---

⚙️ Configuration

Backend Configuration (appsettings.json)

{
  "ConnectionStrings": {
    "SalesDb": "Host=localhost;Port=5432;Database=salesdb;Username=postgres;Password=YOUR_PASSWORD;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore": "Debug"
    }
  },
  "AllowedHosts": "*",
  "Kestrel": {
    "Endpoints": {
      "Https": {
        "Url": "https://localhost:7086"
      },
      "Http": {
        "Url": "http://localhost:5000"
      }
    }
  }
}

Environment Variables (Backend)

Create .env file in backend root:

ASPNETCORE_ENVIRONMENT=Development
ConnectionString__SalesDb=Host=localhost;Port=5432;Database=salesdb;Username=postgres;Password=YOUR_PASSWORD;

Frontend Configuration (vite.config.ts)

Current configuration supports React development:

import { defineConfig } from 'vite'
import react from '@vitejs/plugin-react'

export default defineConfig({
  plugins: [react()],
  server: {
    port: 5173,
    proxy: {
      '/api': {
        target: 'https://localhost:7086',
        changeOrigin: true,
        rewrite: (path) => path.replace(/^\/api/, '/api')
      }
    }
  }
})

Frontend Environment Variables

Create .env file in frontend root:

VITE_API_URL=https://localhost:7086
VITE_API_TIMEOUT=30000

---

🚀 Getting Started

Quick Start (5 Minutes)

Prerequisites Met:

✅ PostgreSQL running
✅ Database and table created
✅ Backend connection string configured
✅ Node.js installed

Steps:

Terminal 1 - Start Backend:

cd PivotTable_PostgreSQL.Server
dotnet run

Terminal 2 - Start Frontend:

cd pivottable_postgresql.client
npm install  # (First time only)
npm run dev

Browser: Open http://localhost:5173

First Success Checkpoint

You should see:

1. ✅ Pivot table interface loads
2. ✅ Sales data displays in the table
3. ✅ Field list panel appears on the right
4. ✅ You can drag fields to reorganize the pivot table
5. ✅ Click a cell to drill into detail records

Next Steps for Learning

1. Understand the Data Model: Review SalesData Model
2. Explore CRUD Operations: Try adding, editing, and deleting records
3. Configure Pivot Table: Drag fields to customize the view
4. Study the Code: Examine SalesController.cs and App.tsx
5. Add Features: Implement filtering, sorting, and export capabilities

---

📁 Project Structure

Directory Tree

PostGreSQL-Sales/
├── README.md                              # This file
├── microsoft-sql-server-datamanager.md   # Reference documentation
├── microsoft-sql-server-grid.md          # Reference documentation
├── postgresql-pivot-table-guide.md       # Database guide
│
├── pivottable_postgresql.client/         # React Frontend
│   ├── package.json                      # npm dependencies & scripts
│   ├── tsconfig.json                     # TypeScript configuration
│   ├── tsconfig.app.json                 # App-specific TS config
│   ├── tsconfig.node.json                # Node-specific TS config
│   ├── vite.config.ts                    # Vite build configuration
│   ├── eslint.config.js                  # ESLint rules
│   ├── index.html                        # HTML entry point
│   │
│   └── src/
│       ├── main.tsx                      # React root
│       ├── App.tsx                       # Main component
│       ├── App.css                       # Component styles
│       ├── index.css                     # Global styles
│       └── assets/                       # Static assets
│
└── PivotTable_PostgreSQL.Server/         # .NET Core Backend
    ├── Program.cs                        # Application entry point
    ├── PivotTable_PostgreSQL.Server.csproj # Project file
    ├── PivotTable_PostgreSQL.Server.sln  # Solution file
    ├── PivotTable_PostgreSQL.Server.http # HTTP test file
    ├── appsettings.json                  # Configuration
    ├── appsettings.Development.json      # Dev configuration
    │
    ├── Controllers/
    │   └── SalesController.cs            # API endpoints for CRUD
    │
    ├── Properties/
    │   └── launchSettings.json           # Launch configuration
    │
    ├── bin/                              # Compiled output
    │   └── Debug/
    │       └── net10.0/
    │
    └── obj/                              # Build artifacts
        └── Debug/
            └── net10.0/

Key Files & Their Purposes

Frontend Key Files

File	Purpose
src/App.tsx	Main React component with PivotView and DataManager configuration
src/main.tsx	React application entry point and DOM mounting
vite.config.ts	Build tool configuration for development and production
tsconfig.json	TypeScript compiler settings
package.json	npm dependencies and build scripts

Backend Key Files

File	Purpose
Program.cs	Application startup, services configuration, middleware setup
Controllers/SalesController.cs	REST API endpoints handling CRUD operations
appsettings.json	Database connection string and configuration
PivotTable_PostgreSQL.Server.csproj	Project dependencies and build settings

---

🔧 Troubleshooting

Common Issues & Solutions

Frontend Issues

❌ "Cannot connect to server" error

Error: Failed to fetch from https://localhost:7086/api/Sales

✅ Solution:

1. Verify backend is running: dotnet run in backend directory
2. Check port 7086 is not blocked by firewall
3. Verify CORS is enabled in Program.cs
4. Check browser console for exact error message

❌ "TypeScript compilation error"

error TS2307: Cannot find module '@syncfusion/ej2-react-pivotview'

✅ Solution:

1. Run npm install to install dependencies
2. Delete node_modules and package-lock.json
3. Run npm install again
4. Clear browser cache (Ctrl+Shift+Delete)

❌ CORS blocked request

Access to XMLHttpRequest blocked by CORS policy

✅ Solution:

1. Verify CORS is configured in backend Program.cs:

app.UseCors("AllowAll");

2. Restart backend
3. Check frontend URL matches configured origin

Backend Issues

❌ "Connection refused" to PostgreSQL

Npgsql.NpgsqlException: could not connect to server

✅ Solution:

1. Verify PostgreSQL is running: psql -U postgres
2. Check connection string in appsettings.json
3. Verify database exists: psql -l
4. Check PostgreSQL is listening on port 5432
5. Test manually: psql -U postgres -d salesdb

❌ "Column does not exist" error

ERROR: column "salesperson" does not exist

✅ Solution:

1. Verify table schema matches SalesData model
2. Check PostgreSQL table structure:

\d salesdata

3. Recreate table if needed (see Database Setup section)

❌ Port 7086 already in use

System.IO.IOException: Failed to bind to address

✅ Solution:

# Find process using port (Windows PowerShell)
netstat -ano | findstr :7086

# Kill process (replace PID)
taskkill /PID <PID> /F

# Or use different port in launchSettings.json

Database Issues

❌ "Database does not exist"

FATAL: database "salesdb" does not exist

✅ Solution:

1. Create database:

CREATE DATABASE salesdb;

2. Create tables (see Database Setup section)

❌ "Permission denied" error

FATAL: role "postgres" does not have CONNECT privilege

✅ Solution:

1. Check PostgreSQL user permissions
2. Reset password:

psql -U postgres
ALTER USER postgres WITH PASSWORD 'newpassword';

Performance Issues

❌ Pivot table loads slowly

✅ Solutions:

1. Create database indexes:

CREATE INDEX idx_orderdate ON salesdata(orderdate);
CREATE INDEX idx_country ON salesdata(country);
CREATE INDEX idx_productcategory ON salesdata(productcategory);

2. Reduce number of rows in view
3. Use pagination instead of loading all data

---

🤝 Contributing

Contribution Guidelines

We welcome contributions! Here's how to get started:

1. Fork the repository (local development)
2. Create a feature branch:

   git checkout -b feature/your-feature-name

3. Make your changes with clear, descriptive commits
4. Test thoroughly before submitting
5. Document your changes in code comments

Code Style & Standards

• Follow the existing code style
• Write meaningful commit messages
• Add comments for complex logic
• Test all CRUD operations
• Ensure no console errors

Pull Request Process

1. Update documentation if needed
2. Test on both frontend and backend
3. Ensure all tests pass
4. Provide clear PR description

---

📜 License & support

This is a commercial product subject to the Syncfusion End User License Agreement (EULA).

Free Community License is available for qualifying users/organizations: ś

• Annual gross revenue < $1 million USD
• 5 or fewer total developers
• 10 or fewer total employees

The community license allows free use in both internal and commercial applications under these conditions.
No registration or approval is required — just comply with the terms.

Paid Licenses are required for:

• Larger organizations
• Teams exceeding the community license limits
• Priority support, custom patches, or on-premise deployment options

Purchase options and pricing: https://www.syncfusion.com/sales/products
30-day free trial (full features, no credit card required): https://www.syncfusion.com/downloads/essential-js2
Community License details & FAQ: https://www.syncfusion.com/products/communitylicense
Full EULA: https://www.syncfusion.com/eula/es/

© 2026 Syncfusion, Inc. All Rights Reserved.