XML and Crystal Reports
By David M. Woods
Published September 28, 2006, 2:42 pm in Software.
Crystal Reports has always included capabilities to communicate with a wide assortment of different types of databases. Version Eleven (XI) raises the standard even higher with support for XML data.
XML is an excellent technique for communication between a Crystal Report and its front-end calling program because it can include everything the report needs, including custom text, formulas, headers, footers, captions, anything you can think of. There is no longer a need to write code to change these kinds of report objects at runtime. After all, XML was created to be a "document-based" data storage technology, and what is a report? A document!
The examples in this article assume that you are using Microsoft .Net on the front end, and an SQL-based datatbase on the back end.
Query
The first step is to modify the query or stored procedure that drives the report, and add the "for XML raw" clause. The SQL string will resemble something like:
select Tab1.fields, Tab2.fields
from Tab1 join Tab2 on join-condition
where where-condition
for XML rawBuilding the XML document
To read the result set in the front-end application, use a DataReader, which is a forward-only read-only DotNet class. (This article assumes you have some familiarity with DotNet data access classes. )
When the "Read( )" method of the DataReader is executed, DotNet will fill the reader buffer to the max, and remember where it "left off". If the result set is short, it may all fit in one read, but don't count on it. Thus, you will need to wrap the Read( ) method inside a "While" loop.
The data can be accessed via the DataReader property "Item(0)". Append each chunk of the result set to a string. We recommend using the StringBuilder object, as opposed to the String object.
Dim Command As New SqlCommand()
Dim Reader As SqlDataReader
Dim StringBld As New System.Text.StringBuilder()
Command.CommandText = "SQL with for XML raw"
Reader = Command.ExecuteReader
While Reader.Read( )
StringBld.Append(Reader.Item(0))
End While
Reader.Close()You will need to manually add in a top-level XML element. Simply append an opening tag at the beginning, and a closing tag at the end. In this example, we will call it "TopTag":
StringBld.Append("<TopTag>")
While Reader.Read( ) . . . End While
StringBld.Append("</TopTag>")When fininished reading, load the string to an XML Document object using the "LoadXml" method, then write the object to a file with the "Save" method. Example:
Dim Document as New Xml.XmlDocument()
Document.LoadXml(StringBld.ToString)
Document.Save("PathFileName.XML")If your query contains any joins, the XML document object will build it with heirarchical nested tags, using the table names or aliases.
Including other data elements
To include other data elements in the XML document which may not necessarily reside in the result set, append them to the result string just like we appended the "top level" tag earlier. The only caveat is that you will need to separate this "non-row" data from the "row" data by another heiarchical tag level. In this example, we will accomplish that via an element we will call "Rows", plus another element above that one called "Msg" for the actual field of data.
Keep in mind that XML is very "picky" about proper tag nesting and case sensitivity.
StringBld.Append("<TopTag>")
StringBld.Append("<Msg>Hello world</Msg>")
StringBld.Append("<Rows>")
While Reader.Read( ) . . . End While
StringBld.Append("</Rows>")
StringBld.Append("</TopTag>")Finishing up
First, validate your XML file by attempting to open it in a browser window.
Once valididated, you are ready to open up your Crystal report and hook up the XML document as the data source. Select "New Connection", and on the dialog box select "Ado.net (XML)", then select the path to the saved XML file.
The top-level element becomes the "database", any named elements are "tables" and attributes are "fields". Any elements in a parent-child relationship are interpreted correctly.
Note: Data changes are not sensed and not refreshed. If you make any structural changes to the XML docuemnt, you will need to close and re-open the report to force a refresh.
Comments & Trackbacks
No Comments/Trackbacks for this post yet...
This post has 6 feedbacks awaiting moderation...
Leave a comment