Integrating SQL Server 2008 R2 Reporting Services with SharePoint 2010

Problem
I'm ready to install SQL Server 2008 R2 and SharePoint 2010 in a test environment.  I'm trying to decide whether to run SQL Server Reporting Services in SharePoint Integrated mode or native mode.   What are the advantages of running Reporting Services in SharePoint Integrated mode?  Can you provide an overview of what I need to do to get Reporting Services installed and running in SharePoint Integrated mode?
Solution
The biggest advantage to running Reporting Services in SharePoint Integrated mode is that you can deploy data sources, reports, etc. to SharePoint document libraries instead of the Report Manager web application that Reporting Services creates for you.  Since your users are probably familiar with SharePoint this makes sense.  In addition your users can take advantage of the new version of Report Builder that came with SQL Server 2008 R2 and deploy their reports to SharePoint document libraries, leveraging SharePoint for security.  This is easier for them than using the Report Manager.  The users can be much more self-sufficient with SharePoint.
In this tip I will highlight the steps you will want to follow to get Reporting Services installed and integrated with SharePoint.  I'll be using SQL Server 2008 R2 and SharePoint 2010 Foundation.  SharePoint 2010 Foundation is the new name for what was formerly Windows SharePoint Services.  It is the version of SharePoint that is included with your Windows 2008 server license.  The steps for SharePoint 2010 Server are essentially the same.  In order to get a test environment up and running as quickly as possible, I will install everything on a single server.  I'm using Windows Server 2008 R2 64 bit; 64 bit is a requirement for SharePoint 2010.
Installing SQL Server 2008 R2
At a minimum, install the database engine and reporting services.  By installing SQL Server first you have the option of specifying the database engine that SharePoint will use.  Alternatively you can install SharePoint and allow it to install its own instance of SQL Server Express (now called SQL Server Embedded Edition).  There are two steps I want to highlight in the SQL Server 2008 R2 installation: Setup Role and Reporting Services Configuration.
In the Setup Role step, choose SQL Server Feature Installation as shown below:

In the Reporting Services Configuration step, choose Install the SharePoint Integrated mode default configuration as shown below:

This allows the install to do the majority of the work, limiting the steps that you will need to perform to get Reporting Services integrated with SharePoint.
Installing SharePoint 2010
In order to successfully install SharePoint, there are a number of prerequisites that must be installed.  When you launch the SharePoint 2010 setup, make sure to first click Install software prerequisites as shown below:

After clicking Install software prerequisites the following screen is displayed:

Note that this step will automatically install anything that you need from the list of required products and updates.  In order to run Reporting Services in SharePoint Integrated mode, you need the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in; this will be done for you.
Now we are ready to install SharePoint 2010 Foundation.  Click Install SharePoint Foundation on the splash screen (shown at the beginning of this section).  Since we are interested in a test environment choose Standalone as shown below:

The Standalone option is perfect for getting going quickly; it installs the software with all default settings.  While this approach requires the least amount of effort on your part, it does install an instance of the SQL Server Embedded Edition that can only be used by SharePoint; i.e. you can't leverage this SQL Server instance for the Reporting Services database.  In addition you truly get a standalone instance of SharePoint; you cannot add any other SharePoint servers.  Despite these limitations, I'm going to proceed with the Standalone option.
When the SharePoint 2010 Foundation installation is completed, you will be prompted to run the SharePoint Products Configuration Wizard.  You need to do that to in order to complete the configuration steps required to have a working installation of SharePoint 2010 Foundation.  When the SharePoint Products Configuration Wizard is finished, your browser will open to the SharePoint Team site that has been created for you.
Configuring Reporting Services SharePoint Integration
At this point we have Reporting Services 2008 R2 and SharePoint 2010 Foundation installed.  The next step is to configure Reporting Services SharePoint integration using SharePoint Central Administration.  Launch SharePoint Central Administration from the Start menu (it should be pinned to the Start menu; if not then select it from All Programs, Microsoft SharePoint 2010 Products group).  Click General Application Settings as shown below:

Click Reporting Services Integration as shown below:

Note that the Reporting Services options shown above will only appear after the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in is installed.  I installed the add-in by running the Install software prerequisites option before installing SharePoint.
There are just a couple of things that you need to enter to complete the integration as shown below:

The following are the main points about the integration settings:

• You can get the Report Server Web Service URL from the Reporting Services Configuration Manager (in the Microsoft SQL Server 2008 R2, Configuration Tools program group).  Click on Web Service URL in the menu on the left.
• When you have everything running on a single server as I do in this example, choose Trusted Account for Authentication Mode.
• For Credentials you do need an account that is in the local Administrators group.  With everything on a single server it can be a local account, but I'm using a domain account.
• Choose Activate feature in all existing site collections.

After entering the required information and clicking OK, you should see the Reporting Services Integration Summary as shown below:

At this point Reporting Services is configured in SharePoint Integration mode.
Preparing a Document Library
There is one last step that needs to be performed in order to create Reporting Services artifacts (e.g. shared data sources, report models, reports) in a SharePoint document library.  We need to add the appropriate content types to the document library and customize the New Document menu options.  For this example I created a brand new document library called Reports.
Navigate to the document library then click Library Settings as shown below:

SharePoint 2010 has a new ribbon menu.   You have to click on the Library tab in order to see the above (the other option is the Documents tab).  Click Advanced Settings on the page as shown below:

Click the Yes radio button under Allow management of content types then click OK:

You will be returned to the library settings page; click Add from existing site content types:

Select the Report Builder Model, Report Builder Report, and Report Data Source content types from the Available Site Content Types List, then click the Add button:

Click OK to return to the library settings page; click Change new button order and default content type:

Change the Visible property and Position from Top as shown below:

Click OK, then return to the document tab for the document library.  Click New Document and you will now see the menu options as we have configured them:

Click Report Builder Report to launch Report Builder.  You should see Report Build 3.0 launch and you can now author reports, save them to a SharePoint document library, and of course run reports by simply clicking on them in the SharePoint document library.

Ref link: http://www.mssqltips.com/tip.asp?tip=2122

Cross Apply Data Source for SSRS R2 2008

I was working on SSRS 2008 R2 migration and needed a list of data sources to join to the ReportServer.dbo.Catalog table. Since a single report can have several datasources I needed a way to rollup the datasource Names to a single ItemID. I have used CTE's in the past but wanted to give the Cross Apply a go. Cross Applym, considerably easier than a CTE.

Cross Apply Data Source Name for SSRS:
SELECT DISTINCT
ds.ItemID ,
Name = LEFT(dsl.list, LEN(dsl.list) - 1)
FROM
dbo.DataSource AS ds
CROSS APPLY ( SELECT
ds2.Name + ',' AS [text()]
FROM
dbo.DataSource AS ds2
WHERE
ds.ItemID = ds2.ItemID
ORDER BY
ds2.ItemID
FOR
XML PATH('')
) dsl ( list )

WHERE
ds.ItemID IS NOT NULL
AND LEN(ds.Name) > 0

Ref Link:

Sql Server Community Free Training Webinar Series Pragmatic Works

If you have not taken part in any of the free Webinars that Pragmatic Works and Sql Server Community have put together you are missing out on a great resource. These webinars are free and provide some information from some of the top industry experts on topics that can help your organization. Each month they put out a series of sessions that you can take part via live webinar or view the recorded sessions. Typically, the sessions are on Tuesday and Thursday of each week in the month. Once again make sure to check out the great resource.

Featured Session

Past Webinars

Integration Sql Server 2008 R2 Reporting Service with SharePoint 2010

For those that are considering SSRS 2008 R2 integration mode with SharePoint 2010 I would recommend reviewing the latest article from MSSQL Tips. It provides a simple approach to setup with step by step photos.

Reference Link: