report from XML data

[priyanthan]

One of the table in by report contains XML data and need to extract data from it.

the xml contains;
<Info><AtmMon><Tier Cnt="10" Amt="60.0000"/><Tier Cnt="25" Amt="40.0000"/><Tier Cnt="40" Amt="42"/><Tier Cnt="50" Amt="45.0000"/><Tier Cnt="100" Amt="20.0000"/><Tier Cnt="99999" Amt="15.0000"/></AtmMon></Info>

I need to report like this

Tier Count Rate
0 10 60.00
1 25 40.00
2 40 42.00
3 50 45.00
4 100 20.00
5 99999 15.00

Can anyone please tell me how to do this. working with CR 8.5 and DB2

Thanks

 

[Madawc]

You can do it using Split and then Left, Right or Mid to get the field. The exact method would depend on your data, something like

Mid((Split({your.field}, "><")[2]), 14, 2)

This would give you the first count value, you'd need to get them individually and display on successive lines of a detail section, or several detail sections. No general solution within Crystal that I can see, you'll need to craft them each indiviudally.

The problem with my solution is that it assumes a count of two digits. Getting beyond that might need splitting on the " character itself. In Crystal 11 you can use other delimiters, I'm not sure if 8.5 allowed that. If not, Replace is another answer.

You'll need to nest commands, as I have, or else reference one field in another. I also suggest you do it by steps, first write a Split and confirm the output is what you want, then adjust or split it further for the output you want. Crystal is very suitable for that, whereas if you try writing one grand commmand to do it all, it may fail to work for unclear reasons.

If the data varies you might need Ubound to determine the number of elements. Check it out in Help.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP