sort question

[kimchavis]

I am trying to sort a report but I have a problem, I was hoping someone could help me with.

My report is grouped by loan number and under each loan number there are multiple status dates with users attached to them.

So it is set up like this:

GH - loan id 12345
D - status date 04/27/2005 user barnes
D - status date 04/25/2005 user smith

GH - loan id 14523
D- status date 03/22/2004 user smith
d- status date 03/15/2004 user jarrell

GH - loan id 15412
d- status date 04/22/2005 user barnes
d- status date 04/21/2005

I want to be able to put the two loans id's in order by users with the latest status dates.

In other words. I want loan id 12345 first and then 15412 next because barnes had the most recent status date and I want all of his latest statuses together. I want the loan id' group to stay in tact, with the other users still under them. Does this make sense?

Im using v 8.5. I have tried everything, and am not sure what else I can do to accomplish this. Please help.

Thanks!

 

[BobSuruncle]

Create a summary - Maximum of Status_Date (right click on the field, choose insert summary)
Once the summary is created, go to the REPORT menu and choose TopN Group Sort Expert.
For your loan group, choose Sort All in Descending order based on the Maximum of Status_Date.



Bob Suruncle

 

[kimchavis]

That would be good if I just wanted to sort by status date, but what I really want to sort by is user with the latest status date.

I want all of the loans with barnes as the max status date together,, all of jarrell with the max status date together etc. But I still want all of the status dates/ users to fall under each loan number. The only user that is considered in the sort is the max status date user position.

With the new sort, my report should look like:

GH - loan id 12345
D - status date 04/27/2005 user barnes
D - status date 04/25/2005 user smith

GH - loan id 15412
d- status date 04/22/2005 user barnes
d- status date 04/21/2005 user jarrell

GH - loan id 14523
D- status date 03/22/2004 user smith
d- status date 03/15/2004 user jarrell

Thanks,
KIM

 

[lbass]

I think you could do the following. Create a SQL expression {%maxdate}:

(select max(AKA.`statusdate`) from Table AKA where
AKA.`loanID` = Table.`loanID`)

Replace "statusdate" and "loanID" with your exact field names, and replace "Table" with your table name. Leave AKA as is, since it is an alias table name.

Then in the record selection formula use:

{table.statusdate} = {%maxdate}

Next insert a group on {table.user} and then a second group on {table.loanID}.

Then insert a subreport which is grouped on loan ID and has the detail fields you want displayed. Link this to the main report on loan ID and place it in the Group #2 (LoanID) Header. You can suppress the Group #1 (User) Header.

This should give you an alphabetical display of users with loan IDs where the user is the most recent user for that loan ID, but with the display of all users and dates in the detail section.

-LB

 

[kimchavis]

When I put in the first formula in the SQL Expression Editor, it gives me : "ODBC error: [MERANT}[ODBC Oracle driver] [Oracle] ORA-01747:invalid user.table.column, table.column, or column specification.

The formula I put in is:

(select max(AKA.'status_date') from pscat_loans_documents_relation AKA where
AKA.'loan_id' = pscat_loans_documents_relation.'loan_id')

Please advise.

 

[lbass]

The single quotes should actually be backward slanted ones as found on the upper left key of your keyboard. However, the punctuation and syntax for SQL expressions can vary by datasource and connectivity, and I can't really tell you how it will work for you.

Try changing the single quotes first. Then you could try adding a field from the field list in the SQL expression editor and see how the punctuation appears and then mimic that in the SQL expression. The syntax I gave you works with the Xtreme database, which I think is Access-based. For the database I ordinarily use, field names are enclosed in double quotes. You could also identify your datasource and connectivity and see if someone else familiar with your setup can recommend the correct punctuation/syntax.

-LB