Why is SSRS ignoring data from certain columns returned by a web method?

Question

I'm using the XML data source feature in Reporting Services 2005 but having some issues with missing data. When there is no value for the first column in a row, it appears that the entire column is ignored by SSRS!

The web method request is very simple:

<Query>
   <Method Name="GetIssues" 
Namespace="http://www.mycompany.com/App/"&gt;
   </Method>
   <SoapAction>http://www.mycompany.com/App/GetIssues&lt;/SoapAction&gt;
   <ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Equally, the response is very simple:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  <soap:Body>
    <GetIssuesResponse xmlns="http://www.mycompany.com/App/"&gt;
      <GetIssuesResult>
        <Issue>
          <Title>ABC</Title>
          <RaisedBy />
          <Action>Do something</Action>
        </Issue>
        <Issue>
          <Title>ABC</Title>
          <RaisedBy>Jeff Smith</RaisedBy>
          <Action>Do something</Action>
        </Issue>
      </GetIssuesResult>
    </GetIssuesResponse>
  </soap:Body>
</soap:Envelope>

In this example the RaisedBy column will be completely empty. If the 'Issues' are reversed so RaisedBy first has a value, there is no problem. Any ideas?

Answer 1

Is it possible to eliminate the NULLs in the XML? Replace them with an empty string? Then you won't have to wrestle with SSRS.

If the XML is generated from a database call, that's easy enough to do (ISNULL in SQL Server).

Answer 2

In the Query itself, try to define your columns explicitly, instead of letting SSRS determine them for you.

In other words, where you have:

<ElementPath IgnoreNamespaces="true">*</ElementPath>

Replace the * with something like:

<ElementPath IgnoreNamespaces="true">GetIssues/GetIssuesItemsResult/listitems/data/row{@Title,@RaisedBy,@Action}</ElementPath>

Of course, that exact XPath may not be correct for your example.