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.

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

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

I recently was rereading some Tek-Tips.com FAQs and spotted a FAQ by synapsevampire that I didn’t remember.  It had to do with creating a combined list of detail values that can print as a single string.  I have a formula for this on my site, but it happens WhilePrintingRecords, which means you can only display the combined values at the bottom of the report.  With this method you populate an array during the WhileReadingRecords step of report processing.  Because this array is complete before printing starts it can be Continue Reading »
Building arrays WhileReadingRecords

There is a link on my library page that explains how to enhance the mapping feature in Crystal reports to include maps by zip code or county.  Unfortunately, adding these features isn’t free.  You have to purchase additional map ‘layers’ for the MapInfo module in CR.  MapInfo is now part of Pitney Bowes.

One of my readers, Robert Meyer, just let me know that he called Pitney Bowes and found that my pricing information was out of date. They used to sell zip codes and county maps one state at a time.  A zip code map for a single state was $395 for one user.  Now you can get a zip code map for all 50 states for $2,250 (still one user).  Not that this is cheap but it is much better than before.   A county map used to be $125 per state per user and it is now $495 for all 50 states.

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

Have you ever copied a group of objects and then pasted them to another section or another report?  When you click “paste” you only see one of the objects on your cursor and the rest appear when you click on the page.  So which object is the one you see first? The one on the right? The left?  So you guess and select a spot for that object (oops), click undo, try again (oops), etc.  Wouldn’t it be nice to know exactly which object is on your cursor so you know how to align the group?  Or even better, wouldn’t it be nice to select this “cursor” object at the time of the copy?

The same challenge comes up Continue Reading »
Selecting multiple objects (to copy or align)

One of my customers recently needed to change a date into text for contract wording.  That meant changing 9/1/2009 into  “this 1st day of September, 2009″.  Most of it was simple but the tricky part was adding the “ordinal suffix” (as in 1st, 2nd, 3rd, etc).  So I did a bit of playing around and came up with this:

WhileReadingRecords;
NumberVar DayIn := Day (PrintDate);
"This "
& Totext (DayIn , 0 )
& (if DayIn in 4 to 20 then 'th' else
if remainder (DayIn , 10) = 1 then 'st' else
if remainder (DayIn , 10) = 2 then 'nd' else
if remainder (DayIn , 10) = 3 then 'rd' else 'th')
& " day of "
& Totext (PrintDate , "MMMM, yyyy")

Note that there are several formulas that can do ordinal calculations for larger numbers, but this simple one works for dates, since day numbers are never bigger than 31.