Search code examples
crystal-reports

Crystal Reports: group by one field, sort by another

I have an "Orders" table:

+---------+-------------+
| OrderID | InvoiceDate |
+---------+-------------+
| 1       | 15/02/2022  |
| 123     | 20/01/2022  |
+---------+-------------+

and a "Rows" table:

+---------+-------+--------+
| OrderID | RowID | Value  |
+---------+-------+--------+
| 1       |     1 | 100,00 |
| 1       |     2 | 200,00 |
| 1       |     3 |  50,00 |
| 123     |     1 |  10,00 |
| 123     |     2 |  20,00 |
+---------+-------+--------+

As shown in the example, it may happen that an order with a higher OrderID value has a lower InvoiceDate value.

In my report I would like to show each order, along with the sum of each row's value, ordered by date:

+-------------+---------+--------+
| InvoiceDate | OrderID | Value  |
+-------------+---------+--------+
| 20/01/2022  | 123     |  30,00 |
| 15/02/2022  | 1       | 350,00 |
+-------------+---------+--------+

My problem is that in order to create an OrderValue formula field with Sum({Rows.Value}, {Orders.OrderID}), I first need to group by Rows.OrderID

But this way rows are sorted by OrderID, and I don't know how to sort them by date.

Solution

  • Add a group total of Maximum (or minimum, or Average) Order Date by Order ID.

    Go to the menu option of Report, Group Sort Expert... and sort the groups by that total.