Removing group from sorting or to get latest date only

[help2003]

Hi

I am doing a report with a latest date and other info.
table1 Uniqueid and Table2 latest date
one Id can have multiple transactions, but I need only latest date.
I can do this by group and suppress other dates,
but report out put sort by ID, I need sort by aging which is (latest date - closed date).
is there any way i need to groupbyID to get latest date and removing group from sorting and use the formula field.

I know other option is creating view at backend(which is not possible with IT for now)

Thanks in advance

 

[synapsevampire]

Create a group by the ID, and in the report->Edit Selection Formula-Group place something like:

{table.date} = max({table.date},{table.ID})

This will limit the rows.

-k

 

[lbass]

If I understand you correctly, you only want to display the most recent record AND you want your groups sorted by a formula {@diff}:

{table2.latest date}-{table.2.closed date}

I think the only way you can do this is by doing a subquery in your SQL and then using a topNsort. You could try the following: Go to "Show SQL Query" and at the end of the last WHERE clause add (you don't need the "and" if it is the only WHERE clause), substituting your field names for "Latest Date" and "Unique ID" and your Table2 name for "Table2":

and Table2.`Latest Date` = (SELECT MAX(AKA."Latest Date&quot FROM Table2 AKA where AKA."Unique ID" = Table2."Unique ID&quot

This should return only the maximum record per ID. Then select {@diff} in the details section and insert a maximum on it. Go to report->topN/group sort and choose "Maximum of {@diff} and choose ascending or descending order, depending on your needs.

This tests out with 8.0, but you would have to take a different approach in 9.0, since the "Show SQL Query" as I understand it cannot be changed directly, although 9.0 provides equivalent options (which I cannot explain, since I don't have 9.0).

-LB

 

[lbass]

PS - Save your report under a different name if you try my suggestion, as once you alter the SQL statement, it can affect your ability to edit selection criteria after that.

-LB

 

[help2003]

Thanks for replies.
synapsevampire,
I told that if I use group(which I am using right now)I cann't get to sort by my @diff.
lbass,
I tried your suggession also before.
my query with sub query is working in sql Plus,
when I use the same query in Crystal 8.5 , I am getting
ODBC oracle driver error.
It says invalid column name. I am still working on that.
lbass,
One more question, If my query works,
I need to use Maxdate to do (currentdate/dateresolved-Maxdate) and i need to right a if to check dateresolved is available or not. YOu mentioned I cann't format,
what is that mean?? I cann't right formula after I use query?? then query doesn't help me
Thanks
in Advance

 

[lbass]

Your ability to edit the record selection formula could be affected.

If you use the subquery approach, only one record will be returned per unique ID, so that means that you will only be able to work with fields that appear in that record. I assumed that the closed date (resolved date) was a separate field that would appear in that record. If the date field is used for both latest date and resolved date, then you should not use the SQL approach.

I can't help you with driver issues or the more technical aspects of the subquery approach--someone who has this expertise will have to jump in. I don't have enough knowledge in this area to explain why the approach might not work for you--I've only recently figured out how to do this myself. I do think it might be the best method for your particular reporting need though.

-LB

 

[help2003]

lbass,

Ignore my previous post.
My report is working fine now.
I used sql query with subquery.
Thanks