Starting with v11.5 Crystal has made it easy to find all uses of a field within the formulas of a report.  Just right click on the field  (database, formula, parameter, etc) and select “Find in formulas”.   Even for those of you using older versions of Crystal there is a workaround in my Expert Techniques vol. 1.

But finding where a variable is used is a bit trickier.  Unlike fields, there is (currently) no central list of all the variables used in a report.  But I just noticed a feature that must have been in the formula editor for at least 2 years.  Open a formula and click on Continue Reading »
Finding all uses of a variable

One of my clients just sent me a formula that turns the numbers from 1 to 20 into superscript characters.    You could do the same thing with subscript characters if you wanted to, but you would need to look up all the Unicode values in a chart like this one.   Note that you must display this formula using a Unicode font (they usually say “Unicode” in the font name) or the characters will space and size inconsistently.

select  totext({Number.Field},0)
case "1" : ChrW (185)
case "2" : Chrw (178)
case "3" : Chrw (179)
case "4" : ChrW (8308)
case "5" : ChrW (8309)
case "6" : ChrW (8310)
case "7" : ChrW (8311)
case "8" : ChrW (8312)
case "9" : ChrW (8313)
case "10" : ChrW (185) + Chrw (8304)
case "11" : ChrW (185) + ChrW (185)
case "12" : Chrw (185) + Chrw (178)
case "13" : Chrw (185) + Chrw (179)
case "14" : ChrW (185) + ChrW (8308)
case "15" : ChrW (185) + ChrW (8309)
case "16" : ChrW (185) + ChrW (8310)
case "17" : ChrW (185) + ChrW (8311)
case "18" : ChrW (185) + ChrW (8312)
case "19" : ChrW (185) + ChrW (8313)
case "20" : ChrW (185) + Chrw (8304)

And thanks to Melody Mulligan of Crystal Clear Solutions for the tip.

I like using the “report wizard”  to create the first draft of a new report.  It was even better back in v8.5 when you had the report expert and could loop through the steps several times.  But even now I find the wizard helpful.  Not only can you quickly assemble a simple report, but it automatically gives you a nice evenly spaced column layout.  And, it does a few things for you that you are very likely to do anyway – like adding a page number or today’s date.

Yesterday I was working with a customer in version 11 (XI) and we noticed a strange behavior in the wizard. We went one step further than we wanted to and hit the ‘back’ button in the wizard to go back a step.  We ended up losing part of the wizard window on the right and couldn’t get it back.  I ended up clicking “Finish” just so we could get out and we did the last steps through the normal menu.

I found out that this is a known issue in some builds of XI.  There is an article on the SAP/BO support site but fixing it involves a registry edit. If you have this issue and want to resolve it you can go to the NOTES section of the BO support site and look for article # 1246536 entitled:

“Standard Crystal Report creation wizard screen shrinks from right side when the ‘Back’ button is clicked”.

Most databases are designed with special tables or views that are designed to store the structure of the database. Each database platform is different but the list below should be a start. To get a list of tables you can use a command object that says the following:

Oracle: SELECT table_name FROM all_tables

SQL Server: select name from sys.objects where type = ‘u’

MySQL: write a SQL command that says simply: SHOW TABLES
or  SELECT * FROM INFORMATION_SCHEMA.TABLES

For more on MySQL System tables you can use this link

MS Access : SELECT * FROM MSysObjects where type = 1
1) You have to set the Acess database options to “show System tables”
2) Then in CR options check off “show system tables”.

For more on MS Access system tables you can check out this link

Older databases like early versions of Dbase, FoxPro or Btrieve store each table as a separate file in the Windows file system.  Since you can use CR to read the files in a Windows folder you can generate a list of tables using the files.  You can also report on the file size and the last time it was changed, which are not as easy in the others.

And thanks to William Chadbourne of OIT-DEP in Maine for correcting my original Oracle command.

Last week a customer called me after they did an upgrade of PeachTree Accounting.  It seems that the records in the report that used to sort were now appearing in random order.  As soon as I added the sort back in and refreshed the report, all the records disappeared.  Fortunately I had seen this before because I work quite a bit with PeachTree and PeachTree uses Btrieve as a database engine.

This week I had another Btrieve customer (not PeachTree) who wanted to add a parameter to select a part number.  We added the rule in the selection formula and, again, all the records disappeared.  Same problem same solution.

Apparently when Crystal sends a query to a Btrieve engine, Btrieve tries to ‘help out’ by Continue Reading »
Vanishing data in Btrieve

I have come to depend on Crystal’s Guidelines for aligning objects. I find them much more useful then the “Snap to Grid” feature, although I do use both from time to time. But there are often cases where the object needs to be somewhere in between. It can be frustrating to try and position an object while it jumps up or down because it is trying to snap to a grid point or a guideline.

If you run into this problem there is a handy solution. Hold down the “ALT” key while you are moving or sizing the object. The “ALT” key temporarily turns off both snap to grid and guidelines, without making any permanent changes to your settings.

Normally if there is an unlinked table in one of my reports it is there by mistake.  However, I have recently been reminded of some of the neat things you can do with unlinked tables, as long as you are careful.  The official name for having unlinked tables is a ‘cross join’.  The data that is returned from a cross join is known as a Cartesian product, which means every possible combination of records between the two tables. That is why you have to be careful.

So say you have a customer table with 100 customers in it and you use that table to create a report listing just the customer name.  Without adding any filters you would get all 100 records in the report.  But then you add a second table that doesn’t link directly to the first, something like the master list of products.  If this table has 25 products in it then Continue Reading »
Using unlinked tables (cross joins)

If you need to pull a single character out of a character string, or a single item out of an array, you can use a subscript to identify the position you want like this:

{Customer.Customer Name} [3]

This example starts counting from the left to identify the third character or element.  Over the years I have had people tell me you could use a negative number Continue Reading »
Using negative subscripts

If you need to buy an older version of Crystal Reports, all the way back to v8.5, you can now download them directly from BO (for $595).  This link includes all versions from 8.5 forward.

If you want to save a few bucks you can check on Ebay, where I regularly find older versions for sale.  Just make sure you know what you are purchasing.  In 2002 I wrote an article about what to look for when buying CR on Ebay.  That article still applies, although some of the product codes listed might be different in newer versions.

Or check with me.  I sometimes know of extra copies that are available.

I recently wrote a blog post to remind developers that you can’t integrate CR into a VB.NET application if you are using the .NET Express Edition.   So I was surprised when a developer wrote to me that he was using the Express Edition to run my sample apps.  These are simple apps that I created for my guide to Crystal Reports in VB.NET.  He said VB wouldn’t let him add a the CrystalReportViewer control onto a form, but if the control was already there he could use it.  He then used the free runtime files Continue Reading »
Using Crystal Reports in VB.NET Express Edition