SharePoint / Guide 11
Reporting and BI: SSRS, analytics and Power BI
SSRS integration through the versions, SharePoint usage analytics, and Power BI.
SharePoint has been a home for reports and dashboards since 2007, and the options have changed almost completely. This guide covers SQL Server Reporting Services (SSRS), SharePoint's own usage analytics, and Power BI, on-premises and in Microsoft 365.
The short version
| You want to... | On-premises | SharePoint Online |
|---|---|---|
| Show paginated (SSRS) reports in SharePoint | SSRS or Power BI Report Server in native mode, shown with the Report Viewer web part or an embedded page | Power BI paginated reports, embedded with the Power BI web part |
| Show interactive dashboards | Power BI Report Server, embedded in a page | Power BI web part on a modern page |
| Report on SharePoint list data | Power BI Desktop with an on-premises data gateway, or SSRS | Power BI (SharePoint Online list connector) |
| See site usage | Popularity Trends and usage analytics | Site usage page and Microsoft 365 usage reports |
SSRS and SharePoint: how it changed
SQL Server 2005 to 2008 R2: SharePoint integrated mode (add-in). Reporting Services ran on its own server in "SharePoint integrated mode". Reports, data sources and subscriptions were stored in SharePoint libraries, and the Reporting Services add-in on the SharePoint servers connected the two.
SQL Server 2012 to 2016: SSRS as a SharePoint service application. Reporting Services in SharePoint mode was installed on the SharePoint servers themselves and managed like any other service application in Central Administration, with PowerShell cmdlets for setup. Reports lived in document libraries and appeared with the Report Viewer web part.
SQL Server 2017 and later: native mode only. Microsoft removed SharePoint integrated mode. Reporting Services (and Power BI Report Server, which includes it) runs in native mode on its own servers, with its own web portal. To show reports in SharePoint you use the native-mode Report Viewer web part (check that it supports your SharePoint version) or embed the report page.
What's next. Microsoft has announced that on-premises reporting is consolidating on Power BI Report Server, starting with SQL Server 2025. If you're planning new on-premises reporting, start there, and check Microsoft's current SQL Server documentation for the details.
Migrating off integrated mode: when you upgrade to SharePoint 2019 or Subscription Edition, integrated-mode reports don't come along. Move reports, data sources and subscriptions to a native-mode server first (Microsoft provides a migration tool for this), then update the links in SharePoint.
Embedding a Power BI Report Server or SSRS report in a page
Power BI Report Server supports an embed parameter that shows a report without the portal around it:
https://reports.ravindran.in/reports/powerbi/Sales/Monthly?rs:embed=true
Use the Page Viewer web part (classic pages) or the Embed web part (modern pages) with that URL. Users need permission on the report server as well as the SharePoint page, and the report server should use the same authentication (ideally Kerberos) so they aren't prompted to sign in.
Other BI features you may still meet
| Feature | Versions | Where it went |
|---|---|---|
| Excel Services | 2007–2013 | Moved to Office Online Server in 2016 |
| PerformancePoint Services | 2010 onward | Deprecated; Power BI is the replacement |
| PowerPivot for SharePoint | 2010–2016 (with SQL Server Analysis Services in SharePoint mode) | Replaced by Power BI |
| Power View (Silverlight) | 2013 | Retired; replaced by Power BI |
| Visio Services | 2010 onward | Deprecated on-premises; Visio for the web in Microsoft 365 |
If a farm still depends on any of these, plan the move to Power BI before your next upgrade.
SharePoint's own analytics
2010: Web Analytics service application. Reports for traffic, search and inventory, per site and site collection.
2013 and later: usage analytics in search. The Web Analytics service was removed. The analytics processing component of search now processes usage events and produces:
- Popularity Trends (Site Settings > Site Collection Administration, and per item and library), which open in Excel with daily and monthly views and unique users
- Most Popular Items per library
- Search reports (top queries, no-result queries, abandoned queries)
These depend on the Usage and Health Data Collection service application, the usage timer jobs, and search crawls. If Popularity Trends are empty, check that usage data collection is on in Central Administration (Monitoring > Configure usage and health data collection), that the search service application is healthy, and that the usage definitions are enabled (look for any showing False):
Get-SPUsageDefinition | Select-Object Name, Enabled
Usage log files are written to the hive's LOGS folder (or the location you set) and imported by timer jobs, so reports lag real activity by a day or so.
Power BI
Power BI web part (SharePoint Online). On a modern page, add the Power BI web part and paste the report link from the Power BI service (File > Embed report > SharePoint Online). Viewers need a Power BI Pro or Premium Per User license, unless the report is in a workspace on Premium or Fabric capacity. The report respects Power BI permissions and row-level security, so sharing the SharePoint page doesn't give access to the data on its own.
Reporting on SharePoint data.
- SharePoint Online lists: use the SharePoint Online list connector in Power BI Desktop; its newer implementation is much faster on large lists.
- Files in a library (Excel, CSV): use the SharePoint folder connector with the site URL.
- SharePoint Server lists: use the SharePoint list connector with the site URL. Scheduled refresh in the Power BI service needs an on-premises data gateway that can reach the farm.
Tips from the field:
- Point reports at a view with only the columns you need, or filter in Power Query; pulling every column of a large list is slow.
- Lookup, person and managed metadata columns come through as records; expand only the fields you need.
- For heavy reporting on SharePoint Online data, consider landing it in a database or Fabric first, rather than querying the list directly on every refresh.