Yummy Reporting Services / SharePoint Integration in SQL Server 2005 SP2


I attended TechReady3 last week, and one of the most interesting sessions focused on the work the SSRS team has done around Reporting Services / SharePoint integration in SQL Server 2005 Service Pack 2 (due next year).


 


As you know, we already have SharePoint Web Parts, and they work well. However, Microsoft will include much deeper integration with MOSS (Microsoft Office SharePoint Server 2007) in SP2.


 


After installing SP2, you can run Reporting Services in “native mode” (what you do now), or “SharePoint integration mode”. You get some pretty cool functionality in SharePoint integration mode, but it does come at a cost.


 


Management


 


Anyway, you’ll configure SharePoint integration mode using the SQL Server Reporting Services configuration tool — In SP2, the tool has been modified to include some additional UI to do this work. The SSRS WMI interfaces have also been extended, which goes without saying.


 


Once in SharePoint mode, most of the objects you’re used to seeing in reportserver db are instead stored in SharePoint. Reports, data sources, models and resources all “live” in SharePoint. Metadata about subscriptions and a few other pieces of functionality (schedules, caching) stay in the reportserver database.


 


In SharePoint Integration mode, all management functions go through SharePoint. This is good and bad, in my opinion. For example, since your reports are now stored in MOSS, you get document versioning (hooray!) and other MOSS features such as workflow and collaboration.


 


However (The bad news), in SharePoint integration mode, the tools you’ve grown accustomed to using to manage SSRS will no longer function: Report Manager and SQL Management Studio won’t work anymore…ALL administration (uploading, rendering, and delivering reports) happens in MOSS. You cannot run your SSRS instance in “dual mode”, by the way – you must choose either native of integrated mode. There also isn’t an automated upgrade path between native mode and integrated. If your reports are already sitting in the reportserver database, you’ll basically need to export them (or write code to do so) and then import them into an instance of SSRS running in SharePoint mode.  VS 2005 and BIDS will be able to publish directly to MOSS, which is a good thing.


 


From a management perspective, another major change while in integration mode is around security: If you run in SharePoint integration mode, MOSS handles Reporting Services security. Again, this is good and bad: you get a single, consolidated place to manage security, but you’ll lose any investment you’ve made in SSRS Web Service security and deployment code you’ve written. Since MOSS does security differently than SSRS does, the SSRS Web Service APIs for security are no longer leveraged. Bummer! Finally, you won’t get complete feature parity while in SharePoint integration mode — Linked reports, data driven subscriptions, and job management (does this mean no creating schedules? I don’t know) are not available in SharePoint mode. I find the loss of data driven subs particularly painful as it is a really powerful feature. As a consolation, the Microsoft team has written a new SharePoint delivery extension to allow you to deliver reports right to the SharePoint library. You also can launch Report Builder from inside MOSS.


 


Presentation


 


Viewing reports while in integrated mode is really nice, and will be second nature to anyone already using the existing web parts. You’ll be able to view reports in either a “full page” view or inside Web Part pages. (This next part is really cool). Our Web Part controls will support Row and Filter consumers for specifying parameter values via filter Web Parts. What this means to you is you could drop two (or more, I guess) SSRS report Web Parts on a page which each display a different report filtered by date, and then filter them both using the same filter dialog. Very cool! You could even display / filter an SSRS report and Excel workbook using the same filter. Nice.


 


For displaying controls outside of MOSS, the Report Viewer controls and URL Access will continue to function, except that you’ll point to your reports inside the MOSS document library instead of out on http://somebox/reportserver?/path/to/report. When using URL Access you’ll still be able to specify parameter and deviceinfo settings.


 


Programmability


 


Microsoft adds a new SharePoint SOAP endpoint for use while in integration mode. This endpoint will REPLACE the two you’re used to, so you’ll need to refactor your code a tiny bit. From what I saw, the methods you’ve grown to know and love like Render() and RenderStream() work just like they used to, but you’ll call them from the SharePoint endpoint rather than from where you’re used to. This will require you to update a few project references, but no big deal, really.


 


Summary


 


You know all that I know now. The team said they are working on getting us complete feature parity between running in native and integrated mode, but it won’t happen in SP2. Once we’re there, integrated mode is a no-brainer. I think that integration mode will be very popular for new deployments of SSRS and for existing (less complex) workgroup deployments. I’m not so sure about large existing enterprise deployments – there seems to be a high cost to pay around administration.

Comments (40)

  1. Carolyn Chau says:

    FYI…Schedules and scheduling will still be supported in integrated mode.  This is a great summary of the RS integration.

  2. Zeev Shilor says:

    I have two questions regarding SharePoint interration. Why only MOSS 2007 is mentioned? Wouldn’t the features you described work with WSS 3.0 as well?

    Also, I couldn’t figure out if SSRS lets or will let us create reports diretly from WSS lists as Excel or Access 2003 can. (Without or with minimal programming)

    Thanks, Zeev

  3. Nope, only MOSS will support this. No, as far as I know you will not be able to report directly against lists unless you want to create your own custom data extension to do so.

  4. SQL 2005 Reporting Services Add-in CTP per Sharepoint 2007

  5. SQL 2005 Reporting Services Add-in CTP per Sharepoint 2007

  6. Sureshquest says:

    Has anybody tried to install SQL SP2 + MOSS 2007 in a domain controller, after i install the Add-in , MOSS give error to configure the "grant db access" part

  7. Matej Kocbek says:

    Have anybody install SQL RS 2005 and SharePoint 2007 on same machine? I have a problem with step Add Reporting Services Content Types in document http://download.microsoft.com/download/f/2/5/f250ed72-c102-4216-8653-63189e24fa02/readme_rsaddin.htm. I don’t see Reporting Services in drop downl list.

  8. I’ve installed it that way. As I recall, I didn’t see all the content types either, but it worked anyway. I suspect this is just a "CTP thing".

  9. Sumitkute78 says:

    I have installed SharePoint 2007 and SQL Reporting 2005. I want to set up SQL Server in SharePoint Integration Mode. With the use of Reporting Service Configuration i try to create ReportServer Database with "Create report server database in sharepoint intergration mode" Checkbox checked. I am unable to set the Mode to Sharepoint Integration. This is creating a problem when i try to access report server from Shareponint Portal.

    Please help.

  10. Sumitkute78 says:

    Hi,

    I got my problem solved. I was not appying the changes once creating and updating the database in sharepoint intergration mode.

    Thanks

  11. Sumitkute78 says:

    Hi,

    I am working on sharepoint 2007 integration with reporting service 2005. There are few facts which i am trying to understand and need some clarifications. Please provide your comments

    1) While configuring "Database setup" in Repoting Service Configuring Manager" i found two modes 1) Native and 2) SharePoint Integration. When creating a report server database if we check "Create the report server database in Sharepoint Integration Mode"  then it set the mode to "SharePoint Integration Mode". There is no option of switching the same database to native mode. So i have to create another database in "Native Mode". (IS THIS CORRECT. DO WE NEED TO CREATE TWO REPORT SERVER DATABASE SEPERATE FOR SHAREPOINT INTEGRATION AND NATIVE)

    2) I am using the new report viewer web part in sharepoint 2007. When i am in sharepoint integration mode i can create datasource, report model and with report builder i can create rdl file. On selecing any RDL file in doument library and it gets displayed in Report Viewer Web Part. When i switch to Native mode i want to configure the web part to use the report created with SQL Server Bussiness Intelligence Dev Studio and deployed on http://localhost:808/ReportServer. (IS THERE ANY WAY SO THAT I CAN EXPORT AND IMPORT ALL .RDL FILE FROM http://localhost:808/ReportServer to http://localhost/ReportServer)  

    I AM NOT ABLE TO CONFIGURE WEB PART WHEN I AM IN NATIVE MODE i.e giving path of .RDL in report textbox throw a error saying "Item not found" i tried all combination. THIS can be related to datasource not reading in .RDL file in native mode while confuring report viewer web part.

    http://localhost:808/ReportServer (Report Server)

    http://localhost/ReportServer (SharePoint Mode) (Default Top Level Site)

    3) LAST : I want to create a .RDL file report from the data in sharepoint, say lists or news etc. So when creating a RDL file how can i connect to sharepoint database. I read some where that in previous release they used to DATA EXTENSIONS and get the data from list in sharepoint with something like http://<servername>/<sitename>/List=Announcement when creating RDL file. Is the alternative in new release to create the report based on sharpoint list data.

    Thanks and i would really appreciate your help

    1. You can only run SSRS in one mode: Native, or SharePoint Integrated…so you only create one database…

    2. No, there is no migration mechanism. You will have to republish all reports

    3. There is no direct support for this. You would need to create a data extension which reads info from a SPS list and returns that info to SSRS as a rowset. Here is a sample that a good citizen created: http://www.teuntostring.net/blog/2006/03/update-reporting-over-sharepoint-lists.html

  12. Martin says:

    I think you can report on sharepoint lists, by using the XML data provider.

    Martin

  13. You are right! While you’d have to create a hellacious <ElementPath/> when using the XML DP, I bet it would work!

  14. Kevin says:

    Hi all , very nice site! Thank You !

  15. Carl Strang says:

    As soon as I enabled integration mode, I lost the ability to access sharepoint admin site.

    I get a logon box, but can’t get past it.

    Reporting services says it could be an issue with the service account, but I am unaware of how to config the perms for that differently.

    Any thoughts

  16. Mark McPherson says:

    I successfully installed SP2 and the Sharepoint AddIn. The SSRS database is in integrated mode. I can create Datasources and reports. When I try to view the report I get the following error: The remote server returned an error: (403) Forbidden.

    I can do everything but view. Any assistance would be appreciated.

  17. Stephen Ellis says:

    Has anybody tried to get this to work using Forms Authentication?  

    I’ve got it all setup on my farm but when I try to access a report in a document library (by clicking it), the resulting url states "The item ‘http://moss:8003/Reports/Company Sales.rdl’ cannot be found. (rsItemNotFound)".

    This is coming from the following URL:

    http://moss:8003/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/Reports/Company%20Sales.rdl&Source=http%3A%2F%2Fmoss%3A8003%2FPages%2FReports%2Easpx&DefaultItemOpen=0

    If I try to same report with Windows Integrated mode, it all works.  Has anybody gotten Forms Authentication to serve up reports in SharePoint Integrated mode?

  18. Kyler says:

    I am trying to use SQL Server 2005 sp2 Reporting Services in Integrated mode with WSS 3.0.  When I am in Reporting Services Configuration Manager on the Sharepoint Integration tab, I get the following error message…

    The report server cannot access settings in the Sharepoint configuration database.  Most likely, the Windows Sharepoint Services object model is not installed…

    Reporting Services is running on a separate machine from WSS 3.0.  Do they need to be on the same machine???  Do I need to install WSS on the Reporting Server just to get the object model?

  19. Klyer, you must at least install the SPS/WSS web front-end on your SQL box in order to lay down SPS object libraries which SSRS utilizes. Make sure you install the same version/SKU of the product as you have installed on your "real" SPS/WSS box.

  20. Nick Barclay says:

    Hi Russel,

    I’m having problems passing report parameters in the URL (migrated to WSS). Apart from the way we address the report itself is there something different in the way we need to pass parameter values in the migrated environment? I’m sure I’ve just misplaced a slash or question mark but I just can’t figure it out.

    Cheers,

    Nick

  21. Forscius says:

    Hi Russell,

    We have just started using reporting Services in Integrated mode. However I have two problems. The drill through action no longer works. I am getting a "the item ” can not be found (rsitemnotfound) error". What could be the problem, it was working prior to the upgrade. Second, how can we hide reports? This is a functionality that is very important to us as we only want to show the user the main report and not all the other drill-through ones.

  22. Ahmad EL KAAKI says:

    Hi there,

    I want to pass parameter dynamicly to the report… depending on the logged in user

    how can I do it in MOSS 2007?

    Thank you in advance

  23. Guy says:

    Hi

    I have a MOSS 2007 site set up on "localhost" and RS 05 is running in native mode, publishing to localhost:8080/reportserver. I want to be able to view the reports in sharepoint webparts on the site.  I have seen several posts saying this is possible but none step through how to do it.  (i do not want to use integration mode)

    I am using the ReportViewer webpart and typing in the address of the reports to point at –

    http://localhost:8080/Reports/Pages/Folder.aspx?ItemPath=%2fexample&ViewMode=List

    but this gives the error:

    The path of the item ‘/localhost:8080/Reports/Pages/Report.aspx?ItemPath=%2fexample%2fReport1’ is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

    No matter how i change the path name i cant find the format to put it in that will make it work.  Any ideas?  if i remove all the characters like ‘:,%,&’ etc then the error changes to "item not found", as this is now obviously the wrong address.

    Any help would be much apreciated!

    Guy

  24. Guy, I’m not exactly sure how you are using the RS viewer controls, so I don’t know where you are plugging in the value you specified. However, it is not a true URL Access query string. Try something like this, instead:

    http://localhost:8080/reportserver?/SomeFolder/SomeReport

  25. Tom Bizannes says:

    We are finding that this solution has a few problems.

    Has anyone tried printing landscape?

    Also the Jump to links don;t work if they point ot a list inside SharePoint.

    Yes we are using elementpath with the XML datastring inside reporting services to report on SharePoint lists and web services running on sharepoint via reporting services but the jump to link option only works if you wrap javascript around it which is clunky and no good for subscribed reports!

    Let us know whether anyone else has the smae problem?

  26. bob says:

    Did anyone find an answer to this:

    I want to pass parameter dynamicly to the report… depending on the logged in user

    how can I do it in MOSS 2007?

    Thanks

  27. Scotty says:

    ANy hae problems with scheduled delivery of reports with excel attachment generating with dual extensions. ie.. Excel file is attached as:  report.rdl.xls  

    Please help..

    Scotty

  28. Kyler says:

    Tom-

    I am having the same problem with Reporting Services reports not rendering ‘Jump to URL’ links if the link points to a page on the (Reporting Services integrated) Sharepoint.  I have logged a feedback case with Microsoft.  It would help if you go to https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=290191 and tell them you are facing the same issue.

    You mentioned you can work around it with javascript.  Can you elaborate?

  29. What I found, in regards to the rsItemNotFound is that the SharePoint integration mode, if you’re building the navigation dynamically, requires the addition of ".rdl" to the report name.

    So, basically I have a piece of code that checks a hidden parm that get’s set on deployment – just returns ".rdl" and concatenates the full report name. Weak solution, but clearly an oversight.

  30. Sher says:

    Hi,

    I have a very unique situation and trying to find an integration solution.

    We are running SSRS in the Sharepoint Integration Mode. Some users will generate and view their reports in Sharepoint. so, thats fine.

    We also have a custom ASP.NET application where we want to have a highly customized user interface to setup report parameters for the SSRS Report and allow users to run reports from there without having to go into sharepoint.

    Initially, before we switched to integration mode, I was using the ReportViewer control in my ASPX page to load reports from ReportServer in Native Mode.  Now, after we switched to the integrated mode, I am not able to run the reports. I do not want to use URL access method to pass parameters because of the length restrictions. Instead I want to use the

    ReportViewer.ServerReport.SetParameters(). That worked great for native mode but not sure how to get it working for integration mode.

    1. Is it possible to access reports running in sharepoint integration mode from the VS.NET ReportViewer control ?
    2. If not, is having the reports hosted on REPORTSERVER in the native mode the only solution for us ?

    3. Is there another way to create a custom report parameter setup interface that can pass parameters and run the reports hosted in sharepoint ?

    PLEASE HELP !!

  31. Julia says:

    Forscius, where you able to figure out how to hide reports?

  32. Resolver says:

    About Jump to URL with Javascript. I found a solution:

    ="javascript:void open(‘"+ URLstringValue + "’,’_self’)"

    URLstringValue is your URL to which you want to jump.

    ‘_self’ means that you open URL in the same window. Use ‘_blank’ to open in a new window.

  33. Amar says:

    I had given

    ="javascript:void open(‘"+ URLstringValue + "’,’_blank’)". It works well with Report manager when put into sharepoint it doen’t gives me any error and not opens…

    I have a asp.net page, on that report viewer control and the control renders  drill down reports and hosted on sharepoint 2007. The main reports renders but the drill down/through doesn;t work from MOSS but works in report manager.

    Any idea?

  34. code4food says:

    Simplest solution to the drill through report issue is to append ".rdl" to the report name in the XML file of the report that has the drill functionality. So just go to View>Code and then search for the report name and append the string above and it should work.

  35. khushboo says:

    I have tried both the suggestions given by Amar and code4food. But neither works for me.

    Can anybody through some light on this issue.

  36. Rodney says:

    hi,

      I have deployed reports on the sharepoint using the sharepoint integrated mode.The reports are drill down reports and my requirement is to open them on a new web page/web part.The drill down reports have parameters passed to it from the main report.

    How do I use the javascript in this case(i.e. how do I pass the parametrs in javascript)?

    Thanks in advance 🙂

  37. Dan says:

    I am creating a site definition for a site running in integrated mode.  I can add the report viewer web part to a page as part of the site provisioning code, but I can’t seem to change any of its properties – primarily I need to change the value for "ReportPath" to reflect that of the new site.  In the site provisioning code, the Report Viewer web part is seen as an "Error Web Part".  any ideas?