We’re running Crystal Reports 2016 with a shared data source connected to our SQL Server data warehouse. After our nightly ETL job completes around 3 AM, reports that multiple teams access throughout the day show inconsistent data - some users see updated figures while others see yesterday’s data.
The ETL process updates dimension and fact tables between 2:30-3:15 AM. Our first scheduled report refresh runs at 4 AM, but when users open reports manually during business hours, they’re getting stale data. We’ve checked the shared data source configuration and it points to the correct database, but the refresh behavior seems unpredictable.
I suspect this relates to report caching settings or how the shared data source handles refresh timing, but I’m not sure where to start troubleshooting. Has anyone dealt with similar refresh issues when using shared data sources across multiple reports?
I’ve seen this exact behavior before. The issue is usually two-fold: first, the shared data source might have connection pooling enabled which maintains stale connections, and second, individual report documents can have their own cache settings that override the data source refresh.
For the shared data source, you need to configure the connection to close after each use rather than pooling. In the data source properties, look for connection timeout and pooling parameters. Set the connection lifetime to match your ETL window.
Also check each report’s database options - there’s a setting for ‘Verify on First Refresh’ vs ‘Verify Database’ that affects how Crystal handles schema and data changes.
Thanks for the suggestions. I found the CMC caching settings - it was set to 120 minutes which definitely explains the stale data during business hours. However, I’m still unclear about the shared data source connection pooling. Where exactly do I find those settings in Crystal Reports 2016?
You need to address this systematically across three layers to resolve the refresh inconsistencies:
Report Caching and Refresh Settings:
In Central Management Console, navigate to your Crystal Reports processing servers and set the report cache to expire well before your ETL completion time. For a 3:15 AM ETL finish, set cache expiration to 3:00 AM or disable caching entirely for critical reports. Additionally, configure each report’s refresh options - right-click the report in CMC, go to Properties > Database, and enable ‘Perform Query Asynchronously’ with ‘Refresh on Open’ to force fresh data retrieval.
Shared Data Source Configuration:
Your shared data source needs proper connection management. In the CMC, edit your shared data source and verify these settings: Set ‘Connection String’ to include ‘Connection Lifetime=7200’ (2 hours in seconds, ensuring connections expire before ETL). Disable connection pooling by adding ‘Pooling=false’ to your connection string. For SQL Server native connections, the string should look like: ‘Server=yourserver;Database=yourdb;Connection Lifetime=7200;Pooling=false;Trusted_Connection=yes’
Most importantly, enable ‘Verify Database on First Refresh’ in each report’s Database menu. This forces Crystal to check for schema and data changes rather than trusting cached metadata.
ETL Timing and Report Scheduling:
Your 45-minute gap between ETL completion (3:15 AM) and first report refresh (4:00 AM) isn’t sufficient. ETL processes need post-completion tasks: update database statistics (DBCC UPDATEUSAGE, UPDATE STATISTICS), rebuild fragmented indexes, and clear SQL Server’s procedure cache. These can take 20-30 minutes for large warehouses.
Restructure your schedule: ETL completes by 3:15 AM, maintenance tasks run 3:15-3:45 AM, then schedule Crystal Reports cache clearing at 3:50 AM using the SDK or CMC scheduling, and finally trigger report refreshes at 4:15 AM. For ad-hoc user access during the day, the ‘Refresh on Open’ setting combined with disabled caching ensures they always get current data.
Implement monitoring by logging report execution times and data timestamps in your reports themselves - add a formula field showing the max transaction date from your fact tables so users can verify data freshness visually.
Check your Central Management Console settings first. Under Crystal Reports Server, there’s a caching configuration that might be causing this. If report caching is enabled with a long TTL, users opening reports will get cached versions instead of fresh data.
The connection pooling isn’t in Crystal itself - it’s in your ODBC/JDBC driver configuration. For SQL Server, check your DSN settings if using ODBC, or connection string parameters if using native drivers. Look for parameters like ‘Connection Lifetime’ and ‘Pooling’. You want connections to expire before your ETL window starts.
Also, coordinate your ETL timing with report scheduling more carefully. If your ETL runs until 3:15 AM and reports kick off at 4:00 AM, that’s cutting it close. Add buffer time to ensure database statistics are updated and indexes are rebuilt before reports run.