Using XML datasets for a Crystal Report

Question

We have this XML schema:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
  <xs:element name="Log">
 <xs:complexType>
   <xs:sequence minOccurs="0" maxOccurs="unbounded">
  <xs:element name="LogEntry" minOccurs="1" maxOccurs="1">
    <xs:complexType>
   <xs:sequence>
     <xs:element name="Time" type="xs:dateTime" />
     <xs:element name="StringRef" type="xs:string" />
     <xs:element name="Parameters" minOccurs="0" maxOccurs="1">
    <xs:complexType>
      <xs:sequence>
     <xs:element name="Parameter" minOccurs="0" maxOccurs="unbounded">
       <xs:complexType>
      <xs:sequence>
        <xs:element name="Name" type="xs:string" />
        <xs:element name="Value" type="xs:string" />
      </xs:sequence>
       </xs:complexType>
     </xs:element>
      </xs:sequence>
    </xs:complexType>
     </xs:element>
   </xs:sequence>
    </xs:complexType>
  </xs:element>
   </xs:sequence>
 </xs:complexType>
  </xs:element>
</xs:schema>

and we want to create a Crysal Report that looks like this:

<Time> <Parameter:Name == Text> <Parameter:Name == Param1> <Parameter:Name == Param2>

where each line of the report is one LogEntry. The LogEntry's that are displayed are filitered on the StringRef parameter. So, given the following XML:

<Log>
  <LogEntry>
    <Time>2009-06-15T11:55:04</Time>
    <StringRef>Type1</StringRef>
    <Parameters>
      <Parameter>
        <Name>Text</Name>
        <Value>Message1</Value>
      </Parameter>
      <Parameter>
        <Name>Param1</Name>
        <Value>1</Value>
      </Parameter>
      <Parameter>
        <Name>Param2</Name>
        <Value>2</Value>
      </Parameter>
    </Parameters>
  </LogEntry>
  <LogEntry>
    <Time>2009-06-15T11:55:05</Time>
    <StringRef>Type2</StringRef>
    <Parameters>
      <Parameter>
        <Name>Text</Name>
        <Value>Message2</Value>
      </Parameter>
      <Parameter>
        <Name>Param1</Name>
        <Value>1</Value>
      </Parameter>
      <Parameter>
        <Name>Param2</Name>
        <Value>2</Value>
      </Parameter>
    </Parameters>
  </LogEntry>
  <LogEntry>
    <Time>2009-06-15T11:55:06</Time>
    <StringRef>Type3</StringRef>
    <Parameters>
      <Parameter>
        <Name>Text</Name>
        <Value>Message3</Value>
      </Parameter>
      <Parameter>
        <Name>Param1</Name>
        <Value>1</Value>
      </Parameter>
      <Parameter>
        <Name>Param2</Name>
        <Value>2</Value>
      </Parameter>
    </Parameters>
  </LogEntry>
</Log>

and filtering on:

StringRef == Type1 or StringRef == Type3

would give a report like this:

2009-06-15T11:55:04 Message1 1 2
2009-06-15T11:55:06 Message3 1 2

My question is this: can this be done using Crystal Reports? If it can, some information on how to do it would be helpful.

Notes - the above has been anonymised somewhat so I'm looking for how to do it rather than a specific answer, although that will be useful as an example. We've been told by the person responsible for implementing this report that the above is not possible, however, I feel that this should be possible. It's OK to say it's impossible, it's just means more work for me :-(

Cheers,

Skizz

Answer 1

Yes, Crystal can easily handle an xml datasource. However, you have to realize that the data will be represented as a table. Just set the datasource to the xml doc and put all the fields in the details section to see what I mean. I'd guess there'd be a LogEntry, Parameter, and Parameters tables.

To concat the parameters into one line you can use a shared stringvar like so...

Group By the LogEntry_Id field then create these 3 formula objects...

reset_var

shared stringvar params;

whileprintingrecords;

params := '';

concat_var

shared stringvar params;

whileprintingrecords;

params := params + iif(length(params) = 0, "", " ") + {Parameter.Value};

show_var

shared stringvar params;

whileprintingrecords;

params;

Drag reset_var to the group header (suppress the group header). This will reset the var on each LogEntry. Drag concat_var to the details section(suppress the details section). This will build the param string. Then drag show_var to the group footer to display the params.

Answer 2

I agree with dotjoe that the method in his post should work (though I didn't try it), but I wanted to throw out another option for you to look at.

Another thing that you can look at doing instead of using Crystal is using XSLT.

If you added the following code to your xml:

<?xml-stylesheet type="text/xsl" href="test.xsl"?>

then you should be able to use an xsl file (named test.xsl) like the following:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
<xsl:template match="/">
<html>
<body>
<table border="1">
    <xsl:for-each select="Log/LogEntry[StringRef='Type1' or StringRef='Type3']">
    <tr>
        <td><xsl:value-of select="Time"/></td>
            <xsl:for-each select="Parameters/Parameter">
           <td><xsl:value-of select="Value"/></td>
        </xsl:for-each>
    </tr>
    </xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

I understand that this is off topic for Crystal Reports, but I just wanted to throw out another idea for you to try that might end up being a better solution for you than Crystal. Just throwing it out there. :)