I recently learned several things about passing hyperlinks from a Crystal report to a PDF while working on a customer project. The goal of the project was for every line of the report (each invoice) to have a clickable hyperlink. Clicking that hyperlink would open the corresponding PDF image for that invoice.

The pdf files were all stored in a network folder accessed by a UNC path. The path to the invoice PDF was stored as a character field in the invoice table. Here is an example:

\\FileServer\shared\Images\Invoices\12345.pdf

The report and the hyperlinks all worked fine in Crystal, but the customer wanted to deliver the report as a PDF. We found that once the report was converted to a PDF, the hyperlinks were no longer ‘clickable’.

The first thing I learned was that to get a report hyperlink to survive the transition into a PDF you have to use the Crystal Export function. Using a PDF print driver, like CutePDF or PDF Creator, will not allow the hyperlink to survive the transition. Even if you expose the entire hyperlink so that it is visible in the PDF, it won’t be automatically ‘clickable’.

The second thing I learned was that not all hyperlinks will survive even if you use the Crystal export process. They will survive if they are URL based hyperlinks or MailTo hyperlinks. But any file/path based hyperlinks, like my example above, will still not be clickable in the PDF. As above, even when you make the entire link visible in the PDF it would have to be copied and pasted to work. It won’t be automatically ‘clickable’ within the PDF.

This customer was also planning to use a third party product (Visual Cut by Millet Software) to generate the PDF files at a scheduled time. So I asked Ido Millet about the problem. He was aware of the limitation on file hyperlinks in PDF exports and had built a feature into Visual Cut to overcome it. Visual Cut had a batch file command called PDF_Auto_File_Link that would read through a PDF and convert file hyperlinks into ‘clickable’ links.

So, if you run into a challenge that involves PDF exports with hyperlinks, give me a call. I might just be able to help.

I have several formulas that relate to “business days” or “business hours“. Most allow you to exclude holidays as long as you maintain a list (array) of holidays in a formula in the report. Years ago, one of my readers took the time to write a formula that would automatically load the holidays into an array based on the current year. I shared this on my site.

Recently one of my customers asked for a calculation to skip over all 10 of the observed federal holidays and that is when I realized that the formula mentioned above didn’t include all of the federal holidays. So I have written a special holidays array formula to calculate the federal holidays for any given year. In doing so I streamlined it a bit. You place the following formula in the report header of the report.

numberVar yr := {@Year}; // Current Year for Holidays
dateVar array holidays;
redim holidays [12];
holidays [1] := Date (yr, 1, 1); // New Years Day
holidays [2] := Date (yr, 1, 6) - DayOfWeek (Date (yr, 1, 6) ) + 16;
//Martin Luther King Jr. 3rd Monday in Jan
holidays [3] := Date (yr, 2, 6) - DayOfWeek (Date (yr, 2, 6) ) + 16;
//Presidents Day 3rd Monday in Feb
holidays [4] := Date (yr, 6, 1) - DayOfWeek (Date (yr, 5, 28) + 2);
// Memorial Day last Monday in May
holidays [5] := Date (yr, 7, 4); // Independence Day
holidays [6] := Date (yr, 9, 6) - DayOfWeek (Date (yr, 9, 6) ) + 2;
// Labor Day 1st Monday in Sept)
holidays [7] := Date (yr, 10, 6) - DayOfWeek (Date (yr, 10, 6) ) + 9;
//Columnbus Day 2nd Monday in Oct
holidays [8] := Date (yr, 11, 11); //Veterans Day
holidays [9] := Date (yr, 11, 3) - DayOfWeek (Date (yr, 11, 3) ) + 26;
//Thanksgiving 4th Thursday in Nov
holidays[10] := Date (yr, 12, 25); //Christmas Day
holidays [11] := Date (yr+1, 1, 1); //New Years Day (next year)
holidays [12] := Date (yr-1, 12, 25); //Christmas Day (last year)
Local NumberVar i;
For i := 1 to 12
do (
if DayofWeek (holidays [i]) = 1 then holidays [i] := holidays [i] +1 else
if DayofWeek (holidays [i]) = 7 then holidays [i] := holidays [i] -1 );
holidays[ 4] // Display one of the holidays

Then you can have other formulas refer to the array. The example below takes any date and if it happens to be on a weekend or holiday, moves it forward to the next business day:

WhilePrintingRecords;
DateVar Array Holidays;
DateVar Target:= {Your.Field}; //put your field or calculated date here
WHILE dayofweek (target) in [1,7] or (target in holidays)
Do target := target +1;
target

You can also refer to this array when using my business days calculation, instead of using a hard coded list of holidays.  If you have any questions about deploying this formula in one of your reports, let me know and we can schedule a session.