A line that grows automatically

Monday 28 August 2006 @ 9:30 am

I had to create an “Income Trend” report this weekend with 12 columns, one for each month of the year. The user inputs the current month and the report prints the columns for January through the selected month. When they enter 8 for August, the report is designed to only show 8 columns, January through August. Since I had to create all 12 columns I gave each column a suppress condition that hides that column when it is a future month.

Then I hit a problem. There were a half dozen horizontal lines going across the entire width of the report. Once I started suppressing later months I noticed how ugly it looked to have a few visible columns but a line that stretched out to the invisible 12th column. I also realized that there isn’t a way in Crystal to have a line grow, shrink or suppress based on a condition. I tried a few solutions before I came up with a relatively simple way of having a line grow and shrink based on the data.

The trick was not to use a line object at all, but to use a formula field with a single top border. The formula was something like this:

ReplicateString ( "XXXXXX", {?MonthNumber} )

I placed it on the page and made it as wide as my line would need to be. The actual width would be controlled by the MonthNumber already being entered by the user. The higher the number the wider the string. The “XXXXXX” string is set (by experiment) to approximate the width of one column.

The formula itself was made invisible by setting the font color of the formula to white. Then I gave it a single top border in black so that only thing visible would be a horizontal line. Last I activated the border property “Tight Horizontal” to make the border shrink and grow with the text. I made several copies of this formula and replaced all of my horizontal lines with them. Now they all grow and shrink together.

If you like tips like this, you should read the list of tips in my Expert Techniques guides.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






2 Responses to 'A line that grows automatically'

  1. dbeleznay - September 18th, 2006 at 7:24 pm

    I’ve seen some really fancy reports that are have been created using the replicate string function. The most impressive was one where they had created their own chart by suppressing the details section and using the formula with a summary field.

    The only problem with using XXX is that it’s imprecise – the width of the X character changes depending on the font, and the X characters are exported to some formats – like Excel.

    A cleaner way to to this is to use the conditional object size and position from XIR2 – which allows you to specify the width of your text or field object in twips (1/1440th of an inch). Unfortunately it still doesn’t work for line objects, so you need to use either a text object, or an empty field object.

  2. hallmerle - September 19th, 2006 at 4:27 pm

    I ran across a similar article on doing the same thing in Excel: http://www.juiceanalytics.com/weblog/?p=236

    Might find some other ideas there on fonts, etc.

    Merle


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server