Print Page | Close Window

Changing Date Format to View

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12781
Printed Date: 02 May 2024 at 2:43pm


Topic: Changing Date Format to View
Posted By: chris.wolf
Subject: Changing Date Format to View
Date Posted: 01 Apr 2011 at 5:41am
Hi,
 
Sorry if this has been covered already.
I have calculated a date and month numerical value in a cross tab report using this formula -
 
ToText({POPOrderReturn.DocumentDate}, "yyyy") + CHR(13) + ToText({POPOrderReturn.DocumentDate}, "MM")
 
But when the report generates I want the month to change back to the alphabetical value but still sort by the numerical value.
 
How do I do this?
 
Many thanks,
 
Chris Wolf.


Replies:
Posted By: lockwelle
Date Posted: 04 Apr 2011 at 3:15am
since cross tabs deal with summaries of data, if this is to be the top column, I can't think of a way, as the top row is both the sort and the display for the column.
 
I haven't used cross tabs much, but if they are like other CR reports, I would have the grouping be on the numerical, and then display another.  If needed, group on numeric value and then create a second band for the group header with the year/month spelled out.  Suppress the first band with the numeric...
 
that might work.
 

Posted By: DBlank
Date Posted: 04 Apr 2011 at 5:04am
Why are you converting to text? You can group the date field by Month and Year without conversion, keep it in the correct date order and change the way the header looks using date formatting.

Posted By: chris.wolf
Date Posted: 05 Apr 2011 at 2:55am

Hi,

Many thanks for the replies.
I have converted to text because when showing Year(String) & CHR(13) & Month(String) the values show as (Year).00 and I can't find where to change the date format.
 
I have tried changing the Display String option with a formula to change the way the date displays but in each column it has the same year and same month values i.e 2010 MAR.
 
What I was looking for was a way of sorting the cross tab columns by the numerical value but display the text equivalent.
 
Regards,
 
Chris Wolf.

Posted By: DBlank
Date Posted: 05 Apr 2011 at 3:55am
for clarity, is your DB field a string type or date/datetime field?

Posted By: chris.wolf
Date Posted: 05 Apr 2011 at 4:04am
The field from the database is a date time field. But for the report I only need to show the year and the month.

Posted By: DBlank
Date Posted: 05 Apr 2011 at 4:20am

OK there a few things to do here.

for grouping in the crosstab add the datetime field as the row (or column) field. The "Group Options" button is now available for use. Click on it.
"The Row will be printed for" option needs to be changed to "for each Month". Click OK.
Now in the CT, select any of the monthyear fields,
right click on it,
select Format Field,
select customize
select date tab
select order as "YMD"
change Month to "03"
select Day as "None"
select Yaar as "1999"
select OK
Select OK

Posted By: DBlank
Date Posted: 05 Apr 2011 at 4:26am
note you can alter the way the datetime field looks in the report canvas (or group headers) by reformatting it using the same steps as with the CT header.

Posted By: chris.wolf
Date Posted: 05 Apr 2011 at 5:28am
Hi,
 
I will give this a try. Many thanks for your help.
 
Regards,
Chris Wolf.

Posted By: chris.wolf
Date Posted: 07 Apr 2011 at 3:33am
Hi,
 
This helped a lot many thanks for this suggestion.
One further question how do I get a carriage return between the year and the month so they are on 2 lines?
 
Many thanks,
Chris Wolf.

Posted By: DBlank
Date Posted: 07 Apr 2011 at 3:40am
In the Crosstab row headers or somewhere else in the report?

Posted By: chris.wolf
Date Posted: 11 Apr 2011 at 4:09am
Hi,
 
Ok managed to get the carriage return to happen but the formatting is now screwed up again.
 
Under culumn group options on cross-tab expert.
The formula I have used to insert the carriage return is -
Date({POPOrderReturn.DocumentDate}) & CHR(13) & Date({POPOrderReturn.DocumentDate})
 
It shows the date as DD/MM/YYYY on both lines how do I get it to accept the formatting that you suggested to me before?
 
Many thanks,
Chris.

Posted By: DBlank
Date Posted: 12 Apr 2011 at 4:36am
Not sure where you are at with this.
The carriage return is tricky. As far as I know you cannot break a line in a date field type and maintain the characteristics of a date field.

Posted By: chris.wolf
Date Posted: 12 Apr 2011 at 4:59am
Hiya,
 
I managed to get it working with the following formula in the group options.
 
ToText({POPOrderReturn.DocumentDate}, "yyyy") & CHR(13) & ToText({POPOrderReturn.DocumentDate}, "MMM")
 
Many thanks for your help on this subject.
 
Regards,
 
Chris Wolf.


Print Page | Close Window