| Author |
Message |
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
   Topic: Changing Date Format to View
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.
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Apr 2011 at 3:55am |
|
for clarity, is your DB field a string type or date/datetime field?
|
|
IP Logged |
|
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
Posted: 05 Apr 2011 at 5:28am |
Hi,
I will give this a try. Many thanks for your help.
Regards,
Chris Wolf.
|
|
IP Logged |
|
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
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.
|
|
IP Logged |
|
|
|
