Dynamic sorting of strings and dates

building reports desktopintelligence
1

Hi,
I have a requirement, where I have to dynamically sort the report based on the user selection. I have strings and dates in the user selection and am sorting accordingly.
Have no issues with strings but as soon as I add date to it, in the If then Else condition, I am getting incorrect datatype messsage DMB0003.
Tried to use FormatDate(Date,“mm/dd/yyyy”), but dates are not sorted correctly, it is sorting alpha numerically just as it does with strings.
Eg.
1/1/2001
11/1/2002
12/1/2001
2/1/2000

I am using BO 6.1b just in case you need the version.
Any ideas, please let me know.

Thanks,
Vinay

Vinay (BOB member since 2003-07-14)

2

I could not solve this issue, but found a work around which is working fine now.
Created a variable to check for user response for date. If its date then sort on it else equating it to some default date (hard coding). This is the first sort.
Then created one more variable to sort on the string fields.
If the first sort is not on date, then it will sort on the second variable.

Any other suggestions, please let me know.

Vinay

Vinay (BOB member since 2003-07-14)