Crystal has a native driver that lets you read data stored in older Excel spreadsheets (XLS). But if you want to read data stored in the current spread format (XLSX) then you will need to get an OLEDB/ODBC driver for Excel. I have had to do this recently for two customers and it took me a while to find the correct driver. So to make it easier for the next time I am going to put the link here.

Part of the problem is that the Excel driver is part of a larger set of drivers included in the Microsoft Access Database Engine 2016 Redistributable (or earlier versions for 2013 or 2010). This includes both ODBC and OLEDB drivers for Access (MDB/ACCDB) and Excel (XLS/XLSX). When you start the download there will be two options:

AccessDatabaseEngine.exe (32 bit)
AccessDatabaseEngine_x64.exe (64bit).

Even though you most likely have a 64-bit PC you might not want the 64-bit driver.  If you have Crystal Reports 2020 you want the 64-bit version, while all prior versions of Crystal Reports need the 32-bit   version.

Once you have installed the correct driver you can read my previous blog post on how to launch the corresponding ODBC Administrator, or you can skip that and connect using OLEDB. The ODBC configuration is much more confusing than it should be.

And thanks to Adam Butt of APB Reports in Norway for showing me how much simpler OLEDB is over ODBC.

I do this chart each year based on my newsletter subscribers.  It shows the version of Crystal Reports that they were using when they sign up for the newsletter. Over time, it shows which versions have staying power and how quickly new versions take hold. I have included numbers for 2016, even though the year is only about half way there. Those numbers will change in next year’s chart when 2016 is complete.

Note that almost 1/4 of users are still using Crystal Reports XI, which is over 10 years old. That is roughly the same as the user base for the next two versions combined.  That may be partly because all versions after XI are limited to the .NET runtime engine.

After 8 years I have finally purchased a new version of Crystal Reports. I skipped the last two upgrades because there wasn’t enough to justify the cost of the upgrade. But there are several new features in CR 2016. Here is what’s new:

Formatting conditions for lines and boxes:
Six new formula condition buttons to control formatting properties of lines and boxes based on data or parameter conditions:

Suppression
Style
Width
Border color
Fill Color (Boxes Only)
Drop Shadow (Boxes Only)

Vertical Alignment Control:
Before now, all objects were top-aligned. Now there is a control that allows you to set vertical alignment to top, center or bottom. And there is a formula condition button that allows you to change the alignment based on data or parameter conditions. The function constants are:
crTopAligned
crCenteredVertically
crBottomAligned

Functions:
GetValueDescriptions()
When you create a parameter you are allowed to assign a description to each parameter value. Before CR 2016 you were only able to use the parameter values in your formulas. There was no easy way to get to the descriptions associated with the chosen values, although there were some cumbersome workarounds. Crystal 2016 has a new function that can read this list of descriptions. Here is a formula that would display the descriptions of all the selected values for the {?items} parameter.

Join( GetValueDescriptions( {?Items} ) , ', ' )

Range Functions
These functions are for getting the top and bottom from ranges values, but they don’t provide any new capabilities that I can see. They do the same thing that CR already does with the minimum() and maximum() functions. They were probably added to make the CR formula language more compatible with other languages.

GetLowerBound()
GetUpperBound()

Whenever you want to give Crystal Reports a specific coordinate for an object, you can right-click on the object and use the “Size and Position” dialogue.  Here you can enter the exact XY coordinates for the upper left corner and then enter the height and width.

In the older versions of CR I wrote about a bug in this screen.  That problem was fixed in CR 2008. But I just noticed that CR 2008 and other newer versions (up to the brand new CR 2016) have their own bugs related to that same dialogue.

1) Namely, if you enter a number that is just slightly different from the number already there, the object doesn’t move and the entry is ignored.  There is a minimum amount of change required before any box will accept a new value, and it appears to be .008.

So, say I have an object whose x value is 2.494 and I want to change it to 2.5.  I enter 2.5 and hit OK and it appears that the value was accepted.  But if I go back in and check the X value it is still 2.494.  The box will take .255 but not .250 because the change has to be .008 or greater. But you can get Crystal to take the value you want if you move the object slightly before you try to put in the value. For instance, if you are trying to change the X value, just his  [CTRL-Left Arrow] a few times.  This will move the object to the left. Now you should be able to put in the 2.5 because the new position of the object should be more than .08 away from position 2.5

2) This works great in CR 2008, but when I tested this in the new CR 2016 I found a different bug that wasn’t in CR 2008. When you enter a new Y value it adds the difference between the old Y value and new Y values to the height value.  Sounds like someone used the wrong variable name in the internal CR code.  This bug isn’t in CR 2008, and Ian Cartwright of Vero Software tested CR 2011 and doesn’t see the behavior there. I don’t have CR 2013 to test so I don’t know if that version is affected.