SharePoint and SQL Server Reporting Services in Local Mode

Update 2014-06-01: Made structural changes.
Update 2013-07-18: Cleaned up info about generating dynamic CAML.
Update 2013-06-10 / 2013-06-18: Added info about using custom code in the query expression.

When your reports are based on SharePoint lists, why would you need SQL Server to show those reports? So local mode for Reporting Services (SSRS) which was introduced with SQL Server 2008 R2 is a straight forward idea.

Shortcomings of Local Mode

Local mode works for reporting from a list. Everything else works while previewing the report in Visual Studio but falls flat on it’s face as soon as you deploy:

  • Both sub-reports and shared datasets (after creating and deploying an appropriate .rsds file) lead to “file not found” errors.
  • Listing possible parameter values from a query doesn’t work: “One or more parameters required to run the report have not been specified”. The report works as soon as you specify the same values in the report itself.
  • You cannot use connection type “XML” without integrated mode either (Error “An attempt has been made to use an unregistered data extension, ‘XML’. To use this data extension, it must be registered in the Web.config file of this Web application”).

I guess those “advanced” features need some of the infrastructure you get when running in integrated mode but haven’t found anything official yet.

Solution Part #1: Your Own Report Viewer Web Part

We were on SharePoint 2013 but stuck with SQL Server 2008 R2 for the months to come, so integrated mode was out of reach. While we could live without sub-reports the only solution for the parameter problem was to write our own Report Viewer web part. That web part shows a URL like this in an IFrame – as Microsoft’s ReportViewer web part does:

http://MySite/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/MyLibrary/MyReport.rdl&rp:MyParameter=MyValue

Note that only few of the Report Viewer parameters like rv:ReportRelativePath and rp:<MyParameter> are supported. But you cannot hide the parameter pane for example (rv:ParamMode=Hidden). You’ll have to hide all the parameters to make the parameter pane disappear.

Solution Part #2: Dynamic CAML

Dynamic CAML took care of local mode’s other shortcomings. To create dynamic CAML use custom code in your query expression. I ran out of time before I could get it working but embedded code did work and using a custom assembly only failed because of a 32 bit / 64 bit mismatch of a referenced assembly. These steps should work:

  • convert your query to an expression
  • from that expression, call custom code either in your report or in an external assembly
  • in your custom code generate the CAML you need including lists of dynamic values and JOINs

If that’s not enough you can write your own Data Processing Extension.

Whether it’s a good idea to use SharePoint as a relational database is a different question.

External Resources

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

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