Reporting on XML data

Thursday 10 June 2010 @ 5:14 pm

(For an update to this post, read my follow-up article)

When you create a connection to data in Crystal Reports there are 3 different methods to connect to XML data.  The first two (“ADO.NET (XML)” and “Database Files”) allow you to connect to a single XML (data) or XSD (structure) file on your local PC. If you connect to an XML file then you can read the data, but all the data types will be strings.  If you connect to an XSD you will see the data types specified in the XSD but your report will have no records.  This is useful if you plan to have the data passed to the report by an application, but it means you have to design the report without being able to see the preview.

The third (“XML and Web Services” allows you to read from local files or from a web based files.  It requires both the XML and an XSD file to work.  You can either point CR to the XSD or you can reference it within the XML.  However this is fragile.  I have tried this on some PCs where it will not combine these files due to some environment problems.

One of my customers had an application that ran the report fine but we found that the report data would loose all formatting at runtime. That is a symptom of the report getting only the XML.  The data is all read as strings so all the numeric and date formatting is dropped.  We were not allowed to change the application process so our solution was to write a formula for each field that was not a string and convert the data to the appropriate data type.  Then we used the formulas for things like grouping,  totals and charts.

If you have an XML file and don’t know how to generate an XSD then you might find an XSD generator helpful.  I found several and this one is a free and open source XSD generator.  A web search will also find several free online XSD generators.

Leave a Reply

Recrystallize Pro