0

Is it possible to trim the leading zeros out of an Evidence Number when displaying that number on a report?

Yes, the following series of formula can be used to do this:

First, for my example I will not be using any actual data, so I created a formula called "Field to Trim", this formula is as follows:

"0001-002-01"

In a "normal" report, you would use the field you wish to trim the leading zeros from instead of this formula.

Next, I created a formula called "Trim Zeros Start" to parse the sub-sections of the string (separated by hypens "-") and trim the leading zeros off of each portion and re-assemble the string with hypens "-" separating each sub-section:

WHILEPRINTINGRECORDS;
GLOBAL STRINGVAR EvidNum := {@Field to Trim};
GLOBAL STRINGVAR EvidNo := "";

WHILE INSTR(EvidNum, "-") > 0
DO
(
 EvidNo := EvidNo + (IF NUMERICTEXT(LEFT(EvidNum, INSTR(EvidNum, "-")))
                     THEN TOTEXT(TONUMBER(LEFT(EvidNum, (INSTR(EvidNum, "-") - 1))), 0)
                     ELSE LEFT(EvidNum, (INSTR(EvidNum, "-") - 1))) + "-";
 
 EvidNum := REPLACE(EvidNum, LEFT(EvidNum, INSTR(EvidNum, "-")), "");
)

Then, I created a final formula called "Trim Zeros Complete" to trim any leading zeros out of the final portion of the string or to trim leading zeros from the string as a whole if it contains no sub-sections:

WHILEPRINTINGRECORDS;
GLOBAL STRINGVAR EvidNum;
GLOBAL STRINGVAR EvidNo;

EvidNo := EvidNo + (IF NUMERICTEXT(EvidNum)
                    THEN TOTEXT(TONUMBER(EvidNum), 0)
                    ELSE EvidNum);

Finnaly, I added the above set of formula into the report itself, supressing the "Trim Zeros Start" formula. Please note, the "Trim Zeros Start" formula must be added to the report body before the "Trim Zeros Complete" formula in order for them to evaluate in the correct order.

A sample report using these formulas has been uploaded to the Crystal Reports Gallery in the Downloads section.

 

NOTE: This set of formula assume that hyphens "-" are used to separate each sub-section of the string you are seeking to remove the leading zeros from and that no sub-section includes both leading zeros and non-numeric characters (i.e., letters). For example these formula would change "0001a-002-01b" to "0001a-2-01b" rather than "1a-2-1b" and "0001_002-01" to "0001_002-1" rather than "1_2-1".

 

NOTE: If you use these formula and then sort by the resulting string rather than by the original string, the items may sort "incorrectly".

 

 

If your evidence numbers have 2 or more nodes of the same "length" and they are identical, the above formula may not behave properly (for example the above formulas will change "0001-001-01-01-01" into "1-1-1-1" rather than "1-1-1-1-1"), to handle this situation the following formula can be substituted for the "Trim Zeros Start":

WHILEPRINTINGRECORDS;

GLOBAL STRINGVAR EvidNum := {@Field to Trim};

GLOBAL STRINGVAR EvidNo := "";

 

WHILE INSTR(EvidNum, "-") > 0

DO

(

 EvidNo := EvidNo + (IF NUMERICTEXT(LEFT(EvidNum, INSTR(EvidNum, "-")))

                     THEN TOTEXT(TONUMBER(LEFT(EvidNum, (INSTR(EvidNum, "-") - 1))), 0)

                     ELSE LEFT(EvidNum, (INSTR(EvidNum, "-") - 1))) + "-";

 

 EvidNum := MID(EvidNum, (INSTR(EvidNum, "-") + 1));

)

 

 A copy of the crystal report template demonstrating this has can be downloaded below




Trim Leading Zeros.rpt

0 comments

Please sign in to leave a comment.