Designing High-Performance SSRS Reports for Epicor

Overview

SQL Server Reporting Services (SSRS) is Epicor's default layout engine for operational documents like invoices, pick lists, and shipping labels. For many teams, however, SSRS report generation becomes slow over time. For technical discussions and answers, visit the EpiUsers

This lag is often caused by bloated Report Data Definitions (RDD). Developers regularly copy standard RDDs and leave unnecessary parent-child table mappings active. For optimal performance, exclude unused columns and relationships. When designing custom BAQ reports, similar performance considerations apply to query structures.

Technical Implementation Details

To implement this solution in your Epicor environment, utilize the following code block. Ensure you replace the standard configurations with your company database names, server URLs, and keys.

-- SQL Query configured in SSRS Shared Data Source for Epicor Invoice Report
-- Rebuilding the select statement to avoid full scans on InvcHead
SELECT 
    T1.InvoiceNum,
    T1.InvoiceDate,
    T2.InvoiceLine,
    T2.PartNum,
    T2.LineDesc,
    T2.DocExtCost
FROM Erp.InvcHead T1
INNER JOIN Erp.InvcDtl T2 ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
WHERE T1.InvoiceNum = @InvoiceNum;

Process Verification and Flow

After optimizing the query, developers can configure Advanced Print Routing (APR) rules to automate output delivery. APR can split a combined batch run and email individual PDFs to customers based on contact records. In more advanced workflows, you can trigger a BPM workflow to handle complex email distributions or post-processing log updates.