Archive for November, 2016

Crystal Reports formula function libraries (2016)

Saturday 26 November 2016 @ 11:52 pm

It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is CUT Light, which now includes a handful of new functions:

  • Convert time strings (hh:mm) into seconds
  • Resize images
  • Convert HTML or a URL into an image
  • Set/Get/Reset lookup values in memory arrays

If you need help deploying one of these functions in a project let me know.

Date vs DateSerial

Sunday 20 November 2016 @ 6:20 pm

There are many functions in Crystal with overlapping capabilities, like Val() and ToNumber(). I have always thought of Date and DateSerial as identical functions that both allow you to specify a literal date in a formula by specifying the year, month and day as numbers like this:

Date (2016,11,24)
DateSerial (2016,11,24)

The three numbers can either be literal values, fields or calculations like this:

Date ( {table.Year}, {table.Month} + 2 , 3 )

This will create a date value by taking the Year value from the table, adding two months to the Month value from the table and then taking the 3rd day of that month.

But I just discovered (via a post by AndyMc in an important difference between the two functions, which is how each function would respond if the value in the field {table.month} was 12.  The Date() function would generate an error because 12 + 2 = 14, which is an invalid month.  The Date() function requires that the month number be from 1 to 12. Also, the day number has to be between 1 and 31 and has to be a number valid within that month.

But the DateSerial() function doesn’t have this limitation. If the formula above used DateSerial(), adding 2 months to month 12 would automatically move forward to next February.  You can also give DateSerial() a day number that is more than 31 and it will move automatically to the next month.

So the The following expression would return 5/1/2016:

DateSerial (2016, 4, 31)

But using Date() would generate an error:

Date (2016, 4, 31)


Using Crystal’s pre-programmed date ranges

Wednesday 9 November 2016 @ 10:57 am

One of my customers learned a new trick from me today. They wanted a report to automatically select the data for the prior month. They didn’t realized that Crystal has a dedicated function for that and you can use it in both the select expert or in the selection formula. So I figured I would share this, since there may be others that have never seen this function in Crystal.

Go into your select expert and add a rule using any field that is a Date or DateTime. Then in the comparison list look toward the bottom for the option “is in the period”. When you select this option the next drop-down list will automatically populate with 25 pre-programmed functions. You will find items like “LastFullWeek”, “LastFullMonth” and “YearToDate”. These periods are automatically calculated each time you run the report, based on today’s date.
So say you want a report to automatically select the records for the prior month without having to enter the date range or change the report each time. You use the LastFullMonth option in the select expert, or you could do the equivalent, like this, in the selection formula:

{Date.Field} in LastFullMonth

One note, Crystal calculates all of these periods based on the print date which is normally the system date of your computer. But Crystal also allows you to override the system date for any report. Just go to [Report > Set Print Date] and you can pick another date. Just don’t forget to reset the date before you save the report or Crystal will continue to use that same date (for this report) until it is reset.

Recrystallize Pro