Growing every field in a row to be as large (tall) as the tallest field.

[Former Member]

I was recently surprised to discover that Crystal Reports 2011 didn't have a function to allow you to grow all fields in a row to be the same size (height).  Depending on why you need the fields to match height, the solution goes from simple to incredibly hard/over engineered.  While the situation I was in required the incredibly hard fix (naturally), I thought it best to provide both solutions in this blog post.

Scenario:

I have five data fields that I need to pull into a report from an SQL query:

SQL.ItemCode               -- String, 18 characters.

SQL.ItemDescription      -- String, 32 characters

SQL.ItemQty                 -- Integer, but report only needs to pull whole number value (database doesn't use fractional quantities)

SQL.ItemNotes             -- text field, can be null or it could be several paragraphs of information.

SQL.ItemOrderNotes     -- Text field, typically less than a paragraph of information

Problem 1:

Users plan to run this report bi-weekly to review inventory information.  On the printed report users want borders around each row/column, all row borders should line up.

Problem 2:

On a monthly basis, they need to export this report to MS Excel(tm) to send to a sales representative who will be at buying events.  The rep will adjust item notes and order notes, and then send the file back to HQ for it to be updated in the system.  Users want the exported report to look similar to the printed report.  They do NOT want to have to format the exported file (thus data only exports are out).  All rows and borders must align properly as they do for the printed report.

Problem 1 Solution:

If only dealing with a print out from crystal, the solution is extremely easy.  Add sections above and below the detail line that contains your data.  Draw vertical lines (or use boxes) to section off each column, making sure the lines span into the added sections.  This way the lines will elongate to always be as long as the displayed data.  Draw horizontal lines in each of the added sections to finish off the box.  My personal preference is to use lines rather than boxes because it gives you more detailed control over the shape and size of each divider.

Unfortunately this solution doesn't help if you're exporting the report since lines and boxes do not carry into Excel.  Also, regardless of how it looks printed, the actual data fields are not the same size, so when they export, your cell size doesn't line up.

Problem 2 Solution:

After two days of searching and testing, I came up with a solution to get things to line up correctly in excel, however, there are some BIG restrictions on doing what I'm about to suggest.   Here's a list of limitations that I came up while developing this solution.

1.  You must use a font that has the same spacing for all characters (monospaced font).  This limits you to fonts like Courier or Lucida Console

1a.  You will need to know how many characters per line in fields on the report.  I did this by having it output a count of characters (12345678901234567890..etc) until it hit a second line then did the math to figure out how many for the first line.

2.  I increase the size of fields using carriage returns;  char(13) so if you're planning to take these excel exports and import them into a different system, you could be dealing with a lot of garbage if the import script isn't setup to specifically deal with that.

3.  To get things to line up right, I had to remove char(13) and char(10) from my Notes fields.  I didn't need them for my purpose, but if you need them, I would recommend creating a formula (or modify the one I give) to count those characters as additional lines.

Step 1 - Line Count
Special Thanks to jamie.wiseman who posted this formula back in 2013 Counting the Number of Lines in a Text Field
Create a formula to count the lines for each of your fields.   In my scenario, I named them: FormatControl_LineCount_ItemCode FormatControl_LineCount_ItemDescription FormatControl_LineCount_ItemQty FormatControl_LineCount_ItemNotes FormatControl_LineCount_ItemOrderNotes Below is the formula for Item Notes to use as an example.
stringvar t:= {SQL.ItemNotes}; numbervar ll:= 36; // set this to the desired width of the output, or line length, in characters > a fixed width font is recommended // the code below does not need to be modified stringvar o:= ''; // the formula output stringvar en:= ' @@%%^^ '; // identifier used to replace "Enter" values in var t t:= replace(t,chr(13)+chr(10), en) + ' '; numbervar lt:= length(t); numbervar i:= 1; // where the next word starts numbervar nt; // where the next token or space starts numbervar ntrt:= 0; // token running total numbervar lc:= 1; // line counter numbervar il:= 0; // character counter which is reset after each line while  i < lt do ( nt:= instr(t[i to lt], ' '); ntrt:= ntrt + nt; stringvar nw:= t[i to ntrt]; // next whole word numbervar lnw:= length(nw); // length of next whole word if trim(nw) <> trim(en) then     (     if ll - il + 1 >= lnw then  o:= o + nw else     if ll - il + 1 < lnw then (il:= 0; lc:= lc + 1; o:= o + chr(10) + nw;)     ) else     (il:= 0; lc:= lc + 1; o:= o + chr(10)) ; if trim(nw) <> trim(en) then il:= il + lnw; i:= i + nt; ); //o; // use this line (and comment out next line) to display the formula output which mimics your text lc; // use the line to display the number of lines output by the formula above

With these line counts in place and having adjusted the numbervar variable to be the number of monospaced characters per line for each field, I'm ready to move on to figure out which field has the largest number of lines (i.e. is the tallest).

Step 2 - Comparison
In my scenario, I know that my longest fields would be either ItemNotes or ItemOrderNotes.  I called this formula FormatControl_CompareLongest
If {FormatControl_LineCount_ItemNotes} > {FormatControl_LineCount_ItemOrderNotes} Then {FormatControl_LineCount_ItemNotes} Else {FormatControl_LineCount_ItemOrderNotes}

Step 3 - The Tallest field
Now I create the control formula that all the fields will use to determine how many carriage returns to use.  Depending on the type of font used, length of the words used in the text, and a few other factors, the line count formula has a margin of error. For my data, I found that for every 9 lines counted, I needed to add an extra line, but roughly every 30 lines, I could reduce the number of carriage returns by one. So, the formula you see below is taking the line count from the longest field, and adding some carriage returns to account for the margin of error. FormatControl_LineCount_Longest
ROUNDUP({@FormatControl_LongestField} + ({@FormatControl_LongestField}/9)) - Round ({@FormatControl_LongestField}/30)

Step 4 - Apply to report data fields
Now for the actual fields that are going to display on the crystal report and export to Excel! This will output the data of the field, then compare the line count of the field to line count for longest.  If there's a difference, add in that number of carriage returns to make the rows equal in height.
{sql.FieldName} & ' ' & IF ({@FormatControl_LineCount_Longest} - {@FormatControl_LineCount_FieldName}) > 0 THEN ReplicateString(chr(13), ({@FormatControl_LineCount_Longest} - {@FormatControl_LineCount_Fieldname}))
**Thank you to abhilash.kumar for providing me with the correct syntax to do this step. Inserting carriage returns into formula field plus database output

and with that all the fields should be at the same height.  If you're seeing some differences, check to make sure the font size is the same for all fields.   If you need to have borders around all these fields (especially when in excel) you can right click on each field, go to format object > Border Tab and select a single line border for all sides.   THOSE borders will export to excel.

That's it.  If anyone has some optimization suggestions for this setup (I'm sure there's plenty since I came up with this and implemented it in two days), feel free to post in the comments section and I'll be sure to point it out below this line with some edit updates.