SharePoint (and Project Server) Shenanigans

So the first phase of the project I have been on for the last year is starting to move into the closing phases.  The information architecture is in place, most of the custom development is complete and mapped to the project lifecycle / business processes of my client.

The project takes data from multiple places including:

• SAP (Resource, cost rate, metadata and product contract information)
• Project Server 2010 (Timesheets, Plans, Project / Task / Assignment data)
• SharePoint 2010 (Risks, Issues, Actions, Documents etc.)
• Custom database (Snapshot data)

I’ve learnt a lot about SharePoint 2010 and Project Server 2010 since I started this project (pre public beta).  So now that we have all this data, the time has come to bring it all together with Business Intelligence dashboards and UI design.

Once of the main tools we are using for this is Reporting Services in SharePoint Integration mode and although I have lead projects involving SSRS in the past, actually getting my hands dirty and creating reports myself is something of a novelty.

However, time and resource pressures have meant that I have had to setup the SSRS infrastructure, development environments and test reports to ensure that the data can be utilised in the required way.  As part of this setup I am also training my client on the business intelligence studio interface and deployment of reports in to SharePoint environment.

So getting to the point of this post, below is a list of useful links, findings and discoveries I have made during this setup process this week to serve as a reminder to me and will hopefully help others too.

Reporting Services Web Part – Firefox Compatibility (Windows, Linux – 3.6.13)

• Turn off Asynchronous data (Edit Web Part Settings > View Section)

NB: If this setting is left to its default, the loading screen does not go away until you hit the refresh button on the Web Part toolbar.

SharePoint Integration Mode – Linking to pages within SharePoint

It would appear that the Reporting Services web part in SP2010 disables links where the target URL is within SharePoint itself (“/pages/default.aspx” for example).

This blog post suggested a useful workaround:

• http://weblogs.asp.net/mnissen/archive/2009/03/26/reporting-services-does-not-display-hyperlink-for-placeholder-with-go-to-url-action.aspx

However the suggested approach was good if you had a definitive URL such as default.aspx, but if you wanted to add a query string parameter based on the data returned, adding as action directly to the image did not allow me to select the appropriate date.

To get round this, I added a calculated field to the data set using the same process suggested in the post:

="javascript:void(window.location.href='/pages/projectdashboard.aspx?projectid='" + dataset.fields[<field name>] +")"

NB: Since working this out, I have extended it to use a standard JavaScript function that is running from the Master Page to make links in the reports more generic.

Then for the action on the image I was able to select “Go to URL” and select my calculated field.

This doesn’t work in the designer preview (or any exports of the report), however it works a treat when you are using the report as part of a dashboard with drill down features.

Interactive Column Sorting

Another useful blog post:

• http://weblogs.sqlteam.com/joew/archive/2008/08/02/60666.aspx

Although the instructions are for SSRS 2005, it is still relevant today.

• Right click on the column header field
• Select Properties
• Interactive Sort Tab
• Select the appropriate sort field
• Click OK

Alternating Row Colours

Always a popular choice for tabular based reports.

Highlight the data row and under the properties pane for the background color, use the following expression:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#F7F7F7", "#FFFFFF")

MSDN Documentation Links

• Support Features for SharePoint Integration Mode: http://msdn.microsoft.com/en-us/library/bb326290(v=SQL.100).aspx
• Reporting Services Features: http://msdn.microsoft.com/en-us/library/ms159273(v=SQL.100).aspx
• Tutorials: http://msdn.microsoft.com/en-us/library/bb522859(v=SQL.100).aspx
• Design Guidance: http://msdn.microsoft.com/en-us/library/ms159734(v=SQL.100).aspx
• Designing and Implementing Reports Using Report Designer: http://msdn.microsoft.com/en-us/library/ms159253(v=SQL.100).aspx
• Development Topics (High Level): http://msdn.microsoft.com/en-us/library/bb522683(v=SQL.100).aspx
• Report Builder Information: http://technet.microsoft.com/en-us/ff657833.aspx
• How to publish a report to a SharePoint Library: http://msdn.microsoft.com/en-us/library/bb283155.aspx

NB: Although a lot of the above links are for SSRS 2008, there is an option at the top of the page to make the content relevant for SSRS 2008 R2 also.