SSRS SharePoint Data Extension

Introduction:

Data processing extensions in Reporting Services enable you to connect to a data source and retrieve data. They also serve as a bridge between a data source and a dataset(used in SSRS )  If required custom data extensions  can be developed by using  .Net framework  and the unique extensibility architecture of Reporting Services  which enables  us to extend specific features of the product and its components.

SSRS SharePoint Data Extension is  a custom SSRS data processing extension that makes it possible to use Microsoft SQL Server Reporting Services 2005 /2008 for building reports over SharePoint data . This let us build sophisticated reports over SharePoint list, library, and InfoPath and document library. This lets you leverage all the power of Microsoft's reporting platform like exporting list data in multiple formats (Excel, PDF, CSV, XML  ... image), SSRS subscriptions to deliver report to email and file system.

Benefits of Extension

·         Many organizations use SharePoint lists /libraries  to keep track of critical business processes / enterprise data  and these data can be useful  for  business analysis and reporting , un fortunately there is no  out of box  data extension in SSRS 2005/2008  to support  SharePoint as an data source. This custom data extension helps in transform your SharePoint site into a fully fledged application by building sophisticated and customized reports 

·         With the help 0f this extension  SharePoint data can be exported to other formats  , use Reporting Services features to export the data into excel , word(SSRS 2008), XML .etc

·         This provides options to configure access permission to specific SharePoint data. You can build reports that will display data from sites and lists for which users do not have permissions because report can execute using the credentials you specify and if required it can be configured to use credentials of the user running the report.

·         This extension development in one time investment with approximate efforts of 2- 3 weeks. Later can be reused in all those projects which need reports to be generated from SharePoint data.

·         This extension can be extended / customized based on business requirement with fewer efforts.

·         This data extension has can use CAML (Collaborative Application Markup Languages)to query on SharePoint data which leads to increase in performance because only selected rows and column will be fetched from SharePoint

·         This support easy to design query and let the developer concentrate on report layout design. Because it abstracts complex web service API of SharePoint from SSRS report designer

·         This also can query on SharePoint user profile data

Alternative of SSRS SharePoint Data Extension

Out of Box XML- Data Processing Extension

Disadvantages 

·         Design of complex  xml structure 

·         No intelligence support

·         Doesn't Support CAML Query ,Fetches  all the Data from List/ library

·         Unable to do  Join, Union, Group

Third Party Data Extension  like  Enesys RS Data Extension

·         Costly alternative

Switching from SSRS Native Mode to SharePoint Integrated Mode

SharePoint integrated mode enables Reporting Services to integrate with the SharePoint(SP) databases and security model. These features become available when you configure a report server to run within a larger deployment of a SharePoint 3.0 product or technology.

In SP integrated mode, a report server must run within a SP server farm. A SP site provides the front-end access to report server content and operations with  all report processing and rendering. The advantage of integrated mode is a rich level of integration that allows you to access and manage report server content types using the application pages and data stores of a SP Web application.

Reports, data sources, and data models in a SP document library are stored as files in the SP content database for that SP document library. Files are stored in the .rdl, .rsds, and .smdl extensions.

Advantages of SharePoint Integrated Mode:

1)   Uniform UI to access reports – No need to access reports using SP and manage using the SSRS report manager. Everything will be done through SP UI.

2)   An ability to create and manage SSRS reports, SSRS data sources, and SSRS report models from the SP UI.

3)   Reports stored in the document libraries to enable versioning, workflow capabilities, collaboration, and document management features

4)   Shared Storage – Report Catalogs stored in the SP Content DBs

5)   Shared Security – No need to manage security at both SharePoint and Report Manager

6)   Inherited Governance Policy including SLA, Backup and Restore, Support, High Availability, Disaster Recovery, and Redundancy Policies

How to Switch from SSRS Native Mode to SharePoint Integrated Mode:

Steps to switch an existing SSRS server to SharePoint Integrated mode. This does not include any steps for migrating reports—we have to do them separately.

Switching to SSRS Integrated mode:

1.   Open SQL Reporting Services Configuration Manager on your SQL Server

·         Start

·         All Programs

·         Microsoft SQL Server 2008 R2

·         Configuration Tools

·         Reporting Services Configuration Manager

2.   Click on Databases, and note the current “Report Server Mode” is “Native”

3.   Select Change Database

4.   Select “Create a new report server database,” and click “Next”

5.   Enter your server name and credentials, and select “Next”

6.   Select a new “Database Name” and change “Report Server Mode” to “SharePoint Integrated Mode,” and click “Next”

7.   Enter authentication information, and click “Next”

8.   Verify Summary, and ensure “Report Server Mode” is set to SharePoint integrated

9.   Click next and ensure everything gives success status.

10.  In Reporting Services Configuration Manger, click on Web Service URL, and then click the URL link to open

11.  We should get an error that reads something like this:

12.   Reporting Services Error:

The report server has encountered a configuration error. If the report server is configured to use SharePoint integrated mode, verify that the server is joined to a SharePoint farm and that the Report Server service account has been granted access to the SharePoint farm. (rsServerConfigurationError)

13.  This error is expected, because we need to configure Reporting Services Integration in SharePoint.

14. Continue to next step.

15. Open up SharePoint Central Administration:

16. Go to “General Application Settings,” and click “Reporting Services Integration:”

17. Fill out “Report Server Web Service URL,” and your credentials:

18. Note that the Web Service URL should be what was specified in the Report Services Configuration Manager