Integrating SQL Reports into SharePoint for CRM

SharePoint is a collaboration product that has been growing in use and market share for a number of years, as it works to tie multiple products into a single place as a integration and presentation tool for day-to-day use.

SharePoint can be used to integrate a variety of Applications, Custom Code and Data Sources in various different methods – however as SharePoint is one of Microsoft’s flagship products, other Microsoft Applications have certain built-in hooks to allow the Application to be used via SharePoint.

Since SQL Server 2005, the Reporting Services aspect of SQL includes one of these hooks to allow Reports to be published through SharePoint as way of presenting and using any reports developed for Reporting Services. From a CRM view of world, this allows various custom SRS Reports to be presented via SharePoint as flexible Dashboards providing analysis of CRM Data.

The aim for this post is to describe how to activate this hook between SQL Reporting Services and SharePoint as a prerequisite to designing and building such Dashboards. However this concept of hooking SQL Reporting Services into SharePoint is of particular interest at the moment, as CRM 2011 will have a similar method for hooking Dynamics CRM into SharePoint.

The initial homepage for SharePoint 2007 after a fresh install of the Enterprise Edition

Once we have SharePoint (in this case, 2007) installed – we can create a new simple Team Site or Blank Site to use for our SRS Reporting. This Site will then require an initial Document Library to store the SRS Reports we wish to publish to SharePoint.

Screen showing a new Site in SharePoint 2007 with a simple Document Library

With this in place we can look into our SQL Server Deployment to configure SQL Reporting Services to communicate directly to SharePoint – we do this by running the ‘Reporting Services Configuration’ tool within the ‘Configuration Tools’ area of SQL Server.

The Reporting Services Configuration wizard for SQL Server 2005

Within this wizard, the first step is to ensure that the Reporting Services Database is set correctly for SharePoint integration by running in SharePoint Integrated Mode as opposed to the default Native Mode. This involves accessing the ‘Database Setup’ area of Reporting Services Configuration:

The Database Setup screen within Reporting Services Configuration, allowing us to set the Reporting Services Database to be ready for integration to SharePoint

This Database Setup area then allows us to click the ‘Change’ button to change the current Reporting Services Database from Native Mode:

Accessing the Change Database Mode screen within the Database Setup area of Reporting Services Configuration

From here we can specify to create a new Database to use for Reporting Services which is ticked for SharePoint Integrated Mode and then Apply the changes.

After changing the Database Setup to Share Point Integrated Mode and applying the changes to SQL Reporting Services

With this step done, we can then configuration how this connection between SRS and SharePoint will operate – by clicking into the ‘SharePoint Integration’ step of the SQL Reporting Services Configuration wizard, we will be provided with a link that will open a new browser into the SharePoint Central Administration site.

Before opening this link, we should download and install the Reporting Services addition for SharePoint from the following link (depending on the version of SQL Server being used):

Reporting Services 2005
Reporting Services 2008

This is a simple MSI file which will download and install on the SharePoint Server to install the required Web Parts and Configuration options into SharePoint.

Installing the Reporting Services Add-in for SharePoint

With this installed, we can hit the link to open up the SharePoint Central Administration site to configure the link between SharePoint and Reporting Services:

The SharePoint Integration step within Reporting Services Configuration after the Database Setup has been changed from Native to SharePoint Integrated

From here, we can browse into the Application Management area to see the following list of options for configuring the SharePoint deployment – the key here is that installing the earlier Reporting Services add-in for SharePoint should have added a Reporting Services section into this list:

The Application Management area inside SharePoint Central Administration after the Reporting Services add-in has been installed

Clicking the Configure Integration option will then allow us to specify the details of the Reporting Connection into SharePoint, notably the website that SRS is currently using as it’s ReportServer Website and whether to use Windows Authentication or a Trusted Connection. For CRM purposes, we should always use Windows Authentication here to preserve the single sign-on security model between MSCRM, SRS and SharePoint through a user’s Active Directory (i.e. Windows) Authentication.

Configuring the Reporting Services Integration within the Application Management area of SharePoint Central Administration

With these steps done, we now have the two systems integrated together such that we can Publish SRS Reports directly into the Document Library we created earlier in SharePoint. If we open up a Reporting Project in Visual Studio (again either 2005 or 2008 depending on the version of SQL Server involved) and edit the the Properties of the Reporting Project, we can specify the SharePoint Web Site and Document Library to publish the report to.

Publishing the SRS Report from Visual Studio to SharePoint

The parameters here for configuring the SRS Project to be published into SharePoint go as follows:

Deployment Property	Description	Example
TargetDataSourceFolder	The URL Location of the Document Library in SharePoint that the Report will be published to	http://crmserver/dashboard/SRSReports
TargetReportFolder	Also the URL Location of the Document Library in SharePoint that the Report will be published to	http://crmserver/dashboard/SRSReports
TargetServerURL	The home URL Location of the SharePoint Site involved	http://crmserver/dashboard

We now have the Report published to SharePoint and presented to any users who are accessing SharePoint through the Document Library, in the same fashion as the user would access a Document or file in SharePoint.

Viewing the Published Report within SharePoint as an item in a Document Library

The Published Report being viewed through SharePoint

This is not a vast improvement from the default behaviour of Reporting Services yet – which similarly gives each report a URL that user’s can access via Internet Explorer to access the report, however this does link the report into SharePoint alongside any other information held in SharePoint to start making the SharePoint Site a ‘one-stop-shop’ for information relevant the user’s accessing the site.

However the real power here is to then take the Report held inside SharePoint and use SharePoint’s Dashboard Pages and Web Part Pages to present this Report inside different pages in SharePoint as way of incorporating the report into different Dashboards, potentially with different filtering criteria to present the report differently for different dashboards as way of filtering for the audience who is likely accessing the page.

We can do this by add or using an existing Document Library in our Site to hold a number of Web Part Pages, and then creating each Web Part Page as a Dashboard to host the report we have added into SharePoint.

Adding new Content to a SharePoint Site - in this case a new Web Part Page to act as a Dashboard

Adding a Web Part Page to a Document Library in SharePoint to act as a Dashboard - choosing the title and layout for the Dashboard

We can open up this Dashboard Page and click ‘Edit this Page’ to begin populating the proposed Dashboard with Web Parts.

Customising the Web Part Page in SharePoint to build the Dashboard

From here, we can add a new Web Part to represent the report we have uploaded via adding a ‘SQL Reporting Services Web Part’ to the page.

Adding a Reporting Web Part to show our Report within the Dashboard

Then customise how we want this report to appear within the Dashboard by configuring this Web Part.

Configuring the Reporting Web Part for the Dashboard we are building in SharePoint

Crucially we can click the ‘Load Parameters’ button to instruct SharePoint to read back the list of expected Parameters (only the non-hidden parameters however) into the configuration of the Web Part to then populate values for these parameters.

Configuring the Web Part to show the Report with a particular Parameter value

Here we are instructing the Web Part to show the Report with a hard-wired value within the parameter – such that the parameter for ‘CRM_URL’ will always be set to this for this Dashboard. In this case the Dynamics CRM Reporting Drilldown URL for the Dynamics CRM Deployment that we are reporting upon.

http://crmserver:5555/CRMReports/Viewer/DrillOpen.aspx

This allows for Dynamics Drilldown from the Report within the Dashboard into Dynamics CRM, further allowing this Dashboard to act as a home-page for the user however this could also be different filtering parameters varying from different Dashboard to Dashboard in order to make each Dashboard tailored for the intended audience without re-coding the underlying Report.

In this case we are looking at hard-wired values entered into the Reporting Web Part, however this can be taken forward to allow for specific Choice Web Parts that supply a user’s choice to the Reporting Web Part to allow the user to control how the Dashboard appears at run-time – this will be subject of further posts on this topic in the future.

However once we exit from edit mode, the Dashboard should then appear as a simple page hosting the report with the hard-wired CRM_URL value – we can then specify the Reporting Web Part to hide or collapse the other areas of the Web Part that the end user does not require:

The Resulting Dashboard Appearance for the end user

Which gives us a simple CRM Dashboard presented via SharePoint.

Obviously this is a very simple example of a single report being hosted via SharePoint – but this concept can be taken forward to produce numerous flexible Dashboards that render different groups of SRS Reports that can then be further filtered by or for the intended Dashboard audience. So whilst this requires a number of different software products and technical knowledge to implement initially, this can be taken forward via no-code customisations to provide additional Dashboards more carefully filtered towards the end-user.

Dashboards have long been a key requirement for day-to-day use of CRM as way of giving user’s a Homepage to such solutions that is specifically relevant to them, and this technique of incorporating CRM, SRS and SharePoint together provides the ability to do this – particularly when we begin to look at having a suite of relevant SRS Reports and use of the Dynamics CRM Web Parts.

This concept of Dashboarding will be embedded as a core feature within CRM 2011 as will the concept of using SharePoint and CRM side-by-side, is a good time for CRM Consultants, Developers and Project Managers to familiarise themselves with the various Dashboarding options and technologies that are available. I will be endeavouring to post further on both how we can take this concept of SharePoint Dashboards further and also on the other methods such as the use of Silverlight and the Qlikview product.