Crystal Reports to XML

2010-08-08 Corrected Formulas below

This will be the first part of a series of blogs that illistrates how to flow data from Excel to Xcelsius using Crystal Reports and XML.

XML (Extensible Markup Language) has had export support from Crystal Reports for quite a while. While the Export to XML option allows for specification of a transform file (XSL/XSLT), another handy export option I value is Export to Text. I’ll get back to that in a second.

In the wild today, there are many types of XML schemas employed by proprietary architectures that make their data more portable through the use of XML. Crystal Reports creates a great mechanism for defining an XML layout within a report that can help from getting data from point A (a data warehouse or mart) to point B, back into the required XML format.

Let’s walk through an example of structuring a Crystal Report in a format conducive to creating an XML file from the Business Objects platform.

Remove unnecessary sections
XML may consist of many layers of nested elements, based on the requirements of the data. The great thing about Crystal Reports in this situation is that any number of groupings can be applied to help aid in the nesting of your XML.

In this example, the Page Header and Page Footer are being suppressed to keep the nesting simple. The Report Header and Footer will serve as the opening and closing tags for our basic XML example.

Set a fixed width font
Courier New 10pt is a great fixed width font. Why is this important? In order to create well-formed XML which aligns properly, the font must maintain the same character size.

Use a two space rule
To continue that idea of well-formed XML, indent each level with two spaces. This will ensure that when viewed externally, the final XML output will align properly and be more easily viewed.

Get the data into the report
If not already present, get the data and get it shaped up however is required. Even though tabular in result, it can easily be manipulated into the hierarchical format required in the XML.

Format the results into formulas
There are a number of ways to create the appropriate look in the output. Each approach differs slightly. I actually prefer to turn the query results into formulas, giving me much more control over the output. It also allows you to add the XML tags surrounding the data attributes.

Start by creating a new formula for one data element. For simple text data:

The simple handler here is that if the value for Country is null, still create the opening and closing tags for the element.

Next, consider the effect of using a numeric value in the XML.

If Revenue is null, display 0. Also, when displayed, we can set the decimal places to 0 and ensure that no separator is placed within the XML (that’s important). If it has a comma in the output, the XML data may be identified as a string value instead of numeric later.

These illustrations are just two examples of the many complexities and business rules you can enforce in the formulas prior to actually rendering this information within the XML itself.

Create the XML body
With all of our formulas in place, the best way to construct the body of the result is to use a text object in the report body. In the detail section, add a text object and with the appropriate number of spaces in front of each variable, and insert as shown here.

Note that the container element in this case is child_element. This element should open and close each detail record in the output.

Close the report out with a footer that matches the opening parent tags at the top and you’ve created some well-formed XML.

Last bits

1. Text can grow dynamically, obviously. Set the width of the text object cell here in the Details section to be 100% of the width of the available page.
2. Right click on the text object in the details and choose “Format Text”. Ensure that the “Can Grow” property is set and set to 0 (no limit). This will ensure that no matter how many rows of detail data you see, you get them in your XML output.
3. When finished, Export as Text (not as XML!) and set the “Number of Lines per Page” property to 0. XML is a continuous file format and should not have page breaks. Save the output file with a .xml extension, even though the Save As dialog box indicates “Paginated Text (*.txt)”.
4. Be prepared to deal with invalid XML characters, such as “&”. Converting them to XML friendly values will avoid future headaches in your finished XML. Simple formulas will help to account for this.

Conclusion
XML is an extremely powerful way to share data. In this illustration, root_element and parent_element
don’t really mean anything. However, these could have been replaced with groups on the Country and Resort to further enforce the hierarchy of the XML data.

Using Crystal Reports is a great way to rapidly create XML structures and automate the publication of this XML via the Business Objects platform.