Reporting Services, get data from any Sharepoint List

Question

I am trying to build an SSRS (2008R2) report based on a Sharepoint (2010) List. The main problem is that the List on which the report will run has to be a report parameter.I know what the list structure will be, but the sharepoint site can contain several list instances having this structure, and when running the report, the user has to choose the List Name. Also, the report has two date parameters, MinDateTime and MaxDateTime, and selects only the records with times between these two.

From what I can tell, there are at least two approaches to building the report:

1.Use a Sharepoint List Data Source and write the Dataset query in CAML, specify the site in the DataSource, let SSRS handle the rest of the details. The problem in this case is that I can't specify the ListName as a report parameter. The DataSet query looks like this:

    <RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ListName>BusinessList1</ListName>
          <ViewFields>
            <FieldRef Name="Title" />
            <FieldRef Name="BusinessUnit" />
            <FieldRef Name="ScanDateTime" />
          </ViewFields>
          <Query>
            <Where>
              <And>
                <Geq>
                  <FieldRef Name="ScanDateTime" />
                  <Value Type="DateTime">
                    <Parameter Name="MinScanDateTime" />
                  </Value>
                </Geq>
                <Leq>
                  <FieldRef Name="ScanDateTime" />
                  <Value Type="DateTime">
                    <Parameter Name="MaxScanDateTime" />
                  </Value>
                </Leq>
              </And>
            </Where>
          </Query>
        </RSSharePointList>

2.Use an XML Data Source and write the Dataset query in soap-readable XML, access the /_vti_bin/lists.asmx webservice directly. The query should look something like this (including the list name as a parameter). However, I couldn't make it work at all with the Date parameters. Where should they be added?

   
    <Query>
       <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
       <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
          <Parameters>
             <Parameter Name="listName">
                <DefaultValue>BusinessList1</DefaultValue>
             </Parameter>
             <Parameter Name="viewFields">         
                  <ViewFields>
                    <FieldRef Name="Title" />
                    <FieldRef Name="BusinessUnit" />
                    <FieldRef Name="ScanDateTime" />
                 </ViewFields>
             </Parameter>
          </Parameters>
       </Method>
       <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

Any direction would be great. Thanks,

Answer 1

I haven't tried it but would probably prefer to use the XML Data Source and the REST api accessing

http://site/_vti_bin/listdata.svc/listName?$select=Title,BusinessUnit,ScanDateTime&$filter=(ScanDateTime lt datetime'2012-04-10T00:00:00') and (ScanDateTime gt datetime'2012-03-01T00:00:00')