Migrating from SSRS to Power BI

Migrating from SQL Server Reporting Services (SSRS) to Power BI is one of the most common modernization projects for organizations looking to leverage interactive dashboards, cloud deployment, and AI-powered analytics. With Microsoft's strategic investment in Power BI and Fabric, the migration path has matured significantly. This guide provides a structured approach to planning and executing the migration based on hundreds of enterprise SSRS migrations completed by our Power BI consulting team.

Migration Approach Overview

| SSRS Report Type | Power BI Target | Migration Method | Effort Level | |---|---|---|---| | Pixel-perfect operational reports | Paginated Reports (RDL) | Republish with data source updates | Low (days) | | Analytical dashboards | Interactive Power BI reports | Rebuild with modern visuals | Medium (1-2 weeks/report) | | Data-driven subscriptions | Power BI subscriptions | Reconfigure delivery | Low (hours) | | Rarely used / obsolete | Retire | Archive and decommission | Minimal | | Reports with custom assemblies | Rewrite in DAX/Power Query | Full redesign required | High (2-4 weeks/report) |

Assessment Phase

Before migrating, inventory your SSRS environment completely. Document every report including its data sources, subscriptions, parameters, usage frequency, and business owner. Use the SSRS ExecutionLog table in the ReportServer database to identify actual usage patterns—many organizations discover that 40-60% of their SSRS reports have zero executions in the last 90 days.

Report tier classification:

Tier 1 — Operational Reports (republish as paginated): Highly used reports that need exact formatting preservation for regulatory compliance, printing, or pixel-perfect layout. These include invoices, statements, compliance filings, and operational checklists. Migrate directly to Power BI paginated reports using the RDL format.

**Tier 2 — Analytical Reports (rebuild as interactive):** Reports that display KPIs, trends, and metrics that would benefit from interactivity. These should be rebuilt as Power BI reports with modern visuals including drill-through, cross-filtering, and AI features like Copilot and natural language Q&A.

Tier 3 — Retire: Reports with zero or near-zero usage in the last 6 months. Archive the RDL files for reference but do not invest in migration. This typically eliminates 30-50% of the migration scope.

Migration Strategies by Report Type

Paginated Reports (RDL Migration)

Power BI Premium supports paginated reports that use the same RDL file format as SSRS. Many SSRS reports can be published directly to the Power BI Service with minimal changes.

Step-by-step process: 1. Open the existing .rdl file in Power BI Report Builder (free download) 2. Update data source connections from on-premises SQL Server to either gateway-connected sources or cloud databases 3. Replace Windows Authentication with Azure AD service principal or managed identity 4. Test rendering in all required export formats (PDF, Excel, Word, CSV) 5. Verify parameters function correctly in the Power BI Service 6. Publish to a Premium or Fabric workspace 7. Configure subscriptions to replace SSRS email delivery

Key compatibility differences: - Custom assemblies (.dll references) are not supported—these must be rewritten as expressions or external API calls - Some VB.NET expressions need adjustment to Power BI Report Builder's expression syntax - PDF rendering may have minor layout differences—test with actual business users - Subreports must be published separately and referenced by path

Interactive Dashboard Conversion

Analytical reports benefit most from full conversion to interactive Power BI dashboards:

1. **Data model design**: Replace SSRS dataset queries with a proper Power BI semantic model following star schema design. Consolidate multiple SSRS datasets into a single optimized model.
2. Visual redesign: Rebuild the layout using Power BI visuals with drill-through, cross-filtering, tooltips, and conditional formatting. Do not attempt to replicate the exact SSRS layout—leverage Power BI's interactive strengths.
3. **Parameter replacement**: Convert SSRS parameters to Power BI slicers, filter panes, or field parameters. Multi-select parameters map to multi-select slicers.
4. **AI enhancement**: Add features not available in SSRS: Smart Narratives, natural language Q&A, anomaly detection, and Key Influencers visual for root cause analysis.

Hybrid Approach

Many organizations run SSRS and Power BI in parallel during migration, typically for 3-6 months. Embed paginated reports within Power BI dashboards to combine pixel-perfect tables with interactive charts on the same page. This lets teams validate data accuracy between old and new platforms before decommissioning SSRS.

Data Source Migration

SSRS reports typically connect to on-premises SQL Server databases using Windows Authentication and direct SQL queries or stored procedures. For Power BI, you have three paths:

**Path 1 — On-premises Data Gateway:** Maintain connections to existing databases. Install the Power BI Gateway in cluster mode with at least two nodes for high availability. This is the fastest path but requires ongoing gateway infrastructure management.

Path 2 — Cloud migration: Migrate databases to Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics. This eliminates gateway dependency and enables full cloud operation. Best for organizations with an existing Azure migration roadmap.

Path 3 — Hybrid: Use the gateway for some sources while migrating others to Azure. Gradually reduce gateway dependence over 6-12 months as databases move to the cloud.

Subscription and Delivery Migration

SSRS subscriptions that email PDF reports to users need careful migration to Power BI subscriptions:

Power BI subscription capabilities: - Email delivery of report page snapshots as images or full paginated report PDFs - Paginated report attachments in PDF, Excel, Word, CSV, and PowerPoint format - Scheduled delivery with hourly, daily, weekly, and monthly frequency - Conditional delivery: only send when data meets specific criteria - SharePoint and Teams integration for report delivery

SSRS features requiring workaround: - Data-driven subscriptions (personalized delivery per recipient) require Power Automate workflows in Power BI - File share delivery is not native—use Power Automate to save exports to SharePoint or network locations - NULL delivery provider (caching) has no direct equivalent—use scheduled refresh instead

Licensing note: Power BI subscriptions require Premium capacity, Premium Per User (PPU), or Microsoft Fabric capacity. Plan licensing before migrating subscriptions.

Testing and Validation Checklist

Every migrated report must be validated against the original SSRS version:

• Data accuracy: Compare totals, subtotals, and detail rows between SSRS and Power BI for the same date range and parameters
• Visual formatting: Verify that paginated reports preserve layout, fonts, colors, and page breaks in all export formats
• Parameter functionality: Test all parameter combinations including defaults, multi-select, cascading, and null values
• Export formats: Generate PDF, Excel, and CSV exports and compare to SSRS output
• Performance: Measure report render time and compare to SSRS baseline. Power BI interactive reports should be faster; paginated reports may have slightly different render times
• Subscription delivery: Verify email recipients receive reports on schedule in the correct format
• **Security**: Confirm Row-Level Security filters data correctly for each user role

Common Migration Challenges and Solutions

Data type discrepancies: SSRS dataset columns and Power BI model columns may infer different data types, causing calculation differences. Explicitly cast data types in Power Query to match SSRS behavior.

Multi-value parameters: SSRS multi-value parameters pass comma-separated strings to stored procedures. In Power BI, replace with multi-select slicers that filter the model directly—no stored procedure modification needed.

VB.NET expressions: Complex SSRS expressions using VB.NET string manipulation, date logic, or conditional formatting need DAX or Power Query M equivalents. Build a mapping reference for common expression patterns.

Custom assemblies: SSRS reports referencing custom .dll files for business logic, encryption, or external API calls have no direct equivalent. Rewrite as Power Query custom functions, DAX measures, or call external APIs via Power Automate dataflows.

Report scheduling conflicts: SSRS report execution plans and cached instances do not transfer. Rebuild refresh schedules and caching strategies in Power BI Service.

Related Resources

• Power BI Migration Strategies
• Paginated Reports Best Practices
• Power BI Gateway Setup
• Power BI Consulting Services