How to parse arrays in XML from web service?

[Former Member]

I'm using CR 2008 with an internal web service that is returning employee information.  So far, each field of the returned XML structure is accessible as a field in the report and I can organize/arrange/formula them with no problems.

OK.  Now the providers of the web service have made the phone number information an array, such that an employee with only a work phone has one element in the array, an employee with a work phone and a mobile phone has two entries, someone with a fax has a third array entry, and so on.  Each phone number array also has a code to indicate what type of phone number that element is.  Nice flexibility, but if I grab the phone number field for display in the report, Crystal will interpret each array element as a separate details row, so all the employee information gets repeated multiple times.  The multiple detail rows also appear if I use the array structure in a formula trying to separate out specific phone numbers by the code.

So how do I parse this array inside the XML structure without Crystal wanting to interpret each array element as the source for a new detail row?  If this was a relational database, I could do some SQL gymnastics to make each phone number a separate field in my SELECT statement, but with an XML web service I can't do any fancy SQL.  The only option I've been able to make work is to make the phone number display(s) in the report each a subreport -- one subreport that filters the XML structure down to a specific phone number type -- but that seems like overkill for what should be a simpler solution.  I need a more elegant solution because they will be adding more employee data as array elements in the future.

Thanks,
Brad

[abhilash_kumar]

Hi Brad,

Why don't you create a group on the Employee ID or Name.

This way, I'm assuming, the only field that causes multiple detail rows would be the phone number field, isn't it?

That's fine, you can then create an array formula in CR and place it on the details section to execute and accumulate values.

You can then create another formula to print the values in the array and use the Group Footer for this purpose. So, essentially, you have the luxury of suppressing the Group Header and details section and only showing the Group Footer section which will show all the employee information in one row.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Thank you for the suggestion.  I did try the group option, but in addition to an array of phone numbers, they also plan to use an array of e-mail addresses, probably an array of userids and more in the future.  When I prototyped that, the various combinations of array elements turned out like a Cartestian product when joining tables.  If they would stay with only one array, the group would probably work for me, but since more arrays are coming, I'm hoping there's a good way within Crystal to parse this darn XML structure.

Thanks,
Brad

[abhilash_kumar]

Hi Brad,

Well, If that's the format CR gets the data in, then unfortunately that's how it will turn out.

You would need to do this at the report level, either using subreports or the group method.

- Abhilash