Skip to Content
Technical Articles
Author's profile photo Sumesh Sivanandan Pai

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michelle Crapo
      Michelle Crapo

      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

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy

      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