Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS SharePoint integrated reporting : Combine data from multiple SharePoint Lists to a single dataset

Answered SSRS SharePoint integrated reporting : Combine data from multiple SharePoint Lists to a single dataset

  • Thursday, January 31, 2013 10:42 AM
     
     
    Sign In to Vote
    0
    I am new to SSRS and now have a requirement to create reports in SharePoint 2010. I have successfully configured SSRS with SharePoint integrated mode. I am using VS2008 BIDS instance coming with SQL Server 2008 R2 since am not able to find template in VS2010 (I have used VS2010 for creating SharePoint 2010 web application). It has three folders "Shared Data Sources", "Shared Datasets" and "Reports". As initial move I created a DataSource of Type 'Microsoft SharePoint Lists' and specified the url of my SharePoint web application. Also created sample reports using wizard by selecting the list and the required fileds, but now i need to create more complicated reports. I need to combine different lists data for the report. How can i build such queries? Also what is needed to be specified in 'Shared Datasets" folder?
     

All Replies

  • Monday, February 04, 2013 6:21 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Sujanr,

    In Reporting Services, the SharePoint List data source type uses the GetListItems Method to retrieves the data for a list, and the method can only return the records for a single list. To work around this issue, we can:

    • Use subreport to render data from multiple SharePoint Lists into one tablix.
    • Implement a new XML web service. In the web service, use the SharePoint API to merge or join two or more list. Then, in SSRS, retrieve data from the XML web service using the XML data source type.

    Reference:
    How do I use Report Builder 3.0 to join data from multiple sharepoint lists

    Besides, the BIDS shipped with SQL Server 2008 R2 uses the Visual studio 2008 shell, hence can only integrate with Visual Studio 2008. To integrate the BI Templates into Visual Studio 2010, we must install SQL Server 2012 which introduces the SQL Server Data Tools (SSDT) that uses the Visual Studio 2010 shell.

    Regards,

    Mike Yin
    TechNet Community Support