Sorting by Employee No on report

VICTORIA_007
VICTORIA_007 Member Posts: 7
Hello,
I have a problem with sorting on report in classic nav 2009 sp1. The Report is with DataItem: Dimension Value and indent table Employee. So it is group by Dimension and the Employees for every dimension are sorted like this: 1;10;100;2;21;3;300;4..... I want them to be sorted like this ascending : "No." : 1;2;3;4;10;21;100;300....

Please can anyone help me with this?

Answers

  • Rishi1109
    Rishi1109 Member Posts: 43
    Have you tried using
    SORTING(No.) ORDER(Ascending)
    in DataItemTableView of Employee ??
    Thanks and Regards
    Rishi
  • ppavuk
    ppavuk Member Posts: 334
    ==1;10;100;2;21;3;300;4===

    It is very common confusion, you are expecting code datatype to be sorted as integer. In fact it is how the SQL sort the numbers stored as text or code. If you try to sort integers - the thing will work like 1,2,3...9,10,11...19,20,21. But when you sorting code field - the 1, 10, 100 always closer to A rather 2, 20, 200, which is perfectly fine. If you want to sort your dimensions A to Z then you have to define them as 001,002, 020, etc, maintaining the same number of characters for all records...
  • VICTORIA_007
    VICTORIA_007 Member Posts: 7
    Thank You,
    I tried with SORTING(No.) ORDER(Ascending) on Data Item = Employee but No. in Employee table is of DataType Code so the result is the same as sorting in navision: 1;10;100;2;21;3;300;4.... It is not working.
    If I define a variable gInt and put the values of No. in it as integer as EVALUATE(gInt,Employee."No."); I do not know how to use this to sort? Please any idea?
  • ppavuk
    ppavuk Member Posts: 334
    No way to sort on global integer variable. read what i said before.
  • MBerger
    MBerger Member Posts: 413
    What you can do is set the "SQL Data Type" property of the field in the Employee table to "Variant". Then it will sort properly.
  • ppavuk
    ppavuk Member Posts: 334
    MBerger wrote:
    What you can do is set the "SQL Data Type" property of the field in the Employee table to "Variant". Then it will sort properly.

    :thumbsup: Wow, didn't know this before! Thanks!
  • VICTORIA_007
    VICTORIA_007 Member Posts: 7
    THANK YOU MBerger, THANK YOU A LOT!!! :)
    IT IS WORKING! IT IS WORKING!
    You make me very happy with SQL Data Type: Variant.

    Best Regards

    Victoria
  • geordie
    geordie Member Posts: 655
    With SQL Data Type = Variant take in consideration these two remarks:
    - If you have dimension values like 1, 2, 15 will not be possible to insert records with not significant zeros (like 01, 02, 015)
    - String will be placed before numbers.