SharePoint 2010 and Business Intelligence SQL Server Reporting Services

Kyle, SharePoint Consultant
for Entrance Software
October 28, 2011

As a SharePoint consultant, there’s no way I can overstate the power and value of setting up a Business Intelligence solution in SharePoint 2010. Being able to both visualize business data and make decisions based on that information transforms SharePoint from a document management and collaboration web app to a full-fledged Intranet portal. And if you’re running SharePoint 2010, then you already have all the prerequisites you need to start creating and surfacing reports for a full Business Intelligence solution.

Most paid versions of Microsoft SQL Server—the database that serves as the backend for SharePoint—include SQL Server Reporting Services (SSRS). This is the engine with which reports are generated. Reports live in a Report Library on your SharePoint server, are fed to the SSRS Server for processing, and returned as finished reports.

When SharePoint and SSRS are working in tandem, the result is a truly enhanced experience that can reap countless rewards for your business. A few of my favorite features include:

SharePoint Security – Using existing SharePoint
security groups with Report Libraries allows you to leverage your existing
SharePoint structure to keep sensitive reports hidden from certain users.
Scheduling and Management – Scheduled delivery
of reports can be set up and managed from within SharePoint, allowing users to
configure delivery via e-mail, file share, or SharePoint library. Snapshots can
also be scheduled to reduce the load time for report pages.
Versioning – Like any document library in
SharePoint, a Report Library comes with all the advantages you’re used to,
including versioning, report history, and metadata.
Data Connection Management – Data connections
shared by reports can be managed from within SharePoint, allowing you to easily
change server names and execution credentials from within SharePoint.
Report Viewer Web Part – Besides running the
reports within SharePoint’s integrated report viewer interface, reports can be
embedded in Web Part pages, giving you the ability to assemble custom
dashboards with several reports, connect reports to other Web Parts, and set
execution parameters and permissions automatically.
Reports with SharePoint List Data – SSRS reports
can be configured to use several types of data sources, but leveraging
SharePoint list data can provide all new metrics for your business. Who are
your most prolific authors? What tasks are aging, and which users have the most
tasks assigned to them? You can answer all of these questions and more using
SSRS reports that pull SharePoint list data.

In order to get SSRS and SharePoint fully integrated, there is plenty of work involved. SSRS must be configured in SharePoint Integrated Mode, which sets up special tables in the SSRS configuration database enabling it to process reports in SharePoint. SharePoint itself must also be configured to point to the SSRS server. And of course, you’ll need reports. Luckily, the team at Entrance Software can help you with all of these tasks, from designing your SharePoint solution to creating and deploying reports.

Tags: Business Intelligence, Sharepoint 2010, SharePoint Consultant, SQL Server Reporting Services, SSRS