Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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,

share|improve this question
    
are you running SSRS in SharePoint integrated mode? Are you running the reports within SharePoint pages (i.e., with the ReportViewer web part?) –  lwbecker2 Mar 13 at 1:35
add comment

1 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')

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.