How can I influence the sorting of attributes in a report?

3
The attributes (enums) are sorted alphabetic by caption of the enum. The data set is as follows: SELECT 'Geen' AS incassoStatus, COUNT(*) as sumIncassostatus FROM Wanbetalers.Verzekeringnemer AS verzekeringnemer WHERE IncassoStatus = 'E01_GEEN' UNION SELECT '1e termijnbrief' AS incassoStatus, COUNT(*) as sumIncassostatus FROM Wanbetalers.Verzekeringnemer AS verzekeringnemer WHERE IncassoStatus = 'E02_1E_TERMIJN_BRIEF_ONTVANGEN'
asked
1 answers
7

To further clarify the question, it looks like Mendix automatically sorts the results of an OQL query using UNION alphabetically on the first column. Take for example this query:

SELECT 'A' AS Col1 FROM System.User
UNION
SELECT 'C' AS Col1 FROM System.User
UNION
SELECT 'B' AS Col1 FROM System.User

The results of this very simple query will be one column ("Col1") and three rows, in the order: A, B, C.

I don't know how to prevent this sorting, but you can use the same mechanism to your advantage. For example:

SELECT 1 AS Col0, 'A' AS Col1 FROM System.User
UNION
SELECT 2 AS Col0, 'C' AS Col1 FROM System.User
UNION
SELECT 3 AS Col0, 'B' AS Col1 FROM System.User

Now "Col0" is the first column and this value will be used for sorting. Just hide this column in the form, and the (visible) result will be one column ("Col1") and three rows, in the order: A, C, B !

While this works, it's more of a workaround, so I'm wondering if there's a better solution.

answered