The Crystal Reports® Underground

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.

Most of my readers know that I don’t think much of the various Crystal Reports certifications that have been offered over the years.  But for those of you find these valuable, you should know that SAP is changing the Business Objects certification exams to align them with the SAP exams. You can read more about it on this BLOG post by Kenneth Schieffer.

The summary is that there will be only one 3-hour exam for each product line (Crystal Reports, BO Enterprise and Web Intelligence).  Passing the exam now makes you an “SAP Certified Application Associate.  And if you are only partially through the current exam process you have until April to complete the exam under the old scheme. Otherwise you have to start over with the new scheme.

Thanks to Gordon Portanier of Crystalize in Canada for pointing this article out to me.

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

It appears that SAP will release a new version of Crystal Reports this year.  This isn’t a big surprise given that it has been two years since the last version.  There are no details yet but George Peck mentioned in a recent article that the SAP Users Group might give a preview in the next month or so.   Anyone have any details to share?

And thanks to Gordon Portanier of Crystalize in Canada for giving me the heads up.

There are many ways to deploy Crystal Reports to users.  I normally lean toward the simpler and less expensive options, like locally installed viewers.  But there are environments where a server based option is necessary.  The ‘official’ options from SAP are Crystal Reports Server and BO Enterprise.  You can read about these on the SAP website under the heading “SAP Business Objects portfolio”.  But there are other products out there that most users never see. These are third party products that also allow you to centrally manage your report deployment from your server.  The list includes:

Ripplestone – Traditional portal
rePORTAL - Traditional portal
ReCrystallize Pro – Web Launch page generator.
Universal Report Server (URS) – Server-based scheduling engine
Report Launch – Bridge between BO server products and server based applications.

The products vary widely and are aimed at
Continue Reading »
Server based options for Deploying Crystal Reports

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)