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:

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:

<pre>
   <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>

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?

 <pre>  
   <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,

weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60288.aspx might help you. — JonH
If you need to do much reporting on arbirtrary SharePoint lists, a third party tool such as enesyssoftware.com/language/en-US/Products/… might be worth it. — Jamie F
@JonH - Thanks, looks good, but I'd rather stick to the "never touch the database" sharepoint rule. — pax162
@Jamie F - Had a look at it too, but we can't use it for this project. — pax162

Davos Davos · Accepted Answer · 2013-02-19T03:29:35

You can use option 1, writing the query as an expression. Build up a long string with the parameter in the middle. You need a separate query to supply the list of BusinessLists to the parameter.

Expression would look like this:

="<pre>
   <RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ListName>"

& Parameters!BusinessList.value & 

"</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>"

[EDIT]: I'm not sure where the pre tag came from either. I've run through creating a test report using the sharepoint list connection type and it doesn't add that. Check out this MS link on the basics.

It points out that you don't need to specify the fields to return, so a very basic query expression looks like this (with my stuffed parameter added):

="<RSSharePointList xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema""><ListName>" & Parameters!List.Value & "</ListName></RSSharePointList>"

In my original example above I failed to mention that you need to escape the double quotes within the XML by doubling them up. I've tested this and it works well.

Reporting Services, get data from any Sharepoint List

1 Answers