This Crystal Reports tip comes from the January edition of our GP Reports Viewer Newsletter. There are often times when a report is printed so that someone can go down the list and verify items or see where information may be missing. While it was a little more timely in January, at the time of the newsletter, confirming 1099 information is a good example of this.

To start, let’s say I have a report with a list of all my 1099 vendors:

What I am checking on this report is whether I have a Tax ID number and an address for each vendor, so I am going to create two formulas these:

taxid:
if {PM00200.TXIDNMBR} = ""
then Chr(168) else Chr(254)

and address:
if {PM00200.ADDRESS1} = ""
or {PM00200.CITY} = ""
or {PM00200.STATE} = ""
or {PM00200.ZIPCODE} = ""
then Chr(168) else Chr(254)

The important parts are in blue in the formulas above, these bring in the Wingdings character codes for a checked box if the condition specified is true and an empty checkbox if it is not true. You can use any characters you want for these, as long as you know what their code is. (More on this a little further down.)

Next step is to add two new columns to the report to show the taxid and address formulas, change the font for them to Wingdings and the font size to something larger (otherwise they will show up tiny). Here is the final result:

Note that vendor DOLECKIC0001 has an empty checkbox for the address, even though there is an address listed. That’s because in my formula I am checking for the ADDRESS1, CITY, STATE and ZIPCODE to all be there. If one of them is missing, I consider that an incomplete address. This is where you really see the power of the checkboxes, when quickly scanning the list it might look like all the addresses are there, however this way no list scanning or guesswork is needed.

To find codes for other characters you might want to use, I have found the following websites:

• Cloford.com Symbol Fonts
• Alan Wood’s Character sets

For more tips like this, sign up to receive GP Reports Viewer newsletters via e-mail or check out the newsletter archive on the Flexible Solutions website.

The Crystal Reports tip from Flexible Solutions GP Reports November newsletter shows how to selectively highlight values in a list without having to write a formula. 

Let’s say you have a customer list with balances:

Now what if you wanted to make all customers with balances of $50,000 or higher more visible?  Here are the steps to do this using the Highlighting Expert in Crystal Reports (click on any of the screen shots to enlarge them):

• Right click on the field you want to change the display for and choose Highlighting Expert (or click on the field and go to Format > Highlighting Expert).
• Click New at the bottom of the Item list.
• Under Item editor select the conditions to check for and the color, font, background or border you want to display. Below is an example to simply highlight any amount $50,000 or greater in yellow:
  Or to make the amounts show in red, bold and italics, it would look like this: 
  
  Note that I set the Background to Default Color, so that my alternate line shading is preserved. (Check out my Alternating shading for lines in Crystal Reports to see how to set up alternate shading.)
• Click OK to close the Highlighting Expert window and your report will now look like this: 

If you want to highlight the entire line instead of just the amount, you can repeat the steps above for each field on your line, the only change would be under the Item editor section, where you would be testing the condition for your balance field, here is what that looks like:



And here is the final result, after adding the highlighting to each detail field:



For more tips like this, sign up to receive GP Reports newsletters via e-mail. Or check out the newsletter archives on the Flexible Solutions website.

Here is a tip from the Flexible Solutions GP Reports October newsletter on how to create a running total in Crystal Reports. 

Running totals are a great way to show totals that have a different calculation than a subtotal without having to write additional formulas. 

Let’s look at an example: You have a list of unapplied vendor transactions grouped and subtotaled by vendor class. Below is an example of what this report looks like right now:

For each vendor class you already have a total due, but you also want to show the total credit limit so that you can compare the totals for each class. The credit limit is stored at the vendor level and because your report has more than one transaction per vendor, you cannot simply subtotal the credit limits for all the detail lines. Here are the steps to create a running total for the credit limit by vendor class:

1. Add the Credit Limit field somewhere in your detail section
2. Right click on the Credit Limit field and choose Insert > Running Total:
3. Choose a name for the Running Total
4. Change the Evaluate setting to ‘On change of field’ and enter the vendor ID field (in this case PM00200.VENDORID)
5. Change the Reset setting to ‘On change of group’ and choose the vendor class group (Group #1: PM00200.VNDCLSID in this case):
6. Click OK to close the Create Running Total Field window
7. Go to Report > Record Sort Expert  and make sure that your details are sorted by the vendor class group, then by Vendor ID:
8. Click OK to close the Record Sort Expert window
9. Optionally, hide the Credit Limit field on the detail section by right clicking on it, choosing Format Field and checking Suppress on the Common tab:

Your report now has the credit limit totals per vendor class:

For more tips like this, sign up to receive GP Reports newsletters via e-mail. Or check out the newsletter archives on the Flexible Solutions website.