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.