SQL Server 2005 Service Pack 2 (Integration between Reporting Services and SharePoint 2007)

Yesterday I was rewriting my two-day Reporting Services 2005 course to a three-day course. The previous times I gave this course I ran out of time and couldn’t explain every module in depth. So I decided to extend the course and add an extra module about Implementing Business Logic with MDX. This new module contains information about using Analysis Services Cubes to create reports. I’ll focus on matrix and graphics data regions as well. Besides adding a new module I was adding some content to the other ones. When I was extending the Programming Reports module I found out about SQL Server 2005 SP2.

As you know, we already have SharePoint Web Parts, and they work well. However, Microsoft will include much deeper integration with WSS/OS in SP2. Some limitations of the current WebParts are:

  • Separate content stores;
  • Different security models;
  • Different management UI;
  • No filter web parts.

The reporting team did a great job to fully integrate Reporting Services into WSS/OS. After installing SP2, you can run Reporting Services in "native mode" (like it’s today), or "SharePoint integration mode". The integration mode will enable publishing, viewing, and management of rich reports.

 
Integration Architecture

Management
SQL Server 2005 SP2 is installed on a report server along with the SharePoint Object Model (farm install). The Reporting Services feature pack is installed in WSS “v3” and the Report Server web service URL is registered with WSS farm. To create a new Report Server database in “SharePoint Integration mode” you can use the Reporting Services Configuration Tool. In SP2, the tool has been modified to include some additional UI to do this work.

Note: Unfortunately it’s not possible to migrate from existing SQL Server 2005 Reporting Services (SSRS) installations.

Using “SharePoint Integration mode”, most of the objects you're used to store in reportserver database are instead stored in SharePoint database. Some functionality like scheduling, subscriptions, snapshots and caching are still stored in the reportserver database. Lets say they implemented every feature of Reporting Services in SQL Server 2005 Express in WSS.

All other objects like reports, data sources, and report models are published to SharePoint document libraries. When a report is selected in WSS, the report viewer Web Part calls the report server API to process and render the report. Users can manage properties and subscribe to reports through WSS UI which calls the RS SOAP API. New report server delivery extension allows for rendered reports to be delivered to WSS document libraries (including Report Center). Another nice thing is all cool features of SharePoint like versioning, collaboration and workflows are now available to all objects.

Note: Report Manager (Web UI / Management Studio) is not supported in “SharePoint Integration Mode” and it's not possible to run SSRS in both modes at the same time.

Another major change is security. All permissions are set within WSS and will give you a consolidated place to manage security. For Example when using Team Foundation Server you now have to set rights in TFS, SharePoint and Reporting Services. I hope tools like this will fully integrate with SharePoint as well. The design tools (Report Designer, Report Builder, Model Designer) are also updated to work with WSS.

Note: It looks like data-driven subscriptions is cut out. Normally this is really powerful feature. So I hope the Microsoft team is aware of this and will soon add it to the “SharePoint Integration mode”.

SharePoint User Interface
Viewing reports while in integrated mode is like any normal webpage in SharePoint. You can use the Report Viewer WebPart to show a report in full page view or on Web Part Pages. This WebPart wraps the ReportViewer ASP.NET Viewer Control and handles report rendering calls to report server. Besides handling it will give you all the properties by using its own EditorPart and verbs. The properties can be devided by:

  • Report: ReportPath, HyperlinkTarget
  • View: AutoGenerateTitle, AutoGenerateDetailLink, ToolBarMode, ParametersMode, ParametersAreaWidth, DocumentMapMode, DocumentMapAreaWidth
  • Parameter: Default Values

At last is will also support Filter Consumer and Row Consumer interfaces for specifying report parameter values via filter Web Parts. This way you can slice Excel Workbooks and Reports on a single Web Part page. Superb.


Document Library UI


Viewing Reports (Full Screen)


Report Properties (Parameters)

   
Report Viewer Web Part

Other important changes
Integration will give you new report server SOAP and WMI interfaces endpoints.
The SOAP Proxy is now installed in WSS to support firewall deployment.
Most API’s are mapped to SharePoint object model calls. For example, ListChildren returns items in the content database.
File Extensions are mapped into Report Server types (Report / Data Source / Model / Resource)
Reporting Services security role definitions are replaced with SharePoint principles. CreateRole, DeleteRole, GetRoleProperties, SetRoleProperties are all removed

Summary
I think the the team did a great job. They have said they will deliver a version with no differences between the native and the integrated mode, but it won’t happen in SP2. The integrated mode will be very populair with customers deciding to use SharePoint and are using less complex solutions. More complex solutions using subscriptions and lots of administration will still use native mode.

22 thoughts on “SQL Server 2005 Service Pack 2 (Integration between Reporting Services and SharePoint 2007)

  1. Adnan Shamim

    hello
    well, i am using sql ser 2005 reporting services and have to find out one thing that may be a bug at the reposrting services' end.
    simply put a textbox in the .rdl and write some text in it, long enough to be in more than one line, at least two lines that is.
    now from the properties of this textbox, try changing the LineHeight values. nothing happens, not in html view and not in any extracted formats....
    can you please let me know? my address is fastian1335@hotmail.com

    Reply
  2. mr.mcgoo

    Is there a Microsoft page dedicated to SSRS 2005 SP2 yet? I'm curious to know what's coming, although I understand it won't come til Vista is released.

    Cheers
    Chris

    Reply
  3. Chris,

    SP2 is still not released. CTP1 is a very important milestone for SP2. After its CTP2 is released, it is amost the time to release SP2.

    CTP1 is only released internally at microsoft at \sqlbuildsbuildsRELEASEDSQLServer20059.00.3017.01releasepackages and to some beta testers.

    By the way there is no Enterprise SP2. The same SP2 will be applicable on all SQL Server 2005 SKUs like Enterprise, Developer, Standard, etc.

    Check https://connect.microsoft.com/SQLServer/feedback/ to actively work with members of the Microsoft product team.

    Reply
  4. Adnan,

    The LineHeight element describes the height of ONE line of text. If you want to show multiple lines in a textbox you should enable the CanGrow element. This element indicates whether the size of the text box can increase vertically according to its content.

    Nevertheless if you export a textbox with an enabled CanGrow element and a LineHeight of e.g. 100pt it won’t work all the time. Only export to Web Archive will show you the correct info. All other exports will mess up the output. Sounds like a bug ;-).

    Reply
  5. Dave Perkinson

    I noticed while reading through the SP2 documentation that "The SQL Server 2005 SP2 CTP2 release of Reporting Services supports a single-server deployment that places the report server, Windows SharePoint Services 3.0, and the SharePoint databases on the same computer."

    Does this support of a "single-server" deployment preclude deployment across a 2-server farm?

    Also, has microsoft put out anything in the way of a demo .vhd that might be used to test-drive the SP2/MOSS integration?

    I appreciate your input.

    Regards,
    Dave

    Reply
  6. Dave

    I'm sorry I can't help you right now. Farming a site looks for me the same as installing it on a "single-server". Nevertheless I've never tested it.

    Futhermore I hope MS will give us a .vhd for demo/training purposes. To be specific, I hope they will use differencing disks that use a common, Windows Server 2003, SharePoint base image. With the move to shipping virtual machines, differencing disks will mean smaller downloads than having to download a complete image with each release.

    If you're unfamiliar with using differencing disks, you should read Andrew Connell's HOW TO: Use Virtual PC's Differencing Disks to Your Advantage.

    Reply
  7. Suresh

    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

    Reply
  8. Ponto

    my goal is to show reports in sharepoint services 3.0 and use this update Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint

    i have this problem:

    After update my sql server 2005 to sp2 ctp with SQLServer2005SP2-KB921896-x86-ENU.exe and upate reporting services, try to:http://:1010/reportserver show the message.

    An internal error occurred on the report server. See the error log for more details. (rsInternalError)

    Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()'.
    i this problem is in the Microsoft.ReportingServices.Diagnostics.dll i see the method and in version (9.0.3027.0) dont have, and have in 9.0.1399, what i can do to this work rigth

    Reply
  9. d. louie

    Where is the Report View CAB stored. I can't seem to locate it in my SQL Server installation. c:program files.....

    Reply
  10. Laurent

    Hello,

    I have a problem with reporting services in intergrated mode and maybe some of you can help me...

    I have SQL2005 and MOSS2007. Everything works fine except reports with parameters in the report viewer webpart... The same report works in 'full screen' on my WSS site. All the reports without parameters works in the webpart. The only error message that I receive is the great "Object reference not set to an instance of an object."

    If someone had this problem, thenaks in advance

    Reply
  11. N@deem R@uf

    Hi,

    Our company is using SQL 2005 Reporting Services (SSRS) to display a dashboard with some useful statisctics and fancy graphs on the company intranet. The problem is that the ReportViewer takes too long to bring up results on the MOSS-based site. The SQL works efficiently from Query Analyzer (takes around 1 sec) but the same query takes almost 40 seconds (!!!!) to throw results on the MOSS site.

    We have tried to identify the cause of delay without any luck so far. The Server has sufficient memory / other hardware specs so that does not seem to be the bottleneck. I wonder if there are any known performance issues with using SSRS over MOSS? Is there any better alternative?

    Any suggestions to help optimize the reports would be highly appreciated.

    Cheers!

    Nadeem

    Reply
  12. Guy

    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

    Reply
  13. Patrick

    Yes you can use a webpart..

    Try googling for RSWebParts.

    It would give you 2 webparts "Report Viewer and Report Explorer" and its easy to install to you WSS since they are cab files.

    Hope that Helps

    Patrick

    Reply
  14. Pat

    Hi,

    I have report server in SharePoint integrated mode and the Reporting services runnign under domain account. which is part of dmain adming group as well.but when i try to connect to http:\localhostreportserver

    it gives me this error:

    Reporting Services Error
    --------------------------------------------------------------------------------

    Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help
    User cannot be found.

    --------------------------------------------------------------------------------

    SQL Server Reporting Services

    and in reporting service log it gives me following information

    w3wp!webserver!1!8/15/2007-13:40:01:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: User cannot be found.
    at Microsoft.SharePoint.SPUserCollection.GetByID(Int32 id)
    at Microsoft.SharePoint.SPWeb.get_Author()
    at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPWebProperties(SPWeb web)
    at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.internalFindObjectsNonRecursive(String wssUrl, CatalogItemList& children)
    at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsNonRecursive(String wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)
    --- End of inner exception stack trace ---
    at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsNonRecursive(String wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)
    at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow()
    at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()
    at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderFolder()
    at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType)
    at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
    at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()

    how can I solve it!!!!!

    thanks

    --------------------------------------------------------------------------------
    pat

    Reply
  15. Swan

    We run SSRS and WSS in integrated mode and have a set up as follows:

    SERVER 1
    .NET Framework 2.0
    SQL Server 2005 SP2 (Database)
    SQL Server 2005 SP2 (Reporting Services)

    SERVER 2
    Internet Information Server 6.0
    .NET Framework 2.0 & 3.0
    Windows SharePoint Services 3.0
    Reporting Services Add-in for Microsoft SharePoint Technologies

    All work well apart from one issue around email subscriptions... All hyperlinks point to Server 1 instead of Server 2 (http://... sharepoint site).

    Any suggestions how this can be fixed?

    Thanks
    Swan

    Reply
  16. Swan,

    Did you change all config files of Reporting Services. There are a lot of them so you could easily mis one. Besides this it could also be a registry setting. I'm not sure. Anyway good luck.

    Reply
  17. Ekta Dahiya

    Services error Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: User cannot be found.

    Whoever is having this error, 99% chances are its related to one of your sharepoint sites Author's user record set to be deleted in Shapreoint Content databases. Run SQL Profile to point to your sharepoint DB, browse to your page, search for exec proc_SecGetPrincipalById where in is the author of the site . Check for tp_deleted status of all the values found in UseInfo table, both in Content Db and Admin DB. Set as Undeleted all who have tp_deleted = 1.

    Reply
  18. bhavik mehta

    can ne one guide me on this ...
    i m having a scenario in which i hav to pass on the a company id from sharepoint to the SSRS reports as paramters so the reports ..
    i have searched on net.. got plenty of ways but still no luck .. (via URL and Jump to URl ..CAML Query) ...since i did not get steps so clear..
    ne help wud b gr8ly appreciated
    waiting for reply....

    Reply
  19. Advait Kulkarni

    Is there a place where I can find limitations of using a WSS 3.0 and SSRS 2005 SP2/ SRS 2008 combination?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *