Advanced Sort of Report

[Mpike926]

Thanks for taking the time to read. Ill do my best explaining.
I have a report that lists all of our assets on a portfolio basis
There are some assets that are related (could be a first and second mortgage for example) and there is a field that the asset manager fills in to relate them together. for example they could assign the related assets a value of CO5 and then in the report, when I sort by the RelationshipID field, it puts them all together.
How the report is set up right now to first sort by relationship Ids, and then after all of the relationships, sort by highest to lowest unpaid balance.
what I would like to do is start sorting by UPB but mix the relationships in between where they fit.
Lets say I have these assets
Asset-UnPaid balance
Ada Inc -2,000,000


1201 S Western-1,000,000
Colony Corporate 1,250,000
Colony Corporate 125,000

The two colony corps are related, right now the table would sort in this order (by relationship first then all others highest to lowest UPB)
Colony Corporate 1,250,000
Colony Corporate 125,000
Ada Inc -2,000,000
1201 S Western-1,000,000.

My question is how would I get it to sort like this
Ada Inc -2,000,000
Colony Corporate 1,250,000
Colony Corporate 125,000
1201 S Western-1,000,000.
etc.
So that way the balance is in decending order while also keeping the relationship group together?
I dont even know if this can be done, but if you have any input feel free to share
thanks again!
-Matt

[Mpike926]

Hi I want to sort a report by Highest to lowest UPB while keeping related loans together. The related loans have a relationship Id that groups them together,
However, my report breaks our each related loan individually instead of grouping them all together and continuing on with the report.
Does anyone know how to achieve these results?
More or less Im looking for highest to lowest upb, and if a relationships greates UPB comes some where in the middle of the report, I want the whole group to be placed there accordingly.
Thanks for the help

[June7]

Is this the same db from your other thread? There is only one report in that db. Is it the one of interest here?

Would help testing if there were multiple borrowers and loans but you reduced the data to one record.

[Mpike926]

Yes it is the same database. and there are many reports involved, and many more assets. the asset that you have is just one that I left in there (I changed amts around for obvious reasons) but yes, that is the one We are working in.
let me send you another version with a fwe more borrowers.

[Mpike926]

June, here is a database with more assets.
I have changed critical infomation around.
The report seems different because when i was deleting fields out of the tables to shrink it down small enough, i accidentally deleted some fields that were in the report, so I just removed them from the report as well (they dont need to be there anyways for what I am trying to accomplish)

Basically when you look at the report, it sorts from Highest to lowest UPB. But what I want to happen when a relationship comes about, I want that relationship to group together and continue to sort accordingly.
So looking at the report, Ada has no relationship, and its the highest UPB. which then next Mountain West, has relationship CO2. I would like all the other CO2 to come up under there, and then follow with Co21 and all of its related loans etc.

[June7]

If you want all the same Relationship records to list together, then must have the RelationshipID as the primary sort or grouping, then by UPB.

If you want the Relationship groups to sort by the max UPB in each group, that is another matter.

[Mpike926]

Ok makes sense. I think I may have thought of a way that I can still do this tho. What if I created a field that was called sort order, and in this field (currency field/or number) I could just put the current upb in it. the loans unpaid balance would populate into this cell, and if the loan had a relationship, the highest balance in the relationship would populate into all of the other related loans. Then I could sort by this new 'sort order' with the current upb being the next sort.
I created an excel document to show what I am trying to explain. Can something like this be achieved via VBA? it would essentially accomplish what I need to do, and granted I could do this by hand every week, but then comes human error, and when you have 800 assets... It could take a while

Sort Demo.zip

[June7]

That is the 'another matter' I alluded to. Could possibly use an expression with DMax or DSum function in query to construct a field to use for grouping criteria in report.