ASP.NET Core  

Building Interactive SQL Server Reports in ASP.NET Core

In modern web applications, reporting is no longer just about displaying static data. Users expect interactive, dynamic, and drillable reports directly in their browser. ASP.NET Core, combined with SQL Server, provides a robust platform for building such reporting solutions.

This article explores how to design, implement, and optimize interactive reports in ASP.NET Core applications, with a focus on real-world best practices and scalability.

1. Understanding Interactive Reports

Interactive reports allow users to:

  • Filter and sort data dynamically

  • Drill down or expand hierarchical data

  • Export reports to PDF, Excel, or CSV

  • Visualize data using charts or tables

Unlike static reports, interactive reports require careful backend design, efficient queries, and responsive frontend interfaces.

2. Architecture Overview

A typical architecture for interactive reporting:

Browser (Angular / Razor Pages)
       |
       | HTTP/REST
       v
ASP.NET Core Web API
       |
       | SQL Queries / Stored Procedures
       v
SQL Server Database

  • Frontend – renders reports, provides filters, sorting, and drill-downs

  • Backend (ASP.NET Core) – handles queries, data aggregation, security, and pagination

  • SQL Server – stores data and provides optimized querying mechanisms

3. Designing Reports

3.1 Identify User Requirements

  • Which data points are critical?

  • Do users need filtering, grouping, or drill-downs?

  • Will reports handle large datasets?

3.2 Determine Data Sources

  • Single table or multiple joins

  • Use views or stored procedures for complex aggregations

Best practice: Avoid querying multiple tables directly from frontend; centralize logic in stored procedures or backend services.

3.3 Choose Report Layout

  • Tabular – simple lists with filters

  • Matrix / Pivot – aggregate data by row and column

  • Charts – bar, line, pie for visual insights

4. Implementing Backend in ASP.NET Core

4.1 Database Access

Use Entity Framework Core or Dapper for efficient querying.

Using Dapper for performance:

using (var connection = new SqlConnection(_connectionString))
{
    var reportData = await connection.QueryAsync<ReportItem>(
        "EXEC GetSalesReport @StartDate, @EndDate",
        new { StartDate = startDate, EndDate = endDate });
    return Ok(reportData);
}

Notes

  • Use parameterized queries to prevent SQL injection

  • Stored procedures improve query optimization and maintainability

4.2 Pagination

Large reports should implement server-side pagination:

var pagedData = await connection.QueryAsync<ReportItem>(
    "EXEC GetPagedSalesReport @PageNumber, @PageSize",
    new { PageNumber = page, PageSize = pageSize });

  • Reduces load on the client

  • Improves API response times

4.3 Filtering and Sorting

Dynamic queries can be built safely using parameters:

var sql = "SELECT * FROM Sales WHERE 1=1";
if (!string.IsNullOrEmpty(region))
    sql += " AND Region = @Region";
if (!string.IsNullOrEmpty(category))
    sql += " AND Category = @Category";

var data = await connection.QueryAsync<ReportItem>(sql, new { Region = region, Category = category });

Avoid string concatenation for user input – always use parameterized queries.

4.4 Aggregation and Drill-down

  • Use SQL GROUP BY, ROLLUP, or CUBE for summaries

  • Drill-down queries fetch detailed rows for selected summary items

Example

-- Summary
SELECT Region, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region

-- Drill-down
SELECT *
FROM Sales
WHERE Region = @SelectedRegion

5. Frontend Implementation

5.1 Choosing Framework

  • Razor Pages for server-rendered reports

  • Angular / React for dynamic, interactive dashboards

5.2 Data Table Libraries

  • Angular Material Table – supports sorting, pagination, filtering

  • DevExpress or Syncfusion Grids – advanced features like export and grouping

Example with Angular Material Table

<table mat-table [dataSource]="dataSource" class="mat-elevation-z8">
  <ng-container matColumnDef="region">
    <th mat-header-cell *matHeaderCellDef> Region </th>
    <td mat-cell *matCellDef="let element"> {{element.region}} </td>
  </ng-container>

  <ng-container matColumnDef="totalSales">
    <th mat-header-cell *matHeaderCellDef> Total Sales </th>
    <td mat-cell *matCellDef="let element"> {{element.totalSales | currency}} </td>
  </ng-container>

  <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
  <tr mat-row *matRowDef="let row; columns: displayedColumns;" (click)="drillDown(row)"></tr>
</table>

5.3 Charts and Visualization

  • Use Chart.js, Highcharts, or D3.js for interactive charts

  • Bind chart data to API responses dynamically

Example with Chart.js

this.http.get<SalesSummary[]>('/api/reports/sales').subscribe(data => {
  this.salesChart.data.labels = data.map(d => d.region);
  this.salesChart.data.datasets[0].data = data.map(d => d.totalSales);
  this.salesChart.update();
});

5.4 Drill-down Interaction

  • Capture row click or chart slice selection

  • Make an API call to fetch detailed data for that item

  • Display in modal or nested table

6. Exporting Reports

  • Backend can generate Excel or PDF using libraries:

    • EPPlus / ClosedXML for Excel

    • iTextSharp / QuestPDF for PDF

Example (Excel export with EPPlus)

using var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("SalesReport");
worksheet.Cells["A1"].LoadFromCollection(reportData, true);
var excelData = package.GetAsByteArray();
return File(excelData, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "SalesReport.xlsx");

7. Performance Best Practices

  1. Use indexes on filter/sort columns in SQL Server

  2. Avoid SELECT *; fetch only required columns

  3. Implement caching for frequently accessed reports

  4. Limit API response size with server-side pagination

  5. Use stored procedures or views for heavy aggregation

8. Security Considerations

  • Validate and sanitize all query parameters

  • Implement role-based access control for sensitive reports

  • Secure API endpoints with JWT/OAuth2

  • Avoid exposing raw SQL queries to the frontend

9. Real-world Example: Sales Dashboard

  1. Summary Table – regional sales with drill-down to individual transactions

  2. Filter Panel – date range, category, region

  3. Charts – top-selling products, monthly trends

  4. Export Options – PDF and Excel

  5. Pagination – server-side for large datasets

  6. Role-based Access – managers see full data; staff sees limited regions

This approach is widely used in ERP systems, financial dashboards, and e-commerce analytics.

Summary

Interactive reporting in ASP.NET Core with SQL Server involves:

  • Designing user-friendly, filterable, and drillable reports

  • Efficient data access using stored procedures, Dapper, or EF Core

  • Implementing pagination, filtering, sorting, and aggregation

  • Building dynamic frontend tables and charts for user interactivity

  • Optimizing performance and ensuring security

  • Providing export and download options for business users

By following these practices, developers can deliver fast, interactive, and secure reports that scale to large datasets while maintaining a smooth user experience.

Membership not found