SSRS SharePoint integrated reporting : Combine data from multiple SharePoint Lists to a single dataset
Thursday, January 31, 2013 10:42 AMI 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?
Monday, February 04, 2013 6:21 AMModerator
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.
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.
TechNet Community Support