SyteLine Report Builder Advanced Techniques

Custom Dataset Design for SyteLine Reports

SyteLine reports can pull data through IDO datasets or direct SQL datasets. IDO datasets respect security and business logic but can be slow for large result sets. Direct SQL datasets offer superior performance but bypass IDO-level security and calculated properties.

The best approach often combines both: use IDO datasets for secured summary data and direct SQL for high-volume detail sections where performance is critical.

• Use IDO datasets when reports must respect SyteLine row-level security and calculated fields
• Switch to direct SQL datasets for detail-level reports exceeding 10,000 rows for performance
• Create dedicated reporting views optimized for common report queries rather than reusing form views
• Parameterize datasets with report parameters to push filtering to the database layer, not the report

Complex Report Patterns and Subreports

Multi-level BOM explosions, where-used analyses, and cross-reference reports require recursive data handling that SSRS subreports manage cleanly. However, subreports have a performance cost, each instance opens a separate database connection and executes independently. For reports with hundreds of subreport instances (like a multi-level BOM with 500 components), consider flattening the data in a stored procedure and using grouping instead of subreports.

• Use recursive CTEs in stored procedures to flatten multi-level BOM data for report consumption
• Limit subreport usage to 50 or fewer instances per report to avoid connection pool exhaustion
• Implement report caching for parameter combinations that are frequently requested by users
• Use SSRS shared datasets for data that appears in multiple reports to improve consistency and cache reuse

Report Deployment and Performance Management

SyteLine report deployment involves publishing RDL files to the SSRS server, registering them in the SyteLine report catalog, and configuring parameters and security. Performance management for a reporting environment with 100+ custom reports requires monitoring execution times, managing report subscriptions, and scheduling resource-intensive reports during off-peak hours to avoid impacting the transactional system.

• Register all custom reports in the SyteLine report catalog with appropriate parameter defaults
• Monitor SSRS execution logs monthly to identify slow reports and optimize or retire them
• Schedule heavy reports as subscriptions delivered via email rather than on-demand execution
• Configure SSRS report execution timeouts aligned with report complexity to prevent runaway queries