Designing Report for complex xml data source

Hello

I want to design a report for a more or less complex xml data source and have problems doing so.

My data source looks like this

<?xml version="1.0"?>
<report>
<document>
<item name='customer'>
<text>Max Muster</text>
</item>

<item name='accounts'>
<text>124890</text>
</item>
</document>

<document>
<item name='customer'>
<text>Theo Tester</text>
</item>

<item name='accounts'>
<textlist>
<text>796542</text>
<text>527988</text>
</textlist>
</item>
</document>
</report>

In my report there shall be a table, a line for each document with a column with the customer name and a second column with the account numbers:

Customers | Accounts
Max Muster | 124890
Theo Tester | 796542, 527988

As far as I am familiar with BIRT now, I see two problems:

1. item 'accounts' may have more than one value, in that case an extra element called 'textlist' will be present
BIRT can only show values for the lowest element, so my column mapping needs to be either item[@name='accounts']/text or item[@name='accounts']/textlist/text - how can I tell BIRT when to use which XPath?

2. With the given column mapping I won't be able to receive all accounts, as item[@name='accounts']/textlist/text only returns the first account number.

How can I solve these issues?

1. No idea

2. Different data sets for the documents and the accounts and then try to connect them. I wanted to do that but when I tried to create a dataset for /document/item[@name='accounts']/textlist/text I was told, that the XPath expression was invalid.
So I tried /document/item/textlist/text first and wanted to get the value of this element by XPath of . (point). But when I tried to preview that I was told that the query text was invalid.

So in the end I can't find out which accounts my customers have in this example.

Can anyone help? I have to work with this type of xml data sources a lot and if I can't find a way to design a report for multiple value items I think our customers won't be too happy about that.

Thanks alot,
Johannes

Find more posts tagged with

Comments

mwilliams

Hi Johannes,

Because of the setup of the XML source code, BIRT has a hard time picking out the "listed" account numbers using column mapping. If you could possibly change the XML code design to work better with BIRT, that would probably be your best option.

However, using your data, I was able to bring in all the data into 1 column, the type in another column, then used a filter and a computed column to separate and match the customers with their account numbers. A zip file with the xml file and rptdesign file is attached.

Hope this helps.

mwilliams

Oh, I forgot to tell you that that report was created in BIRT 2.3.

azique

Hello Michael!

Thanks a lot for your explanation and the zip file!

And thanks for the trick with item//text to get all text elements under item - which I really should have known myself

But it does not seem to work in BIRT 2.1.3.

But of course, you're right, if one is interested in the accounts here, one should transform the source into a better structured form.

Thanks again!
Johannes

mwilliams

Johannes,

So, the solution that worked for me in 2.3 doesn't work for you in 2.1.3?

azique

Michael,

I am not able to use the item//text notation in Version 2.1.3 (I can enter it, though, if not with the mapping wizard gui, finally by changing the source xml of the design file manually). The result is an empty column. So I first have to change my data source that there always will be a 'textlist' element before 'text'.

The rest does work! But as I said: it's better to transform the xml data to a better readable structure first