Many organisations are turning to the cloud for their SharePoint options; however there are still a significant number of businesses that require SharePoint to be on-premises. The cloud has lots of different options for storing and sharing information, and the options are flexible with on-premises, too. Our guide here explains how you can utilise reporting within SharePoint; below is a quick summary of how you can utilise on-premises SharePoint for reporting.
If your SharePoint is held locally in your organisations, i.e. it’s NOT in the Cloud, then you have an on-premises instance. Possibly you have SharePoint 2010, 2013, or maybe 2016 (although we have come across a few instances of SharePoint 2007!)
Managing your SharePoint environment on-premises comes with many advantages, such as all your data being managed on your site and only controlled by you – so effectively the data lives behind your firewall and Business Connectivity Services remain in place and maintained. This on-premises environment can deliver an exceptionally robust reporting function for your organisation. With this SharePoint environment, you will have SQL Server acting as your database. As this is the case you can immediately utilise SQL Server Reporting Services (SSRS).
Leverage SQL Server Reporting Services (SSRS)
SSRS is there to support the publishing, management, creation and delivery of reports. These reports can be delivered to the right users in various ways (viewing the reports in a browser or via an email, or on mobile). You’ll have the ability to create modernised paginated reports with a mobile responsive layout so the reports can be delivered exactly how you want. There are many tools and features in the platform to enable you to tailor your reports however you want.
You can make Excel work for you by storing workbooks in SharePoint; so, if you’re not quite Excel-free in your business (who is?!), you can make hook those spreadsheets into SSRS and interrogate your data in different ways.
Leverage Power Pivot
Power Pivot is an Excel add-in you can utilise to perform data analysis and create sophisticated data models. Power Pivot allows you to combine large volumes of data from various sources and slice and dice that data into meaningful reports. It also allows you to interrogate and analyse data rapidly, and share insights easily. Excel and Power Pivot both allow the creation of a Data Model and a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. The difference between Power Pivot and Excel is that you can create a more sophisticated and complex data model by working on it in the Power Pivot window.
Using Power Pivot / Excel in a SharePoint environment
You have more control and advantage by publishing your (Excel) workbook to a SharePoint environment that has Excel Services enabled. On your SharePoint server, Excel Services processes and renders your data in a browser window – allowing others to analyse the data. On SharePoint, you can add Power Pivot to get additional collaboration and document management support, including:
- Power Pivot Gallery
- Power Pivot management dashboard in Central Administration
- Scheduled data refresh
- The ability to use a published workbook as an external data source from its location in SharePoint.