The Crystal Reports Underground News - Volume 2017.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for November 2017:
** Crystal Reports formula function libraries (2017)
** Microsoft breaks spreadsheet drivers with update
** My library of Crystal Reports materials
** Formula that calculates all US federal holidays
** SQL Function "cheat sheet" for writing commands
** Let me create your Crystal Reports
** Report hyperlinks that export to a PDF
** New Crystal Reports viewer
** Open enrollment Crystal classes in Frederick, MD
** Find it EZ partners with reportal
** Save money on reportal
Gems from the Archives:
Free MICR font turns blank security paper into checks - Nov 2007
Don't rouse sleeping tables - Sept 2007
Crystal Reports formula function libraries (2017)
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 and can do distance calculations and drive times based on addresses as well as coordinates.
If you need help deploying one of these functions in a project let me know.
Microsoft breaks spreadsheet drivers with update
Two customers contacted me in the past few days about the following Microsoft updates.
KB4041676 for Windows 10
KB4041681 for Windows 7/8
These updates broke reports that use the 'classic' Microsoft JET drivers to read 'classic' xls spreadsheets. 'Classic' here is a euphemism for 'ancient but still working', like my workhorse Office XP from 2002.
Today I got a similar error when I tried to help a customer import a spreadsheet(xls) into an MS Access database (mdb). MS Access failed with the following error:
"The wizard is unable to access information in the file…."
So I tried to read the same spreadsheet from Crystal Reports using the DAO connection method, and got this error:
"Unexpected error from external database driver (1)"
I checked and found that KB4041681 had been installed on that workstation.
The official Microsoft temporary solution is for us to use a different "OLEDB provider" in place of JET, but that only works if you can use OLEDB and/or choose your provider.
So I did some research and found a post from 2008 where someone was getting this error message. They asked why they could only read xls files when those files were already open in Excel. If the file wasn't open in Excel he would get the same errors I mentioned above. I figured it couldn't hurt to do a quick test so I opened the file in Excel and then tried to read it from MS Access. It worked for MS Access imports and also worked for Crystal Report connections. Both would work as long as the xls file was open in Excel. Both would fail with the errors above when the file wasn't already open in Excel.
This isn't a great solution, and hopefully Microsoft will fix the issue in the near future. But in the meantime this workaround might just help someone meet a deadline. If you try this, let me know if it works in your environment.
Update 10/19/2017
One of my readers pointed me to the dll that causes this error and how to roll back just that one dll. I now see that this is one of several solutions found in the MSDN discussions. See the links below for more information:
MSDN blog article: Unexpected error from external database driver
MSDN forum thread: ODBC Excel Driver Stopped Working…
My complete library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the LIBRARY page of my site.
Formula that calculates all US federal holidays
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.
SQL Function "cheat sheet" for writing SQL commands
In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.
My solution was to create a "cheat sheet" grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):
Microsoft SQL Server
Oracle
MySQL
Pervasive
Progress
PostGre SQL
MS Access
I have empty columns for Providex and DB2. They are empty because I haven't had any recent projects on those platforms and don't have an environment where I can experiment.
I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated. And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Report hyperlinks that export to a PDF
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.
New Crystal Reports viewer
APB Reports has released a new viewer called CR Dispatch. CR Dispatch takes a different approach to viewing your reports, since it has a minimal user interface that only appears the first time you run a report. After that it provides a secure way to run a specific RPT with one click.
The first time you run the application it asks you to select an RPT file. It then creates a config file that stores the report name and (optionally) the credentials used to run it. The next time you run the application it silently runs that same report with the stored credentials. This means that you need an instance of the EXE file for each report you want to run. Usually the EXE and RPT are stored together in one folder, with a separate folder for each RPT file. This works best for users with a small number of reports to run, or if you are setting up reports for a user and want a very simple setup for that user.
Here is a demo video of CR Dispatch in action.
CR Dispatch costs $20 per user.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my public classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near several major cities and are willing to travel. Call for details.
Find it EZ partners with rePORTAL
Find it EZ (search and documentation) has recently partnered with rePORTAL (scheduling and distribution), which means some new Find it EZ features and some discounts for rePORTAL customers. Find it EZ now directly integrates with the rePORTAL scheduling database. This allows rePORTAL users to extract scheduling information (run times, recipients, destinations, etc.) into user-friendly spreadsheets and also to search and document changes in the underlying database model.
rePORTAL customers can also get a 20% discount on any new license purchase of the latest Find it EZ products, by using the discount code "rePORTAL" at checkout:
Code Search Pro ( Server only )
Dev Surge 365 Enterprise
You can read the full press release here.
Save money on rePORTAL
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.
Gems from the Archives:
Free MICR font turns blank security paper into checks - Nov 2007
Don't rouse sleeping tables - Sept 2007
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2017 by Ken Hamady
All rights reserved - Republishing this material requires written permission