Hi!

I've got a problem with the grouping and sorting of a report.

The report shows a list of unpaid invoices. These invoices should be grouped by debtor, and sorted by age of the debt.

So this is what it should look like:


Debtor Age
x 240 days
a 239 days
a 40 days
g 239 days
o 220 days

But how do I achieve this?
If I put the group by debtor first in the group/sort by menu of the report, it puts the whole list in order of the debtor's name.
If I put the order by age first in the group/sort by menu of the report, it doesn't group at debtor level anymore...

How is this list sorted?

EDIT: Are you talking about these two entries?

a 239 days
a 40 days

If so, they are sorted if you have built the Days expression in the QBE grid. 2 is less than 4. It is probably sorting the values as a string.

To sort by the value, you need to include a numeric expression of just the number of days and sort on that.

First by the age of the debt, then by the Debtor's ID

The example got a but unclear after posting it. In the example you see that X has the oldest debt open, and no other. Debtor A has the second oldest debt open, and also a more recent debt open. So they need to be grouped. Then debtor G has the oldest debt, etc...

Does this make clear what I want?

I think you need to include an "Oldest Debt" column (possibly using a subquery or through joins) and then sort on that first. Then by Days.

DebtorID Age Oldest
X 240 days 240
A 239 days 239
A 40 days 239
G 238 days 238
O 220 days 220

Yup that's brilliance in it's simplicity! Got it to work now. Thanks!

NP.