Justin Blanchard
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.Ext ra};
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_tra nsaction1. extra, '"Checkdate">',"</") as checkdate - to my query which I have copied from Crystal below.
SELECT _invoice_transaction1.Tran sactionDat e, _customer1.LastName, _customer1.FirstName, _customer1.MiddleName, _insurancecompany1.Name, _invoice1.ID, _invoicedetails1.BillingCo de, _user1.Login, _invoice_transaction1.Amou nt, _invoice_transaction1.Taxe s, _invoicedetails1.ReviewCod e, _invoice_transactiontype1. ID, _invoice_transaction1.Extr a
FROM (((((c01.tbl_invoice_trans action _invoice_transaction1 LEFT OUTER JOIN c01.tbl_invoicedetails _invoicedetails1 ON _invoice_transaction1.Invo iceDetails ID=_invoic edetails1. ID) LEFT OUTER JOIN c01.tbl_invoice _invoice1 ON _invoice_transaction1.Invo iceID=_inv oice1.ID) LEFT OUTER JOIN c01.tbl_customer _customer1 ON _invoice_transaction1.Cust omerID=_cu stomer1.ID ) LEFT OUTER JOIN c01.tbl_invoice_transactio ntype _invoice_transactiontype1 ON _invoice_transaction1.Tran sactionTyp eID=_invoi ce_transac tiontype1. ID) LEFT OUTER JOIN c01.tbl_insurancecompany _insurancecompany1 ON _invoice_transaction1.Insu ranceCompa nyID=_insu rancecompa ny1.ID) LEFT OUTER JOIN c01.tbl_user _user1 ON _invoice_transaction1.Last UpdateUser ID=_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_tra nsaction1. 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!!
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.Ext
ExtractString (strTest,'"CheckDate">',"<
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_tra
SELECT _invoice_transaction1.Tran
FROM (((((c01.tbl_invoice_trans
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_tra
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 n="CheckNumber">12</v>
<v n="Paid">35</v>
<v n="PaymentMethod">Check</v
</values>
Thanks in advance!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 _transacti on1.Extra, '/values/v [@n="Check Number"]') ,20) as checknumber for example to populate my new checknumber field.
Thank you both so much!! How do I select both as the answer?
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
Thank you both so much!! How do I select both as the answer?
Just select "This is the solution" to all the posts that helped you get the solution.
ASKER
Thank you both!!!
ASKER
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?