Suppressing blank lines in Crystal Reports


Here is a Crystal Reports tip from the GP Reports Viewer newsletters that can help when you are working with addresses that are stored in multiple fields. Dynamics GP (and probably many other systems) stores address components in separate fields, so if we look at a customer address window there are many individual fields that make up an address:

customer address window

It is entirely possible that many of these fields will be blank, but we need to account for them on reports like customer invoices and statements just in case there are records where these fields are not blank.

If you simply add all the address fields individually to a report, here is what the report design will look like:

individual fields

And the results would be:

individual fields results

To get the address to appear without any blank fields, instead of adding each field individually to your report, use a  text object:

  1. Go to Insert > Text Object.
  2. Place the Text Object in the area where you will want the address and make it at least a few lines tall, you can always resize the text object as needed later.
  3. Drag each address field inside the text object in the order they should appear. Insert carriage returns and additional spacing or punctuation where needed between the fields:text object
  4. Right click on the text object and choose Format Text. On the Common tab check Suppress Embedded Field Blank Lines. On the same tab, Can Grow is also useful – this lets you make the text object the size of your typical address and if the address is larger, Crystal will automatically adjust the size of the text object and the rest of the report: text object format editor
  5. Click OK and take a look at your results, they should now look a lot nicer: text object results

For more Crystal Reports tips, check out my Crystal Reports Tips page. Past GP Reports Viewer newsletters can be found on the Flexible Solutions website.

14 Responses to “Suppressing blank lines in Crystal Reports”

  1. Thanks, this was a great help.

    Like

  2. I searched all over to find this solution. Thank you so much for posting.

    Like

  3. New to Crystal and had inserted a formula to do this (also found online) but with poor results. This was sooo much easier – and it worked SO much better! Thanks.

    Like

  4. Thanks a lot..

    Like

  5. This was a great write-up! I am just getting started in report writing and this tip saved me lots of time.

    Like

  6. Thank a ton! Very useful information

    Like

  7. i Love this thank u very much i am search for this almost one week thank you…………………..

    Like

  8. Thanks Victoria – I found this within seconds on Google and it saved me hours! BTW – your GP2010 implementation guide is fantastic as well! I can recommend it as required reading for any GP consultant.
    Best Regards
    Phillip

    Like

  9. Brilliant solution, I have tried and tried to make the section expert do this without success.

    Many thanks

    Like

  10. If the “City” field is blank, I assume the comma will still show up? Other than a case statement and concatenation in the select statement that generates the data, is there any way around this?

    Like

    • Moonica,

      Yes, if there is no City, the comma will still show up, as will the spaces between the fields. If you have data where you suspect the City will be blank, instead of hard-coding punctuation or spaces, you could create a formula like this:

      if {TABLE.CITY} = “” then “” else “, ”

      Then insert that formula into the text object instead of the actual comma and space.

      -Victoria

      Like

  11. Great Tip Victoria, Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: