KBase Article ID:c2010059

Article refers to:Crystal Reports 7

Symptom
How can you display a date field or a numeric field with superscript text in Crystal Reports?


For example:

There is a date field in Crystal Reports and you want to customize the date to show a superscripted extension.


December 3rd, 2001


You want the "rd" to appear smaller and slightly higher than the date field text.


When you attempt to customize the date you do not see an option for superscript style.


Resolution
In Crystal Reports, there currently is no option available to automatically format a field to display a superscript style. However, you can create a formula to display as superscript style in Crystal Reports.


===== USING A DATE FIELD =====


The following example is based on using a date field in Crystal Reports:


1. From 'Insert, select 'Field Object'. This launches the 'Field Explorer'.


2. Select 'Formula Fields', and click the 'New' icon. 


3. Type a name, such as ExtensionDate, and click 'OK'. This will launch the formula editor.


4. Create a formula similar to the following:


// This formula will parse the day value out of a date field 

// verify the value and then assign the appropriate script extension. 

//This formula will work on all number fields.


//script extension held in an array

stringvar array A := ["st","nd","rd","th"]; 


//verify the length of the parsed day value and assign it to a number variable.

//in this sample the reason for using LLength as a variable name is due to "Length" 

//being a reserved function name 

numbervar LLength := length(totext(day({Orders.Order Date}),0));


//changes the parsed day value to text, extracts the given number (1) of text characters 

//from the right side of the specified string and assigns it to a string variable

stringvar dDay:= right(totext(day({Orders.Order Date}),0),1);


//changes the parsed day value to text, extracts the given number (2) of text characters 

//from the right side of the specified string and assigns it to a string variable

stringvar xDay:= right(totext(day({Orders.Order Date}),0),2);


//there are 3 exceptions to the script extension, the number values 11, 12, 13 all have

// the "th" script. Comparative to 1st, 2nd, 3rd.

// the following steps check the values and assign the appropriate script.


if LLength = 2 and xDay = ["11","12","13"] then A[4] else 

if LLength = 1 and dDay = "0" then " " else 

if LLength = 2 and dDay = "0" then A[4] else

if dDay = "1" then A[1] else 

if dDay = "2" then A[2] else

if dDay = "3" then A[3] else

if dDay >= "4" and dDay <= "9" then A[4]

else 

if LLength = 2 and dDay >= "10" and dDay <= "20" then A[4]


5. Save and close the formula.


Creating and formatting the formula 

==========================


Ensure you are in Design view of your report.


1. From 'Insert', select 'Text Object'. Place the text object where you want to display the formula output. 


Formatting the Date Field

==================

A. From 'Insert', select 'Field Object'. This launches the 'Field Explorer'. 


i. Select the <Date.DatabaseField> and place it on the report. 


ii. Right-click the <Date.DatabaseField>, from the fly-out menu,

select 'Format Field'. 


iii. Select 'Customize', then select the 'Date' tab.


iv. From the 'Format' window, select 'Month' to appear as the written 

name, select 'Day' to be any format, and select 'None' for 'Year'.


v. Click 'OK'. 


B. Copy and paste the <Date.DatabaseField> back into the 

report so there are 2 copies of this field.


i. Right-click the <Date.DatabaseField>, from the 

fly-out menu, select 'Format Field'. 


ii. Select 'Customize', then in the 'Format Section' of 

the 'Date' tab, select 'Month' to appear as 'None', select 

'Day' as 'None' and select 'Year' to be any format you want 

it to appear as. 


iii. Click 'OK'. 


C. Select the <Date.DatabaseField> that is formatted with the 

Month and Day appearing and place it in the text object.


2. From the 'Field Explorer', select the 'ExtensionDate' formula and place it somewhere on the report so you can format it.


3. Right-click the formula field, select 'Font', and select the color 'White'. The reason you are formatting the text white is so that you can overlay the 2 fields in the following step.


4. Select the formatted formula field and place it in the text object beside the <Date.DatabaseField>. 


5. Select the <Date.DatabaseField> that is formatted with only the year appearing and place it in the text object next to the ExtensionDate formula field in the text object.


NOTE: =====


If you want any other text to be included in the field, place it in the text object.


==========


6. Copy and paste the text object on to a blank space of the report. You must make a duplicate copy of this text object and paste it in the report. You will now have 2 text objects that are exactly the same. 


7. In the copied text object, double-click on the <Date.DatabaseField> to select it. The <Date.DatabaseField> field will be highlighted in black.


8. Right-click the field highlighted, select 'Change Font', and from the 'Font' tab, select the color 'White'. 


Repeat steps 8 and 9 for both the formula field that is currently formatted white and the Date.DatabaseField>, and select them to be black in color. 


9. Insert this field on top of the other text object adjusting the placement of the visible text to show as superscript.


When previewing the report, the text will appear to display as superscript text.


TIP: =====


To create a subscript font, complete steps 1 through 9 in this knowledge base article. To create the subscript font, you must insert the formula into the report, place it lower than the field. For the sample provided in this knowledge base article, a superscript font was created and placed higher than the field.


==========


===== USING A NUMERIC FIELD =====


The following example is based on using a numeric field in Crystal Reports.


1. From 'Insert, select 'Field Object'. This launches the 'Field Explorer'.


2. Select 'Formula Fields', and click the 'New' icon. 


3. Type a name, such as Extension, and click 'OK'. This will launch the formula editor.


4. Create a formula similar to the following:


//This formula will verify the value and then 

//assign the appropriate superscript extension. 

//This formula will work on all number and date 

//fields.


Whileprintingrecords;


stringvar array MyArray := ["st","nd","rd","th"]; 

numbervar FieldLength := length(totext({Orders.Order ID},0));

stringvar Check1 := right(totext({Orders.Order ID},0),1);

stringvar Check2 := right(totext({Orders.Order ID},0),2);


if FieldLength >= 2 and Check2 = ["11","12","13"] then MyArray[4] else 


if FieldLength = 1 and Check1 = "0" then " " else 


if FieldLength >= 2 and Check1 = "0" then MyArray[4] else


if Check1 = "1" then MyArray[1] else 


if Check1 = "2" then MyArray[2] else


if Check1 = "3" then MyArray[3] else


if Check1 >= "4" and Check1 <= "9" then MyArray[4]


5. Save and close the formula.


Creating and formatting the formula

=======================

Ensure you are in Design view of your report.


1. From 'Insert', select 'Text Object'. Place the text object where you want to display the formula output. 


2. From 'Insert', select 'Field Object'. This launches the 'Field Explorer'. 


3. Select the <Numeric.DatabaseField> and place it in the text object.


4. From the 'Field Explorer', select the 'Extension' formula and place it somewhere on the report so you can format it.


5. Right-click the formula field, select 'font', and select the color 'White'. The reason you are formatting the text white is so that you can overlay the 2 fields in the following step.


6. Select the formatted formula field and place it in the text object beside the <Numeric.DatabaseField>. 


NOTE: =====


If you want any other text to be included in the field, place it in the text object.


==========


7. Copy and paste the text object on to a blank space on the report. You must make a duplicate copy of this text object and paste it in the report. You will now have 2 text objects that are exactly the same. 


8. In the copied text object, double click on the numeric database field to select it. The numeric database field will be highlighted in black. 


9. Right-click the black field highlighted, select 'Change Font', and from the 'font' tab, select the color 'white'. 


Repeat steps 8 and 9 for the formula field that is currently formatted white and select it to have a black color font. If you placed another field in the text object, format the field to also be white. 


10. Insert this on top of the other text object adjusting the placement of the visible text to show as superscript.


When previewing the report, the text will appear to display as superscript text.


TIP: =====


To create a subscript font, complete steps 1 through 9 in this knowledge base article. To create the subscript font, you must insert the formula into the report, place it lower than the field. For the sample provided in this knowledge base article, a superscript font was created and placed higher than the field.


==========


MORE INFORMATION ON ARRAYS


The technical brief, Advanced reporting techniques using arrays, can be

found on our support site by searching for the file name, cr_arrays.pdf at:


http://support.crystaldecisions.com/docs