SharePoint and Reporting Services Integration, the good, the bad, and the ugly

/

 

Benefits of using Reporting Services in SharePoint integrated mode

  1. Version Control in SharePoint (vs. BIDS project saved in TFS).  Having version control for reports is critical, especially if you allow end users to use Report Builder.  Using BIDS with TFS is fine.  But if you are directly using Report Builder with Report Server native mode is terrifying.
  2. Check-in / check-out
  3. Draft/publish versions
  4. Alerts (when someone updates reports)
  5. Workflows (though to be honest - I have yet to see a business reason to use workflow for .rdl)
  6. If you are using SharePoint to surface your reports, the integrated webparts makes sense.  Though with reporting server in native mode you can still use a Report Viewer webpart via IFrame.

Same / Similar functionality

  1. Report Subscriptions are handled by RS
  2. URL: Report Server or SharePoint document library
  3. Permissions control either in Report Server, or in SharePoint

Problem / suggestions

  1. To use integrated mode you _do_ need to have SharePoint somewhere.  Sometimes a company may only have a database and don't even have IIS on the database server.
  2. But if you use native mode, and need some sort of source control, you'll need TFS.  So may be install SharePoint is more useful than TFS for a power user / BI Expert.
  3. A reporting service can only connect to one SharePoint - you can't join two different farms (e.g. dev sp and prod sp).  This was a concern during migration to SP2010 - some reports are for intranet and some for public…  can't move one or another.
  4. Report database cannot switch modes between native and integrated - so migrating reports is a pain in the butt - you need to export old reports, create a new report database and choose integrated mode, then add them back in.  A bit painful I think.
  5. The UI for setting up BIDS project to export to SharePoint integrated mode is REALLY crap.  I have no mercy for this crap dialog:

clip_image002

clip_image004

 

The trick

Native mode

  • TargetServerURL: http://Server/ReportServer
  • TargetDataSourceFolder: /Data Sources                           <- OK.  Deploy no problems.

SharePoint mode

  • TargetServerURL: http://server
  • TargetDataSourceFolder: /Documents/Data Sources           <- wrong!  
  • TargetDataSourceFolder: http://server/documents/Data Sources/   <- correct.  Need entire URL.  WHO THE HECK KNOWS THIS WITHOUT THE TOOLTIP?

If you don't set up the folder properly as per example.  BAM deploy fail no proper errors.  Just says no permission to path or some irrelevant rubbish - sends you in circles thinking you stuffed up SharePoint…