ORDER BY IN DATA SET LOBBY

hi @Rpauley 

you need an order by clause like this

case when state = ‘Planned’ then 1

when state = ‘Released’ then 2

when state = ‘Reserved’ then 3

…

end

Use column “objstate” for the order by logic (not “state” which is a translated value in the language the user runs) as you already have done in the condition.

You can also use decode if you like in the ORDER BY:

DECODE(objstate, 'Planned', 1, 'Released', 2, 'Reserved', 3, 'Picked', 4, 'In Transit', 5, 'Arrived', 6, 'Received', 7)

If you want the condition to be cleaner and also work for other languages than English (if needed) you can do like this:

objstate IN ('Planned', 'Released', 'Reserved', 'Picked', 'In Transit', 'Arrived', 'Received')

@paul harland , @Tomas Ruderfelt 

Gentlemen thank you, i have tried both your ways and both work in the Data Set Preview, how ever when i build the element it will not take using either way, here is screen shot of the X i get when i preview, any thoughts or suggestions as to why it wont take

If you hover over the X with the mouse pointer you will get a small error text under the pointer.

Can you show us that it will be easier to see the problem.

It might be tricky to get a picture of it since the text disappears automatically after a couple of seconds or if you move the mouse. So just do a print screen directly.

Example:

Error when using

DECODE(objstate, 'Planned', 1, 'Released', 2, 'Reserved', 3, 'Picked', 4, 'In Transit', 5, 'Arrived', 6, 'Received', 7)

Error when using

order by case when state = 'Planned' then 1

when state = 'Released' then 2

when state = 'Reserved' then 3

when state = 'Picked' then 4

when state = 'In Transit' then 5

when state = 'Arrived' then 6

when state = 'Received' then 7

The Matrix element is automatically adding a GROUP BY to the SQL-statement with the columns specified for X-Axis and Y-Axis. So the Order By we have added must contain only the X or Y Axis columns.

Which database column is the “DO STATE” column you use on the X-Axis? I guess it is not the OBJSTATE column? If it is the column named STATE  you should either change the “DO STATE “ to OBJSTATE or change the order by part from OBJSTATE to STATE (That will only work in English.).

I changed it to State and now get the below

I changed it to the below and it is working now, thank you for all your help

(DECODE(state, 'Planned', 1, 'Released', 2, 'Reserved', 3, 'Picked', 4, 'In Transit', 5, 'Arrived', 6, 'Received', 7))