SQL Server 2008 Reporting Services Integration with SharePoint 2010

I have been working with a Virtual Machine running SharePoint 2010. It has SQL Server 2008 Enterprise Edition but it did not have SSRS installed. I therefore downloaded the 64-Bit version of SQL Server Enterprise to install the extras.

I went through the process and when I saw some options disabled I figured something was wrong:

The SharePoint options were disabled. I couldn't select Native or Integrated mode here. I realized that it was looking for MOSS 2007.

I therefore realized that SQL Server 2008 R2 probably has the SharePoint 2010 awareness. I immediately downloaded the SQL Server 2008 R2 Enterprise Evaluation and installed it on the VM. This time I was able to configure Reporting Services properly:

After downloading and running through the installation of SQL Server 2008 R2, I realized that the process for installing Reporting Services is essentially the same as before (with SQL Server 2008 making it a little easier than SQL Server 2005). This means that you still need to install the SharePoint Add-in as well.

So what you need are both parts:

SQL Server 2008 R2 November CTP

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx
(I used the download from MSDN but it is publicly available using the above link)

SQL Server 2008 R2 RS Addin for SharePoint

http://www.microsoft.com/downloads/details.aspx?FamilyID=16bb10f9-3acc-4551-bacc-bdd266da1d45&displaylang=en

After you install the Add-In for Sharepoint, new options appear in Central Admin (just like in MOSS 2007):

Clicking on the Reporting Services Integration allows you to complete the integration process:

But it appears now can specify which site collections to deploy and activate the features. I had a few other site collections on the VM but under the /sites/ managed path. I am therefore thinking that it is really asking you to select the Root Site Collection (so if I had a another web application with a site collection, that would be shown in the selection box as well).

After successfully integrating, a Reporting Services option appears under the site collection site settings:

That's about as far as I have gotten so far. I did, however, wanted to check BIDS to see what Data Sources were available. I had a hunch that a SharePoint list would now be an integrated option (instead of having to create an extension as I have done before in SQL 2005). I was right:

I am pretty sure that wasn't in there before I installed R2.

So overall in SharePoint 2010 it's the same process to install Reporting Services in Integrated Mode but with the SQL Server 2008 R2 installs.

SSRS 2008 Pie Chart Formatting

I was involved with a client where after building the warehouse to host the data, one of user deliverables was delivering a SSRS report that contained many different types of charts. One of the charts that had a wrinkle to it was a Pie Chart that broke down customer dollars by category and then comparing that customer to its industry to see the percentages of where the client's customer income was in relation to the rest of the industry that the customer belonged. For this report, the customer was a parameter and it would display the appropriate information based upon the customer selection by my client.

Building two Pie Charts and having them side by side on the SSRS Report to display these percentages and dollars was a great way to compare the results but it had a couple of formatting issues that I would like to share.

For the requirement, I needed both charts to have the same colors representing the same category in each chart. I could have hard coded the categories but then the report would not have been very flexible if new categories were introduced into the data down the line. To solve this problem, I had to display all of the category types (simple left join) in the legend for the industry and the customer charts. Originally, I only showed those categories that each belonged to but if a customer only was involved in a subset of what the total industry would be involved in, the color coding of the charts would not be in synch. The first requirement was now solved using the left join but now I had a new problem.

Adding the left join now meant that I was retrieving all categories that could have a percentage of 0 since not all customers were involved in every category that the customer's industry was mapped. When displaying that on the chart and trying to show percentages and dollar amounts, if there were multiple categories that a customer did not have, all of the 0 percents and dollars became one garbled mess of characters.

Luckily, in SSRS 2008, one can change the formatting of the Pie Chart's Label Series using Keywords and combining that with a numeric string format. The keywords can be found in the Adding Chart Keywords section in the Formatting Data Points on a Chart article on MSDN, the numeric strings can be found at the Custom Numeric Format String section on MSDN.

To go to the Label formats, right click on the Labels within the chart and select the Series Label Properties:

The properties will display and in the General option, implement the Chart Keywords and a custom numeric format string combination:

For the example, I implemented the following format: #PERCENT{#%;;""} #VALY{$#,###;;""}. Notice, after the keyword, the format is laid out and the Section Separator ";" splits the following types of values into "POSTIVE;NEGATIVE;0" and what format to apply to each type of numeric value.

For the 0 value, I put in an empty string and therefore the combining of empty categories did not display in the Pie Chart itself but the category would still show up in the Legend.

The Pie Chart also has the capability to group percentages together below a certain threshold (percents or dollars) to then allow for a custom title for that category. However, for the customer vs the industry scenario and the color coordination of the legends, this avenue would not apply since I was not guaranteed the same combination of categories for the industry would be setup for the customer, which was the point of the chart in the first place.