Pogo69's Blog

December 1, 2012

SSRS Reports in CRM 2011 with SQL Server 2008 (pre-R2) – Implications for Managed Solutions

Filed under: Cutting Code — pogo69 [Pat Janes] @ 05:53

Overview

I am writing this blog posting as a warning to all regarding the deployment of SSRS Reports into a CRM 2011 Organisation wherein the SQL Server instance is pre-version 2008 R2.

SSRS 2008 “Compatibility” in BIDS

If your SSRS Report is created in SQL Server 2008 R2 BIDS (while CRM 2011 supports the use of SQL Server 2008; most CRM 2011 deployments are, to my knowledge and certainly in my experience, running SQL Server 2008 R2), you will have an option when creating the Report to set “compatibility” to:

  • SQL 2008 R2
  • SQL 2008
  • SQL 2005

This forces BIDS to store your report using the corresponding XML Schema.

You could reasonably assume that selecting SQL 2008 would produce a report that will upload without error into a SQL Server 2008 Reporting Service deployment.  Unfortunately, if you add a Chart to your SSRS Report, such is not the case.  Uploading the report will fail with an “index out of bounds” error.

Implications

I first discovered this issue while attempting to import the PowerMailChimp solution into a client’s CRM 2011 Organisation.  The client has on OnPremise installation using SQL Server 2008 (pre-R2).

I checked (and re-checked) the schema definition in the offending Report (2008) and that the Report conformed to the SSRS 2008 schema (it did).  But it still would not import.  If I removed the report from the solution; the solution imported without error.

After a very frustrating session of Googling and experimentation, I confirmed and reliably reproduced the above error.  If you create a Report in BIDS 2008 R2, setting compatibility to SQL 2008 is not sufficient to ensure true compatibility if your Report contains a Chart.

Conclusion and Recommendation

If you are packaging a Managed Solution for distribution to an arbitrary CRM 2011 deployment that contains SSRS Reports, you must either:

  1. Create your Reports in an SSRS 2008 (pre-R2) deployment

Highlight to potential Users that the solution is compatible only with SQL Server 2008 R2+

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: