Mark and Terry Cunningham were part of the original team that created Crystal Reports, back in 1984. They were with Crystal Services which was part of the Cunningham Investment Group. Now they are back as part of Indicee, Inc. and they are going take on a new BI challenge. They are creating a new BI tool that is supposed to be simpler and less expensive than the enterprise products that dominate the market today. The management team is mostly former employees of Crystal Services, Crystal Decisions and Business Objects who weren’t excited about the purchase by Business Objects. Indicee, Inc. is even located in the same original offices where Crystal Services started back in 1984. Indicee has since been purchase by Dunn and Bradstreet.

Here is a little secret that not everyone knows. The Crystal Reports formula editor has an autocomplete function that allows you to type the beginning of a function or other reserved word, and let Crystal give you choices to pick from. To invoke the autocomplete you type CTRL – Spacebar and a window will open with all of the reserved words in it. As you type the list will narrow down to the functions that start with the characters that you have typed.

So say you want to use the function InRepeatedGroupHeader. You do your CTRL – Spacebar and type “IN” and you get a choice of 10 options. But if you add the “R” there is only one available choice so that choice is entered into your formula for you.

Or if you start typing a function you can hit the CTRL – Spacebar and the same window will open showing what matches what you have typed so far.

This is available in versions 10 and later.

I maintain a list of commonly used formulas on my site and some of these formulas go back nearly 10 years. I have started to notice that some of the older formulas can be done more simply using features in the newer versions of Crystal. So I will occasionally update a formula to simplify it.

For example, one of the first formulas I wrote converts a character date into a true date. The original worked for text stored in formats such as M/D/YY or MM/DD/YYYY. The challenge back then was determining if the month or day was 1 or 2 digits and the method I used was to look for the positions of the slashes. This job is made easier now because we can use the SPLIT () function. So I have finally updated the formula. I also added formulas that convert values in “YYYYMMDDHHMMSS” or “YYYYMMDD” formats.

I am surprised at how many of these get forwarded to me. They are easy to spot because they usually:
1) Arrive with a half page of CC Email addresses at top.
2) Urge you to forward the message to all of your contacts.
3) Describes some emergency or noble cause that needs everyone’s immediate attention.

I can assure you that ANY message that asks to be forwarded to everyone on your contact list is a hoax. (So is every request for UPC codes, pull tabs, post cards, etc – but that is another story.) So if you are ever tempted to click “forward”, first copy the key phrase from the message and Google it along with the word “hoax”. There are lists of hoaxes out there just for this purpose.

If you group on a date field in Crystal Reports, the groups are always based on calendar periods. So a group by year will be by calendar year and a group by week will be by calendar week (Sunday to Saturday). If you want to group by a non-calendar period, like a fiscal year, then you have to write formulas. I have BLOG entries that show you how to group by fiscal year and fiscal quarter.

One of my former students asked me today how to group dates into weeks that aren’t calendar weeks, like their pay week that starts on a Monday and ends on a Sunday. Continue Reading »
Grouping by non-calendar week