Archive for November, 2017



Crystal Reports formula function libraries (2017)

Monday 27 November 2017 @ 6:31 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) Generate bar codes without having to install any fonts

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 can now do 16 different bar codes (without requiring special fonts) and can do distance calculations and drive times based on addresses as well as lat/long coordinates.

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




Report hyperlinks that export to a PDF

Wednesday 22 November 2017 @ 5:02 pm

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.




Save money on reportal

Monday 13 November 2017 @ 3:26 pm

reportal Software has just released reportal 6.2, a new version of their web portal for Crystal Reports.  They are offering a 10% discount on all server license fees through November 30, 2017.  I don’t see the server price on their web site but the price was $3,000 per server in January when I did my latest annual review.  That link is also a great place for more information on rePORTAL and the other third party web portals.




Formula that calculates all US federal holidays

Tuesday 7 November 2017 @ 4:33 pm

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.




Jeff-Net

Recrystallize Pro

The Expert Series