Link to home
Start Free TrialLog in
Avatar of Justin Blanchard
Justin BlanchardFlag for United States of America

asked on

Modifying Crystal Reports Query through Database Expert

Hello Experts,
Here is my issue.  I have a MySQL database that a report is being derived from. There is a field in one of the tables that is stored as XML. Previously, an expert gave me some great advice and I was able to create a formula that broke out one of the 3 pieces of data that I need.

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

While this formula gives me the CheckDate, as I suspected, I could not create a parameter field from the formula.
Unless there is a workaround that I have not yet deduced, my latest attempt is to go to the SQL query and modify the query prior to the data reaching the report.

I know I need to go to: Database:database expert: odbc: add command... and there I can modify the SQL.

So, that leaves me 2 questions: Where and or how can I see the SQL statement output so I know if my modifications are going to have the desired results. If I was using straight SQL I would probably add a line like: ExtractString(_invoice_transaction1.extra, '"Checkdate">',"</") as checkdate     -  to my query which I have copied from Crystal below.

 SELECT _invoice_transaction1.TransactionDate, _customer1.LastName, _customer1.FirstName, _customer1.MiddleName, _insurancecompany1.Name, _invoice1.ID, _invoicedetails1.BillingCode, _user1.Login, _invoice_transaction1.Amount, _invoice_transaction1.Taxes, _invoicedetails1.ReviewCode, _invoice_transactiontype1.ID, _invoice_transaction1.Extra
 FROM   (((((c01.tbl_invoice_transaction _invoice_transaction1 LEFT OUTER JOIN c01.tbl_invoicedetails _invoicedetails1 ON _invoice_transaction1.InvoiceDetailsID=_invoicedetails1.ID) LEFT OUTER JOIN c01.tbl_invoice _invoice1 ON _invoice_transaction1.InvoiceID=_invoice1.ID) LEFT OUTER JOIN c01.tbl_customer _customer1 ON _invoice_transaction1.CustomerID=_customer1.ID) LEFT OUTER JOIN c01.tbl_invoice_transactiontype _invoice_transactiontype1 ON _invoice_transaction1.TransactionTypeID=_invoice_transactiontype1.ID) LEFT OUTER JOIN c01.tbl_insurancecompany _insurancecompany1 ON _invoice_transaction1.InsuranceCompanyID=_insurancecompany1.ID) LEFT OUTER JOIN c01.tbl_user _user1 ON _invoice_transaction1.LastUpdateUserID=_user1.ID

Questions are:
Can I modify the output query like I am attempting? / Where or which program would I need to use to test the output?
Does Crystal's query even use syntax like:  ExtractString(_invoice_transaction1.extra, '"Checkdate">',"</") as checkdate    ('as' being the syntax in question)

If there is another way that you experts see that I can use the data from the XML field as parameters, please advise.

Here are the contents of the XML field:

<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>

Thanks in advance!!
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Justin Blanchard

ASKER

Mike,
Can you explain more about creating a query using the SQL from the report and adding a command to change the report to use that command...
what is that process or documentation for that process?
Moderators... please help..
I found my solution, and it was the result of BOTH of the above answers.  
Mike got me to the place where I could create my own SQL statement and slightWV gave me the genesis of the code I used.

Ultimately, for someone looking to use data from a Memo field as a searchable parameter in Crystal Reports, you need to create you own query and shorten the memo field Left(memo field, 255).  When the query is run, the resulting field can now be used as a parameter.

I used : left(extractvalue(_invoice_transaction1.Extra,'/values/v[@n="CheckNumber"]'),20) as checknumber for example to populate my new checknumber field.

Thank you both so much!!  How do I select both as the answer?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Just select "This is the solution" to all the posts that helped you get the solution.
Thank you both!!!