How to group a column with datatype = DATETIME?

[voirin]

Hi, I was hoping if someone could help me. I have a main report that displays appointment starttimes for patients, along side with other details. The starttime is of datatype DATETIME. Now I would like to group by this column. But I am finding that Crystal encompases all the results into a single group. An example output of the column is as follows:

Sunday, 1 January 1899 9:55AM
Sunday, 1 January 1899 10:30AM
Sunday, 1 January 1899 10:30AM
Sunday, 1 January 1899 11:10AM

The system actually stores the real *date* in another column in the database. Hence the "Sunday, 1 January 1899" is bogus. Obviously this is not the best design (I'd change it if I had access to the code). I thought that when I perform a grouping on this column it would (as in the above case) create 3 groups:

group1 -> Sunday, 1 January 1899 9:55AM
group2 -> Sunday, 1 January 1899 10:30AM
group2 -> Sunday, 1 January 1899 10:30AM
group3 -> Sunday, 1 January 1899 11:10AM

But it isnt doing this. Instead its putting all the above into one group:

group1 -> Sunday, 1 January 1899 9:55AM
group1 -> Sunday, 1 January 1899 10:30AM
group1 -> Sunday, 1 January 1899 10:30AM
group1 -> Sunday, 1 January 1899 11:10AM

I have tried to "Format Field..." this column to display only the time i.e. customize > Date and Time > Order: Time. However all the data is still being grouped into one group.

I know I can do a sort (which I have done) but I want to have a line drawn between each group, and have 'Suppress if Duplicated' i.e. I want something like the following displayed:

+------------+-----------+----------+
|starttime | patient | matter |
+------------+-----------+----------+
|9:55am | 3 | 45 |
+------------+-----------+----------+
|10:30am | 7 | 67 |
| | | 101 |
+------------+-----------+----------+
|11:10am | 12 | 76 |
+------------+-----------+----------+

Any help most appreciated!

cheers,
voirin

 

[voirin]

I managed to get around this by creating a formula field with the following: time({TIMESLOT.STARTTIME})

Then I insert this formula field in the report, group by this formula field, and suppress it.

cheers, v