Missing Fields Returned From XML DataSet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Missing Fields Returned From XML DataSet

Author

Topic

gtrussell
Starting Member

3 Posts

Posted - 2005-12-05 : 11:44:17

First time poster; long time RS user. I'm using a VS 2005 WebService to return a DataTable that is consumed by a RS 2005 report.

The problem is when I execute the query in the Report Designer, there are seven fields being left out. I have tried to manually add these fields to the DataSet using the DataSets tab, but when I run the report, I get an "Index out of bounds" error.

Of course, when I view the results from the WebService in IE, I can see the fields just fine. Here are a few cuts from what I'm talking about:

The WebService returns several columns, including the following:


  <xs:element name="Sulfur" type="xs:string" minOccurs="0" /> 
  <xs:element name="Phytase" type="xs:string" minOccurs="0" /> 
  <xs:element name="Ash" type="xs:string" minOccurs="0" /> 
  <xs:element name="Pepsin" type="xs:string" minOccurs="0" /> 
  <xs:element name="FFA" type="xs:string" minOccurs="0" /> 
  <xs:element name="Insolubles" type="xs:string" minOccurs="0" /> 
  <xs:element name="Peroxide_x0020_Value" type="xs:string" minOccurs="0" /> 
  <xs:element name="Lysine" type="xs:string" minOccurs="0" /> 
  <xs:element name="MHA" type="xs:string" minOccurs="0" />

Most of the rows have values for Sulfur and Ash, but only a few have values for Phytase, Pepsin, FFA, etc.

When I run the WebService, I get approx. 3000 rows returned. Again, most are similar to this:


  <Sulfur>0.284474558</Sulfur> 
  <Ash>35.3861484803189</Ash>

There is not a Phytase column. However, some results have it:


  <Sulfur>0.159540677</Sulfur> 
  <Phytase>499.18995324323</Phytase> 
  <Ash>4.88373642481176</Ash>

And this goes for the others (Pepsin, FFA, etc.) Unfortunately, Phytase, Pepsin, FFA, etc. are completely left out of the report because the query doesn't return them in the DataSet.

So, when the query is executed within RS, does it take a "sample" of the data and assume these are all of the appropriate columns? How can I get these other columns to show up? As I said earlier, I tried to manually add the column to the DataSet, but I get an error when I run the report.

Any advice or "try this" thoughts are greatly appreciated.

Thanks,
Gene

jhermiz

3564 Posts

Posted - 2005-12-05 : 13:04:42

This is a bug in RS, I have run into the same issues as well. When adding to the xml schema something sort of goes out of sync in RS. The only solution I found was to recreate the page :(.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

gtrussell
Starting Member

3 Posts

Posted - 2005-12-05 : 14:43:25

Thanks for the tip that it's a RS bug. I tried recreating the page, starting from scratch with a new DataSource, DataSet and Report but no luck.

I'm really baffled on this.

Gene

jhermiz

3564 Posts

Posted - 2005-12-05 : 14:54:22

I would need to look at the issue sorry.

At least you know you are not the only one with these problems.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

gtrussell
Starting Member

3 Posts

Posted - 2005-12-05 : 16:12:13

Thanks for the help, jhermiz.

However, I created a work-around if anyone else is having the same problem. I had to put a default value on each of the columns in the DataTable I'm returning from the WebService. Unfortunately, this is going to cause a little bit of XML bloat as it's returning a default value ("0" in this case) for every column for every record, if it doesn't already have a value.

Not too much of a bad thing but not what I was expecting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources