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.