The Crystal Reports® Underground

Jeff-Net has released a free Edition of Report Runner viewer.   It is completely free, can be installed as many times as you like and doesn’t have any ads.  It is only missing 2 features that are included in their paid edition:

1) You can’t launch reports from the command line (batch files).
2) You can’t customize the interface

But otherwise it can do everything a report viewer should do (preview, print, export, refresh, etc).  To get your license you have to provide a company based Email address (not Hotmail/Gmail/etc.).  That’s it.

If you want to compare the features of Report Runner with the other free (and paid) viewers you can read my review of Crystal Reports Viewers, which I update every September.

When you specify field values in Crystal Reports formulas do you have to match the case?  Well, it depends on where you are. Take this example:

{Name} = “Joe”

It would only be true when the first letter was upper case and the other two were lower case.    So what if you have values in your data that use different case patterns?  You could get around the problem
Continue Reading »
Case sensitivity in Crystal Reports

It is always a good sign when vendors respond quickly to user requests.  Earlier this month the developers of Find it EZ received a request from a potential customer.  He needed to search for table and field names within his SQL commands and SQL expressions.  The next day this user was using a new version of Find it EZ, including this new feature.  The user was surprised that he didn’t even need to fill in the report parameters before the SQL was searched – something required by his old software.

This support for embedded SQL searches is now included in the current release of Find it EZ.  It also added a search of Crystal Dashboards (Xcelsius) files and even the embedded Excel models.   See this article for more info on Find it EZ and special pricing through the end of this month.

It has been one year since I did my first comparison of formula function libraries, so it is time for an update.  If you aren’t familiar with the term, User Function Libraries (or UFLs) 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 four primary sources are:

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

Last night I had a customer with a strange problem.  Their report ran fine and looked great in preview and when printed.  But if it was exported to PDF it had a long line down the page.  This didn’t happen if it was printed to a PDF driver so the only time it was visible was when using the CR export function.

I opened the PDF and there was a vertical line on every page, even on blank pages, starting near the top.   I opened the RPT and there was no visible object anywhere near the sections at the top where the object should be sitting.  I was getting ready to start deleting objects one at a time to see if it was tied to a specific object, but first I decided to
Continue Reading »
Mystery line in PDFs

Yesterday I was asked if there was a simple way to group records into Spring, Summer, Fall and Winter.  There are several ways but I think this is the simplest.  I convert the month and day into a number by multiplying the month by 100 and then adding the day number.  This gives every day of the year a sortable value from 101 to 1231. Then you can use that value in a formula like this:

Local NumberVar x := Month({Orders.Order Date}) *100 + Day ({Orders.Order Date});
If x < 320 then "Winter" else
If x < 621 then "Spring" else
if x < 923 then "Summer" else
if x < 1221 then "Fall" else "Winter"

If you were considering trying out Find it EZ this might be a good month to do it. For the month of November, Find it EZ is offering my readers a 30% discount on their scanning tool. It is designed to find all uses of a particular table or field in any layer of your project. So it goes beyond scanning your RPT files and also scans your application source code, your SQL queries, documentation, config files and just about anything else. It even includes plugins that integrate automatically into Visual Studio and Eclipse.

When you scan your project for a proposed change Find it EZ will generate an ” Impact Checklist” that
Continue Reading »
Find it EZ offers a 30 % discount

The folks at ReCrystallize have recently released version 14 of ReCrystallize Pro.  For those of you who are not familiar with ReCrystallize, it is a low cost way to put your reports on the web.  Walk through a wizard and ReCrystallize will generate ASP or ASP.NET web pages that are designed to run your reports.  You can read more about it in my comparison of server based deployment options.  With version 14 there are two significant changes.

1) It now supports the newest version of Crystal Reports (2011). This means you can do dynamic and cascading parameters and you can change parameter values without changing web pages or in some cases without even refreshing the report.

2) It now supports ASP.Net which means it works better with the current browsers and current versions of Windows and IIS.  It also means that the user’s browser doesn’t need to download anything.

Note that they still support classic ASP and most older versions of CR.  See their web site for more details.

Last month I wrote about a new feature of CUT Light that allows your report to calculate the distance between two points based on zip codes or lat./log. coordinates. The one challenge I found was that the conversion of zip codes to long./lat. relied on a web query and therefore had a daily quota limit. But since then Millet Software has added a local conversion table which allows you to bypass the web query.  This makes it much faster and removes the limit.  The included data is as of the 2010 census and is limited to 5 digit zip codes, but for most applications it is probably still a better option than the web query.

You can read this page for a more complete list of what CUT Light can do.

I had two different customers in the past week ask me why their distinct count grand totals were not totaling correctly. They had distinct count subtotals at the group level and distinct count grand totals at the report level.  But when they added up all the subtotals the sum didn’t match the grand total.  Usually it won’t because of what a distinct count is designed to do.

A distinct count summary will count how many different values there are in a column.   So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level.   And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer.  But it is very likely that this grand total is not going to match the sum of the doctor subtotals.  This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.

If you ever have a requirement where you need the grand total to be the sum of the subtotals you can purchase Expert Techniques volume 1 and read tip 25.