I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.

Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0].  This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.

It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.

Every version of Crystal Reports in the past 20 years comes with a neat little label wizard that allows you to automatically set up about 30 common Avery label sizes. But there are a few things you need to know to avoid problems.

Tip #1) A report created using the label wizard can’t be changed to a regular report, and a regular report can’t have one of the label sizes automatically applied. You have to decide to use the label wizard when you first create the report. The wizard is found under the menu options “File > New > Mailing Label Wizard”.

Tip #2) The wizard lets you pick the fields you want, but it will put each field you select on its own line. So, if you create an address label and add the fields City-State-Zip, the wizard will put those fields on 3 separate lines. I add only the first field of each line while I am in the wizard. Then when I preview the report I can add the other fields, or write a formula to combine all of the fields that you need.

Tip #3) This label wizard always puts the label fields on subsections of the details band. If you want to print one label per group, don’t try to move the fields to group headers or you will make lots of unnecessary work. Instead you can make the detail labels appear as if they are at the group level by only printing the first detail label of each group.

To do this you:

• Add the group to the report
• Suppress both the Group Header and the Group Footer.
• Create a running total field that counts the records in each group(reset ‘on change of group’).
• Suppress the Detail sections or the Detail parent section with a condition that says:

{#running total} > 1

Only the first record (label) of each group will print. This allows you to use the wizard to get the layout automatically, but still print one record per group.

Last month I wrote about a difficult time I had installing Crystal Reports. One of my complaints was that if the install fails, you have to start over – including waiting 5-7 minutes while the setup ‘unpacks’ all the files. It seems like there should be a way to keep the files unpacked when an install has to be repeated.

Well SAP doesn’t provide an official way to do that but one of my readers (who apparently does lots of CR installs) shared his method. Here are his steps:

1. Run the install .exe.

2. Wait until everything has been unpacked. If you aren’t sure, wait until it asks for the license key. Then go to this folder, substituting your user name in the path:

C:\users\<username>\AppData\Local\Temp

Look for a folder in Temp that starts with “RARSFX”.

NOTE: AppData is a hidden folder, so you’ll have to set Explorer to view hidden items.

3. If there’s more than one “RARSFX” folder, delete all of them except the most recent one. These are failed installs that didn’t get deleted correctly.

4. Copy the remaining folder somewhere else. You cannot cut and paste because the installer has those files open. You might want to give the folder copy a more meaningful name.

5. For subsequent installs, go to this new folder and run setup.exe.

So if you are troubleshooting a balky install, or if you have to install CR on several different workstations, the steps above will speed up the iterations. And, thanks to Dell Stinnett-Christy, a senior manager at Protiviti, for sharing this method.

One of my former students sent me a question. He wanted to know the easiest way to add the dashes back into a Social Security number when it is stored as a 9 digit string. I was going to have him parse it into 3 pieces using substring brackets and then assemble those around the dashes. Then I remembered the picture() function. I don’t use it much, but it has been on my potential blog topic list for a while.

The picture() function allows you to insert characters and punctuation into a string so that it has a specific format. The two most common uses I have seen are adding the parentheses and dash back to the US Phone number: “(000)000-0000” or adding the dashes back to a SSN: “000-00-0000”. It will work with any consistent pattern.

To use the picture function you give it two arguments. The first is the field or string you want to format and the second is a pattern string. The pattern string has an ‘x’ for every character in the field. You then insert the other characters among the ‘x’s at the appropriate places. Crystal will insert those characters automatically at those positiions.

The picture function for the Phone Number would look like this:

Picture({table.phone}, '(xxx)xxx-xxxx')

The picture function for the SSN would look like this:

Picture( {table.SocSec} , 'xxx-xx-xxxx')

Note, that if the field is empty or short the formula will still show all of the inserted characters.