After updating transactional data in our ERP system, embedded analytics visualizations in SSRS 2017 reports continue showing stale data for 15-20 minutes. We need near-real-time analytics for operational dashboards, but there’s a significant lag between ERP updates and SSRS visual refresh.
The embedded analytics integration uses direct SQL queries to the ERP database, so I expected immediate data updates. However, SSRS appears to be caching the report results aggressively. I’ve checked the report execution settings and dataset cache settings, but the refresh behavior hasn’t improved.
Here’s our current query structure:
SELECT OrderID, Status, UpdatedTime
FROM ERPSystem.dbo.Orders
WHERE UpdatedTime > DATEADD(HOUR, -1, GETDATE())
The query returns current data when run directly in SQL Server Management Studio, but SSRS visuals show data from 15+ minutes ago. Is there a way to force SSRS to bypass caching for embedded analytics that need real-time ERP data synchronization?
For embedded analytics requiring real-time updates, you should avoid SSRS’s built-in caching entirely. Set the report to ‘Always run this report with the most recent data’ in Report Manager. However, be aware this will increase database load significantly if many users access the dashboard simultaneously. Consider implementing a middle-tier cache with shorter TTL instead.
Have you verified that your ERP data synchronization is actually completing when you think it is? Sometimes the issue isn’t SSRS caching but delays in the ERP’s own data propagation. If you’re querying a read replica or reporting database, there might be replication lag between the transactional database and the reporting database that SSRS queries.
SSRS Web Portal does implement client-side caching in the browser. Try adding cache-busting parameters to your report URL, or configure the Web Portal to send no-cache headers. Also, some proxy servers or CDNs between users and SSRS can cache report output. Check your network infrastructure for intermediate caching layers.