asked on

Parsing XML data in crystal reports

Hi Experts:
So I'm not super with Crystal Reports... let's start there.

I have a memo field that is stored as XML.
I need to parse the XML and pull 3 variables for one for Checknumber, one for checkdate, and one for paid.

Here is part of the problem. Some data sets contain the 3 variables, some don't, some data sets are null (no data at all).
Here are 2 sample pieces of data.

<values>
<v n="Billable">50</v>
<v n="CheckDate">04/29/2019</v>
<v n="CheckNumber">12</v>
<v n="Paid">35</v>
<v n="PaymentMethod">Check</v>
</values>

<values>
<v n="Billable">3617.85</v>
<v n="Paid">500</v>
<v n="PaymentMethod">Cash</v>
</values>

Ultimately, the goal is to have a formula and to be able to search for the CheckDate, CheckNumber, or Amount.
At this point, I am not certain that even If I can isolate the variables, that the variable can be made into parameters for searching...

I have researched and attempted various string variables but without success.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

If that doesn't work, what version of Crystal are you using?

I know there are limitations on how a memo field can be used in Crystal formulas. You may have to start with a formula that just grabs the first 500 characters.

I don't think memo fields can be used in report filtering but there may be away to use formulas to do it.

mlmcc

Justin Blanchard

ASKER

Thanks very much Norie,
with your help I am able to breakout the data I need.

StringVar strTest := {_invoice_transaction1.Extra};
ExtractString (strTest,'"CheckDate">',"</v");

... but, unless there is a workaround, I do not think it is possible to use my formula as a parameter...

and if so, I'll be picking your guidance as a solution.