Archive for February, 2010

Superscript numbers in a Crystal Report

Monday 22 February 2010 @ 2:50 pm

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.   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.

Changes coming for the SAP/BO certification exams

Wednesday 17 February 2010 @ 2:24 pm

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.

Screen Problems in the Standard Report Wizard

Tuesday 16 February 2010 @ 9:59 pm

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”.

Update 7/12/2015:

A temporary solution is to resize the window. That usually straightens things out till the next time you click ‘Back’ in the wizard.

Reporting on the Database Schema

Tuesday 9 February 2010 @ 11:19 pm

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

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.

Update 12/9/2020: Added a post with links for SQL to generate a Schema in SQL Server, Oracle or MySQL. 

Recrystallize Pro