Selecting Specific Records

[BRL123]

I am working with Crystal 8 and an ODBC to Oracle

I have table of test results and I need to pull back only the records that have more than one entry in table(for their ID) and then of those..I need to only select the 2 most current records based on testdate field. Is there a way to use imbedded sql queries to accomplish this?

 

[synapsevampire]

Not with CR 8. OK, there is a method, however you'll want to be careful and realize that you cannot pas any parameters or pass filtering ot the database.

Using th ADO or RDO connection there is an option to paste in a query, which if your SQL is string, you can write this out in a SELECT, or et your dba to assist you.

Using crystal all you cano is filter the rows.

Group by the ID.

Use Report->Selection formula->Record and palce:

count({table.field},{table.id}) > 1

Now we've limited them to only those with more than one row per ID.

Now for display purposes, you can cheat this by Sorting the rows by the date (Under the Database menu in CR 8 I think) and then by right clicking the group footer and selecting insert section below.

Place the fields required in the group footer B. This will the latest row for the date field for each ID.

Then create formulas for the Group Footer A of:

whileprintingrecords;
if previous({table.id}) = {table.id} then
previous({table.field})

Where {table.id} is the field you need for the row preceding the last row for each ID. You'llneed as many formulas as you have fields in report footer B.

Then right click group footer A and select format section and in the X2 next to suppress place:
previous({table.id}) <> {table.id}

-k

 

[dgillz]

I believe sv meant:

Use Report->Selection formula->group and place:

count({table.field},{table.id}) > 1




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

 

[synapsevampire]

Good catch, Don, that is correct.

-k