Technical Articles
The Power of Crystal Reports: Data Manipulation on the Fly
Report Requirement:
Recently client came with the Report requirement of adding a “.” between each digit of a Number value in Database Field. For example, if Database Field Value is: 12345 then client want to show in the report as: 1.2.3.4.5
Interestingly the length of the number can change, sometimes it will be 2 digits and sometimes it will be 6 digits etc.
Solution:
Crystal Report is so much powerful that it can be done by simply creating a Variable with below logic:
Note: In the below logic DATA_PROC.SRL_NBR is a field of a Procedure. You can do the same thing for Field of a Table.
stringVar x := “”;
numberVar y := Length(ToText(Trim({DATA_PROC.SRL_NBR})));
numberVar start_pos := 1;
x := Mid (ToText(Trim({DATA_PROC.SRL_NBR})), start_pos,1 );
if (y > 1) then
(
do
(
start_pos := start_pos + 1;
x := x + “.” + Mid (ToText(Trim({DATA_PROC.SRL_NBR})), start_pos,1 );
) while (start_pos < (y-1));
);
x
I like this one.... It's been so long since I've used Crystal reports. I tend to forget about that tool.. I do use query. But for a nicely designed report that is easy to change quickly - I think it is a Crystal Reports is an excellent tool.
At this job I don't think I have it available. I'll have to look a little harder later. And I like the tip. I also love the comments. It makes for a nice discussion.
Nice - It triggered a memory for me!
Michelle
You could also simplify a bit by doing this:
stringVar x := “”;
numberVar y := Length(ToText({DATA_PROC.SRL_NBR}, 0, ""));
numberVar start_pos := 2;
x := Left({DATA_PROC.SRL_NBR}, 1 );
do
(
x := x + “.” + Mid (ToText(Trim({DATA_PROC.SRL_NBR})), start_pos,1 );
start_pos := start_pos + 1;
) while (start_pos < (y-1));
);
x
You used ToText() without any formatting information - it will automatically use the number format defined on the computer where it's run, so you risk having commas and decimal places added if you use ToText() without formatting - unless the field is a string instead of a number, in which case you don't need ToText().
-Dell