Setting up Custom Tables

This guide walks you through creating custom tables in Dotnet Report by writing SQL queries, which allows you to create virtual tables from your connected data sources.

Prerequisites

• Existing data connection configured in the Dotnet Report
• Basic knowledge of SQL queries
• Admin access to Dotnet Report 

1. Access the Custom Tables Section

1. Log in to your Dotnet Report Admin Panel.
2. Go to “Manage Database” → “Custom Tables”.
3. Click “Add New Custom Table”.

2. Enter Table Details

• Name: Give a meaningful name (e.g., QuarterlyOrders2014).
• SQL Query: Write your SQL SELECT statement.

Example Query

SELECT 

    DATEPART(quarter, OrderDate) AS Quarter,

    COUNT(*) AS OrderCount,

    SUM(Freight) AS TotalFreight

FROM Orders

WHERE DATEPART(year, OrderDate) = 2014

GROUP BY DATEPART(quarter, OrderDate)

ORDER BY Quarter;

3. Save the Custom Table

• Click “Save” to validate and store the table.
• If successful, it will appear under “Database Tables”.
• You can configure and preview the table from Database Tables

Best Practices for writing SQL queries

• Optimize Queries – Only fetch necessary columns to improve performance.
• Use Clear Naming – Name tables logically (e.g., QuarterlySales).
• Test Queries First – Run them in your database tool before adding.

Troubleshooting

Common Issues and Solutions:

1. Query Syntax Errors:
   • Test queries in your database first
   • Ensure all table and column names are correct
   • Check for missing commas or parentheses

2. Performance Problems:
   • Add indexes to frequently filtered columns
   • Consider creating database views for complex queries
   • Limit results with WHERE clauses

3. Missing Data:
   • Verify join conditions
   • Check for NULL values that might be excluded
   • Confirm your query accounts for all needed business logic

Next Steps

After setting up custom tables, you can:

• Build reports using these tables.
• Set relations between tables (if needed in a future guide).