Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Faith is the substance of things hoped for, the evidence of things not seen
 
Hebrews 11:1
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

13
June

How Do I Display the Ordinal of the Date in Crystal Reports XI?

During a recent Crystal Reports XI training session a delegate asked, "How do I display the Date Ordinals Superscript?"

Unfortunately it is not possible to display the date ordinals i.e. 13th June 2012, using any of the formatting options, including using HTML formatting, as the   superscript tag is not recognised within any current version Crystal Reports.


Solution

  1. Create 3 formulas to split the date

    2. Day :-

    WhileReadingRecords;
    NumberVar MonthDay := Day (CurrentDate);

    Ordinal :-

    WhileReadingRecords;
    NumberVar MonthDay;
    Stringvar Ordinal:=
     
     (if MonthDay in 4 to 20 then 'th' else
    if remainder (MonthDay , 10) = 1 then 'st' else
    if remainder (MonthDay , 10) = 2 then 'nd' else
    if remainder (MonthDay , 10) = 3 then 'rd' else 'th')

    Month Year :-

    WhileReadingRecords;
    Totext(CurrentDate , "MMMM yyyy")


  2. Add the 3 formula fields to the report next to each other

  3. Select the Ordinal formula field

  4. Change the font size to 7 pts



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

hjs

25
September
Hi Thanks for sharing this. I've condensed it into one formula based on your solution

_______________________ whileReadingRecords; stringVar th := ChrW (7511) + ChrW (688);
stringVar st := ChrW (738) + ChrW (7511);
stringVar nd := ChrW (8319) + ChrW (7496);
stringVar rd := ChrW (691) + ChrW (7496);

numberVar MonthDay := Day (CurrentDate);
stringVar Ordinal:= ( if MonthDay in 4 to 20 then th else if remainder (MonthDay , 10) = 1 then st else if remainder (MonthDay , 10) = 2 then nd else if remainder (MonthDay , 10) = 3 then rd else th );

ToText(MonthDay, 0) + Ordinal + ' ' +Totext(CurrentDate , "MMMM yyyy")

++++++++++++++++++++++++++++++++++++
2

Julia Emelogu

30
September
Hi hjs, Glad we could help you find a satisfactory solution to this problem. Julia
3

suvidhya

17
December
Solved ---- Thank you for posting this great solution
4

Di

19
March
Hi hjs, I try also with your solution above using chrW. But why some ordinal characters showing 􀀀, like superscript 'd', 't', etc. Please advise. Thanks,
5

Julia

20
March
Hi Di, If the formula results in the character 􀀀 rather than the ordinal, this is usually due to the font being used by the formula. Julia
6

Di

21
March
Hi Julia, I've tried with some font styles. The problem occurs only in Windows XP. It's working fine in Windows 7. Do you have any idea for this? Thanks.
7

Julia

22
March
Hi Di,

When working with the function Chr or ChrW, there are two reasons why crystal reports displays the 􀀀 character in the resultant report.

1. The font being used, so to minimise this I would suggest using the fonts Arial, Tahoma or Verdana.

2. The ASCII/unicode number entered within the Chr/ChrW function is not recognised/supported by the font being used.

This is more of a problem in Windows XP, as in Windows 7 the unicode may be part of the standard font set.

This is the reason why in my original post I did not use the Chr/ChrW function, and just used raw text.

If you cannot get hjs solution to work, I would suggest that you use the orginal post, to achieve the desired outcome.

Julia
8

Jason Didner

26
April
That was very helpful for my project in Crystal. Only modification I made was to include the MonthDay variable in the same formula, as follows: WhileReadingRecords; NumberVar MonthDay := Day ({?SubmitDate}); WhileReadingRecords; NumberVar MonthDay; Stringvar Ordinal:= (if MonthDay in 4 to 20 then 'th' else if remainder (MonthDay , 10) = 1 then ToText(MonthDay,"0") & 'st' else if remainder (MonthDay , 10) = 2 then ToText(MonthDay,"0") & 'nd' else if remainder (MonthDay , 10) = 3 then ToText(MonthDay,"0") & 'rd' else ToText(MonthDay,"0") & 'th') Thanks for posting this!
9

Woodrowfooryfum

20
November
What can you do for New Years?

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

77 + 96 =