SharePoint 2010 and reporting services mutiple lists [closed]

Question

I'm, looking at how to manage some lists in SharePoint 2010 and have to use SQL 2008 Reporting Services to make reports on these lists. I'm new to Reporting services and is wondering if you can make reports on multiple lists in SharePoint 2010 or just one list?

Answer 1

You can add one data source per list for your report (ie: multiple data sources within your report), this Q on StackOverflow asked something similar I think:

https://stackoverflow.com/questions/35315/how-do-you-combine-multiple-result-sets-in-ssrs

Answer 2

Reporting Services allows you to use a web service as your data source, so if you only needed to retrieve items from one list you could use the GetListItems method in the lists.asmx service. i.e.

Data source > connection string: http://server/_vti_bin/lists.asmx

Report > Dataset > Query string:

<Query>
  <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
  <Method Namespace="_http://schemas.microsoft.com/sharepoint/soap/"
          xmlns="_http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
      <Parameter Name="listName">
        <DefaultValue>name of your list</DefaultValue>
      </Parameter>
      <Parameter Name="viewName">
        <DefaultValue>{guid}</DefaultValue>
      </Parameter>
    </Parameters>
  </Method>
  <ElementPath IgnoreNamespaces="true">
     GetListItemsResponse/GetListItemsResult/listitems/data/row
     { ows_First_x0020_Field,ows_Second_x0020_Field }
  </ElementPath>
</Query>

However, if you need to join 2 lists this will not be sufficient. If you are more proficient in .NET than SSRS, it might be easier for you to write your own web service that calls lists.asmx and returns the joined list the way you want it. Then use your custom web service as the data source.