How do I modify the SQL query for the report?

[Snakeroot]

I'm using CR XI and I see where to view the SQL statement for the report, but is there any way for me to modify it?

For example, right now it's:

SELECT DISTINCT "InvoiceLine"."BillAddressAddr1", 
"InvoiceLine"."InvoiceLineItemRefFullName", 
"InvoiceLine"."InvoiceLineDesc", "InvoiceLine"."InvoiceLineQuantity", 
"InvoiceLine"."InvoiceLineSeqNo", "InvoiceLine"."RefNumber", 
"InvoiceLine"."TxnDate", "InvoiceLine"."Memo"
 FROM   "InvoiceLine" "InvoiceLine"
 WHERE  "InvoiceLine"."Memo" LIKE 'tank service%'
 ORDER BY "InvoiceLine"."BillAddressAddr1", "InvoiceLine"."RefNumber"

I would like to change the ORDER BY line. How do I go about doing this? Thanks for any help!

 

[lbass]

You can't change the query in "Show SQL Query" in XI. Go to report->sort records and change the sort order there. This change will be reflected in "Show SQL Query."

In XI, you can use "Add Command" (database->database expert->your datasource->add command) to create a SQL query to use as a datasource for your report.

-LB

 

[Snakeroot]

Thanks LB...

I found report>sort records, but apparently since I set a sort order when I setup the report initally, it won't let me change the sort order in there. All the buttons are greyed out. The only thing it lets me do is add an additional way to sort, not reorder the fields I'm already using to sort.

I'll look into the Add Command. That may be what I'm looking for, if it replaces the initial SQL query. Thanks

 

[Snakeroot]

Well, I stumbled on my solution, but it was right next to where you pointed me LB, so I'll give you the credit I was actually needing to go to group expert instead of record sort expert. Thanks for pointing me in the right direction!

 

[dgillz]

Yes if you group your report by a given field then by definition it must sort by that field first.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown