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:

Spatial Convert Latitude/Longitude (Lat/Long) to Geography Point

Convert Latitude/Longitude (Lat/Long) to Geography Point

Prior to SQL Server 2008, geography locations such as bank branches, restaurant locations and household locations, are usually stored as latitude and longitude coordinates in two separate columns. With the introduction of spatial data types in SQL Server 2008, particularly the GEOGRAPHY data type, this can now be stored as points in a single column stored as a spatial data object. This article will show the different ways of converting the latitude and longitude coordinates of geography locations into a geography POINT.

The GEOGRAPHY data type supports seven spatial data objects that you can create and work with, namely Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon and GeometryCollection. From these spatial data objects, we will be using the Point.



Let's say we have a table of famous international geographic landmarks where we store its latitude and longitude coordinates and we want to take advantage of SQL Server 2008's new geography data type. (Note: The latitude and longitude coordinates of these landmarks were taken from Google maps and not from Microsoft's Virtual Earth (or Bing Maps).)

CREATE TABLE [dbo].[Landmark] (
[ID] INT IDENTITY(1, 1),
[LandmarkName] VARCHAR(100),
[Location] VARCHAR(50),
[Latitude] FLOAT,
[Longitude] FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GOUsing geography::STPointFromText

The first thing to do is to add the new column where we will store the geography point. This can easily be done by the ALTER TABLE statement and we will use the GEOGRAPHY data type for the new column.

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO
To convert the latitude and longitude coordinates to a geography point, we will use the StPointFromText function. The STPointFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOAs you can see from this UPDATE statement, the longitude is specified first before the latitude. The reason is because in the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, the format is (x, y). Geographic coordinates are usually specified by Lat/Long but between these two, the X is the Longitude while the Y is the Latitude.

You may be wondering why the X-coordinate is the Longitude while the Y-coordinate is the Latitude. Think of the equator of the earth as the x-axis while the prime meridian is the Y-axis. Longitude is defined as the distance from the prime meridian along the x-axis (or the equator). Similarly, latitude is defined as the distance from the equator along the Y-axis.

If the latitude and longitude were accidentally interchanged in the parameter for the STPointFromText, you may get the following error message particularly for the Sydney Opera House landmark record:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
Using geography::STGeomFromText

Another way of creating a geography POINT is with the use of the STGeomFromText OGC static method. Just like the STPointFromText OGC static method, the STGeomFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOUnlike the STPointFromText method, which only accepts a POINT, the STGeomFromText can accept any of the seven spatial data objects (POINT, POLYGON, LINESTRING, MULTIPOINT, MULTIPOLYGON, MULTILINESTRING and GEOMETRYCOLLECTION). If you try to pass any other spatial data object to the STPointFromText aside from a POINT, you will get the following error message:

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24142: Expected "POINT" at position 1. The input has "LINES".
Using geography::Point

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Point extended static geography method. The geography::Point method constructs a geography instance representing a POINT instance from the latitude and longitude values.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
GO
Unlike the STGeomFromText and STPointFromText geography static methods, which accepts as parameter the point coordinates in the Open Geospatial Consortium (OGC) Well-Known Text representation, the Point geography static methods accepts as parameters the Latitude and Longitude coordinates of the point in that order.

Using geography::Parse

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Parse extended static geography method. The geography::Parse static method is similar to the STGeomFromText static method in the sense that it returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Parse('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')')
GOThe only difference between the geography::Parse method and the geography::STGeomFromText method is that in the Parse method, it assumes a spatial reference ID (SRID) of 4326, which is the second parameter of the geography::STGeomFromText method.


Reference link:

7 Free .NET Development and Architecture E-books

Who doesn't love free books? Take a look at this list of 7 useful free e-books for .NET programmers and budding architects


7 Free .NET Development and Architecture E-books

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

SSIS Expression Cheat Sheet

Over the past week I was watching some of the free Pragmatic Works Webinars and came across a SSIS Expression Cheat Sheet. If you work with ETL processes in SSIS I would highly recommend taking a look at this resource. It outlines some of the more common data transfers that are needed in the Data Flow Task.

Reference Link

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: