SOAP call with query on result (SSRS, Sharepoint)

Question

Hi!

I created a report in VS using a shared data source which is connected to a sharepoint list. In the report I created a dataset with a SOAP call to the data source so I get the result from the sharepoint list in a table.

this is the soap call

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems&lt;/SoapAction&gt;
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
  <Parameters>
     <Parameter Name="listName">
        <DefaultValue>{BD8D39B7-FA0B-491D-AC6F-EC9B0978E0CE}</DefaultValue>
     </Parameter> 
     <Parameter Name="viewName">
        <DefaultValue>{E2168426-804F-4836-9BE4-DC5F8D08A54F}</DefaultValue>
     </Parameter>
     <Parameter Name="rowLimit">
        <DefaultValue>9999</DefaultValue>
     </Parameter>   
  </Parameters>
</Method>   
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

THis works fine, I have a result which I can show in a report, but I want to have the ability to select a parameter to filter the result on. I have created a parameter and when I preview the Report I see the dropdownbox which I can use to make a selection from the Title field, when I do this it still shows the first record, obviously it doens't work yet (DUH!) because I need to create a query somewhere, But! I have no idea where, I tried to include

   <Where>
    <Eq>
     <FieldRef Name="ows_Title" />
     <Value Type="Text">testValue</Value>
    </Eq>
   </Where>

in the the soap request but it didn't worked... I've searched teh intarwebz but couldn't find any simliar problems... kinda stuck now...any thoughts on this?

EDIT

Here's the query I used according to the blogpost Alex Angas linked.

<Query>
   <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems&lt;/SoapAction&gt;
   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<queryOptions></queryOptions> 
<query><Query> 

<Where> 

<Eq> 

<FieldRef Name="ows_Title"/> 

<Value Type="Text">someValue</Value> 

</Eq> 

</Where> 

</Query></query>   
      <Parameters>
         <Parameter Name="listName">
         <DefaultValue>{BD8D39B7-FA0B-491D-AC6F-EC9B0978E0CE}</DefaultValue>
     </Parameter> 
     <Parameter Name="viewName">
        <DefaultValue>{E2168426-804F-4836-9BE4-DC5F8D08A54F}</DefaultValue>
     </Parameter>
     <Parameter Name="rowLimit">
        <DefaultValue>9999</DefaultValue>
     </Parameter> 

  </Parameters>
</Method>   
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

I tried to put the new query statement in every possible way in the existing, but it doesn't work at all, I do not get an error though so the code is valid, but I still get an unfiltered list as return... pulling my hair out here!

Answer 1

See the question and answers for GetListItems Webservice ignores my query filter. This shows you how (and how not to) set up your SOAP call to include a query. You probably need to wrap your query with another <Query></Query>.

Answer 2

You have your FieldRef as

ows_Title

I believe it should just be Title.

When you get results from the SOAP request all your field name will begin with

ows_

Answer 3

A post at:

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/1562bc7c-8348-441d-8b59-245d70c3d967/

Suggested using this syntax for placement of the <Query> node (this example is to retrieve the item with an ID of 1):

<Query>
  <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems&lt;/SoapAction&gt;
  <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
      <Parameter Name="listName">
        <DefaultValue>{CE7A4C2E-D03A-4AF3-BCA3-BA2A0ADCADC7}</DefaultValue>
      </Parameter>
      <Parameter Name="query" Type="xml">
        <DefaultValue>
          <Query>
            <Where>
              <Eq>
                <FieldRef Name="ID"/>
                <Value Type="Integer">1</Value>
              </Eq>
            </Where>
          </Query>
        </DefaultValue>
      </Parameter>
    </Parameters>
  </Method>
  <ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

However this would give me the following error:

Failed to execute web request for the specified URL

With the following in the details:

Element &lt;Query&gt; of parameter query is missing or invalid

From looking at the SOAP message with Microsoft Network Monitor, it looks as though the <Query> node is getting escaped to &lt;Query&gt; etc, which is why it fails.

However, I was able to get this to work using the method described in Martin Kurek's response at:

http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx

So, I used this as my query:

<Query>
  <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems&lt;/SoapAction&gt;
   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
      <Parameters>
         <Parameter Name="listName">
            <DefaultValue>{CE7A4C2E-D03A-4AF3-BCA3-BA2A0ADCADC7}</DefaultValue>
         </Parameter>
         <Parameter Name="query" Type="xml">
        </Parameter>   
      </Parameters>
   </Method>
   <ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

And then defined a parameter on the dataset named query, with the following value:

<Query><Where><Eq><FieldRef Name="ID"/><Value Type="Integer">1</Value></Eq></Where></Query>

I was also able to make my query dependent on a report parameter, by setting the query dataset parameter to the following expression:

="<Query><Where><Eq><FieldRef Name=""ID""/><Value Type=""Integer"">" & 
Parameters!TaskID.Value & 
"</Value></Eq></Where></Query>"

Answer 4

Brilliant, thanks. This solution worked for queryOptions also.

In the Query:

<Parameter Name="queryOptions" Type="xml">
</Parameter>

And in the parameters list of the dataset:

Name: queryOptions

Value: <QueryOptions><Folder>Shared Documents/MyFolder</Folder></QueryOptions>

Answer 5

Saxon's answer worked perfectly.I love examples that clear, to the point and most importantly - work as advertised!

Answer 6

Hi.., I want to generate a SSRS report for sharepoint list, i have followed the steps but when i previewed it is showing the error An error occured during local report processing. An error has occured during report processing. Query Execution failed for dataset 'Dataset1' ........... blah blah......