I need a bit more flexibility out of crystal reports..

[cjard]

Hi

I am using CR8.5 (though i have a copy of 10 that I have not installed yet because work only just bought it) and i am trying to create a crosstab query out of the following scenario:


Imagine we have a single database table that records shop purchases:
Field1: Date of transaction in YYYYMMDD format
Field2: The ID of the shop
Field3: The amount of the transaction


Now, i want a yearly crosstab report with the months across the top (12 columns)
and the shops down the side, so i can see how much each shop did per month over the last year

My problem comes because I cannot find a way to make CR trim off the last 2 digits of the date, which would remove the days from the grouping and group by months:
200501
200502
...

At the moment, im getting 365 columns because the transactions are group[ed by day rather than month:
20050101
20050102
...

How can i change this? I'm using an Oracle back end DB and "Group on server" is ticked

Thanks

[cjard]

I've installed Crystal 10, and started playing around. I used a Formula Editor to create a new formula that simply took a Left(date, 6) reducing 20050101, 20050102 etc to just 200501
Then i used the formula for the columns on the crosstab rather than the record date field itself

etc.. and it *seems* to work as I want.. If anyone has any comment on a better way to do this, please let me know

tia