Archive for November, 2012

Crystal Reports formula function libraries (annual update)

Monday 26 November 2012 @ 4:00 am

It is time for my annual comparison of formula function libraries, and this year I have added a new one, which is actually a very old one.  It has been hiding at the bottom of my LINKS page for a decade and you can read about it here.

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, or from one report to another.
2) Append lines to an external text file.
3) Automatically copy a value to the clipboard.
4) Check the user name of the user running the report.
5) See if a file or folder exists – either on your network or on the internet (http).
6) Rename/copy/delete a file on your hard drive or network drive.
7) Launch an application or run a batch file.
8) Execute a SQL statement (Select/Insert/Delete).
9) Send an Email using information in the report.
10) Create a table of contents or an index for your report.
11) 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)

If you need help deploying one of these functions in a project let me know.  I am sure I will be able to help.

Rediscovering a long forgotten UFL

Sunday 25 November 2012 @ 4:12 pm

I have been writing about UFLs now for several years.  So I was a bit chagrined this week when I realized that I have been hosting a UFL on my site since 2002 and never thought to include it in the review.  This UFL was written by Paul Birch who was with a group in the UK called Maginus Software Solutions.  He gave me a free copy and also allowed me to host a copy for others to use on my site.  It has been on my LINKS page ever since.  I just tested it and it works just fine in versions 8.5, 10 and 12 so I am guessing it will work in other versions as well.

The main reason I found it useful is that it has a function that will strip out any punctuation from a string.  I think it does this by only allowing English characters, numbers and spaces.  If you deal with international character sets Continue Reading »
Rediscovering a long forgotten UFL

The third way to split a section

Friday 23 November 2012 @ 11:34 pm

Most users know that you can split a section in Crystal to get subsections (e.g. Report Header a, Report Header b, etc.).  The standard ways to create a subsection are to:

A) Go into the section expert and hit the INSERT button at the top of the window.

B) Right-click on the name of a section and select “Insert Section Below”.

Not everyone knows the third way, which is the one that I find the most useful.  It is especially useful when you need to put some additional space in the middle of a large section.

Say you are working on a full page form and Continue Reading »
The third way to split a section

Crystal Reports and QuickBooks

Tuesday 13 November 2012 @ 10:19 am

I recently got a chance to work on a project using Crystal Reports to read QuickBooks data. To read QB data from CR you have to use a special ODBC driver called QODBC put out by FLEXquarters. This an unusual driver in that when it queries the QB tables it displays a small counter panel for each table, showing the records returned from that table. If your report includes 6 tables and you will see a stack of 6 small windows pop open and close as QODBC assembles your data set.  There is a switch to hide them if you choose.

We found another quirk when tried to deploy the reports to users with a client side viewer program. The report worked fine in CR but would not return any data through the viewer. It took a while to find the problem.  Before QODBC can access QB data you have to run the first query while you are logged into QB as an administrator.  The instructions made this sound like a one time process, but we found that Continue Reading »
Crystal Reports and QuickBooks

A new way to Tweet report data

Tuesday 6 November 2012 @ 11:10 pm

There are desktop scheduling tools and server-based scheduling tools that can post messages directly to twitter.  These are all in the $1000 and up price range. Now there is a third option to consider.

Ido Millet of Millet software recently wrote up a case study in the user manual of Visual Cut (page 41).  It shows how the Santa Monica Fire Department uses Visual CUT to tweet calls for service as they entered into their database. A report is scheduled Continue Reading »
A new way to Tweet report data

Recrystallize Pro