Sorting by Employee No on report

VICTORIA_007

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?

Rishi1109

Have you tried using
SORTING(No.) ORDER(Ascending)
in DataItemTableView of Employee ??

ppavuk

==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

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

No way to sort on global integer variable. read what i said before.

MBerger

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

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

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

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.