how to group by one column, sort by another column

[wprmdev]

I have the same problem. I have a dataset with the following structure:

SupplierId, SupplierName, ProductId, ProductName.

I need to group by SupplierId and sort by SupplierName.

And the DataBand sorts by ProductName.

Why not group by SupplierName? We can't guarantee that the name is unique. The ID is responsible for that.

This is a common situation. To me, it doesn't make sense to use a name or description instead of the ID.

Is there a solution for this?

I am using version: 2024.3.2

[Lech Kulikowski]

Hello,

What is the problem you have? You can use the Sorting option.

Thank you.

[wprmdev]

The group sorting option does not allow you to specify which field to sort by.
Since I grouped by SupplierId, the sorting is based on that. I need to sort by SupplierName, and keep the group by SupplierId.

[Lech Kulikowski]

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.

[wprmdev]

Good morning. Attached is the example as requested.

[Lech Kulikowski]

Hello,

Could you explain your issue in more detail, what is wrong with the sent report?

Thank you.

[wprmdev]

I want to group the data by the supplier ID but sort the data by the supplier name.

I can't group by name because the user may have changed the name and there will be records with the same ID but different name because we keep a copy of the values.

Ex.: User does:
- Register supplier: 10 Supplier AZ
- Register product: ProdA 10 Supplier1
- Change supplier name: 10 Supplier AE
- Register product: ProdB 10 Supplier AE

I notice that I have two products from the same supplier (ID: 10) but different name. Therefore, I can only rely on the ID to group. But the order of the data must be by name, not by ID.

In the report, when grouping, I can change the order to ascending or descending. But the field used in the sort is always the same one used to group.

I need a way to say: Hey, group by ID but sort by Name.

The equivalent SQL would be:

Code: Select all

SELECT supplierId, ...
FROM products
GROUP BY supplierId
ORDER BY supplierName ASC;

[Lech Kulikowski]

Hello,

Unfortunately, it is not possible with GroupBands.

Thank you.