I am trying to see if there is a way to have my Element list the Distro Order Status in a specific order, The order i am looking for is
Planned , Released , Reserved , Picked , In Transit , Arrived , Received
Screen shots below are of what order they are now along with a screen shot of Data Screen
+24
hi
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')
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))
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.