Crystal Report Specified Sort Order not working correctly in InfoView

[Former Member]

I created a Crystal report which groups on division then project id. However, I want the report to be sorted by division, project date, then project ID. The issue is the project date needs to be formatted and sorted as MM/YY. So if 2 projects have the same MM/YY, the sorting should look at the project id to determine order. Now, the report looks at the full mm/dd/yyyy to determine order. Any way to use the date field formatted as MM/YY and sort on the formatted value, not the underlying data?

Example data

Date               MM/YY Format     Proj ID

10/12/2012          10/12                 10

10/20/2012          10/12                 05

using this, the display should be

MM/YY Format          proj ID

10/12                         05

10/12                         10

Any help would be greatly appreciated.

[former_member209066]

Hi,

Change the Query in Order By ProjId Asc

Thanks,

Nithi

[Former Member]

The report is sorting the projects in ascending order. The issue is the dates are sorting by the underlying data, not the formatted MM/YY. Thanks for your quick response.

[former_member209066]

Hi,

SELECT RIGHT(CONVERT(VARCHAR(8), A.DocDate, 3), 5) AS [MM/YY] from OINV A

Thanks,

Nithi

[Former Member]

That's it. Thanks!

[Former Member]

That's it. Thanks!

[former_member209066]

HI..

Please Mark it answered

Thanks