I’m evaluating the best approach for delivering KPI dashboards in our Crystal Reports 2022 enterprise environment. Currently, we schedule most reports to run during off-peak hours and cache the results for user access during business hours. This approach ensures consistent performance and reduces server load, but users sometimes complain about data staleness, especially for operational KPIs that change throughout the day. The alternative is on-demand generation where reports run in real-time when users request them, providing the freshest data but potentially impacting server performance during peak usage. I’m particularly interested in understanding how others handle server load balancing between scheduled and on-demand reports. What factors should drive the decision between these approaches for different types of KPI dashboards?
The on-demand approach sounds great until you experience a server meltdown during quarter-end when 500 users all try to refresh their dashboards simultaneously. Server load balancing isn’t just about having enough capacity - it’s about predictable load patterns. Scheduled reports give you that predictability. We use on-demand only for low-usage executive dashboards and schedule everything else. Our server utilization is smooth and we rarely have performance issues.
In today’s business environment, scheduled reports feel outdated. Users expect real-time data, especially for operational dashboards. We moved entirely to on-demand generation and addressed performance through database optimization and server scaling. Crystal Reports 2022 has improved caching mechanisms that make on-demand viable even at scale. The investment in infrastructure is worth it for the business value of current data.
After managing enterprise Crystal Reports 2022 environments serving thousands of users, I can provide a comprehensive framework for this decision.
Report Scheduling: Scheduling is optimal for KPI dashboards that meet these criteria: data changes on predictable intervals (daily, weekly, monthly), high user concurrency (many users accessing the same report), complex queries with long execution times, and regulatory or compliance reporting that requires consistent snapshots. The advantages are predictable server load, optimized resource utilization during off-peak hours, and guaranteed report availability during business hours. Schedule reports to run during maintenance windows, typically 2-4 AM, and cache results for daytime access. For global organizations, schedule reports in each region’s off-peak window.
On-Demand Generation: On-demand is appropriate for dashboards with these characteristics: data changes continuously throughout the day, low to medium user concurrency, relatively fast query execution (under 30 seconds), and user-driven parameters that create many unique report variations. The benefit is data freshness and elimination of stale data complaints. However, on-demand requires robust infrastructure and careful capacity planning. Implement query timeout limits (90 seconds maximum) to prevent runaway queries from consuming resources.
Server Load Balancing: The key to successful load balancing is implementing a multi-tier strategy. First, categorize your reports by execution time: fast (<10 seconds), medium (10-30 seconds), and slow (>30 seconds). Use separate Crystal Reports Processing Server pools for each category, with more servers allocated to fast reports since they handle higher concurrency. Configure the load balancer to route requests based on report category. This prevents slow reports from blocking fast reports.
Implement intelligent caching at multiple levels. At the Crystal Reports server level, cache report instances with varying TTL based on data volatility: 5 minutes for operational KPIs, 1 hour for tactical dashboards, 24 hours for strategic reports. At the database level, use materialized views for commonly queried aggregations. This reduces database load from on-demand requests.
For true real-time operational dashboards, consider a hybrid approach: schedule the bulk of the report (historical data) to run hourly, and use on-demand queries only for the most recent data (last hour). This dramatically reduces query complexity and execution time for on-demand portions while maintaining data freshness.
Monitor server metrics continuously: CPU utilization, memory usage, queue depth, and average response time. Set up alerts when queue depth exceeds 50 or average response time exceeds 45 seconds. These indicate capacity constraints. For horizontal scaling, maintain at least 30% headroom capacity to handle unexpected load spikes during month-end or quarter-end periods.
In practice, the optimal distribution for a large enterprise is approximately 60% scheduled reports (strategic KPIs, compliance reports, distribution reports), 30% on-demand with caching (operational dashboards, parameterized reports), and 10% pure on-demand (executive dashboards, ad-hoc analysis). This balance provides data freshness where it matters most while maintaining overall system performance and stability.
We use a tiered approach based on data volatility and user expectations. Strategic KPIs that change daily or weekly are scheduled overnight. Operational metrics that need to be current within the hour use on-demand generation with aggressive caching (30-minute cache duration). This balances freshness with performance. The key is setting user expectations - if they know a dashboard is refreshed hourly, they’re usually satisfied.