I had a customer today who wanted to write a formula that assigned a numeric value to each Employee. Normally I would write an IF THEN statement like this:

if {table.EmpNO} = 123 then 10 else
if {table.EmpNO} = 124 then 15 else ...

But the list of employees and values was in a spreadsheet with 4000 employee rows. A linked table wasn’t an option so I decided to do something I don’t do very often. I wrote the formula using SELECT CASE instead of IF THEN.

Normally I prefer to use IF THEN because you can use more complex conditions.  I also find that IF THEN reads more naturally. But in cases like this where you have a single field and LOTS of comparison values, the SELECT CASE pattern is more efficient. The pattern below reduced the total character count by 70% of an equivalent IF THEN structure:

Select {table.EmpNO}
case 123:10
case 124:15
...
default : 0

The first time I used SELECT CASE was when I had to translate a large spreadsheet grid into a formula. I wanted the formula structure to look like the spreadsheet so we could visually check the formula by comparing the columns. The spreadsheet grid had 30 rows (Types) and 20 columns (Sizes) so my formula ended up with 30 rows that looked like those below, except that each row had 16 more case instances where you see the ellipsis […]:

if {prod.Type}=1 then(select {prod.Size} case 01:03 case 02:03 case 03:05 case 04:06 ... default:00) else
if {prod.Type}=2 then(select {prod.Size} case 01:02 case 02:03 case 03:04 case 04:05 ... default:00) else

Note that I combined an IF THEN to determine the row (Type) with a SELECT CASE to determine the value for each column(Size).  I found this more manageable and easier to validate than 600 additional IF THEN loops.

When working with customers I often re-open recently used files.  Recently it seems that some of my customers versions of Crystal don’t show the recent files in the file menu.  This puzzles me because I am on CR 2016 and I have always seen the list of recent files at the bottom of the File menu.  There is a short list on the start page as well, but the longer list has always been in the menu.

Yesterday one of customers shared with me that she had been struggling with the same issue and then stumbled across the recent file list in a new place.  There is a yellow folder icon on the toolbar which represents “File > Open”.  Next to this folder is a small drop-down arrow.  Clicking that arrow shows the recently used files.  Neither she nor I had noticed this so I am betting that we are not the only ones.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for pointing this out.

On a related note, you can change the number of files that are shown in this list.  The default is 5, but you can increase this number in the registry.

The key is here:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\
Crystal Reports\Recent Files

The value to change within that key is called “FileCount”.

But there is also some strangeness about the filecount value. You can put in any number, but the registry only has 10 slots. So any number beyond 10 has no where to go. Then, the list in the file menu can only display the first 9 items of the 10 so I am not even sure why they have a 10th item. The other list, the one in the box on the Start Page, can only show the first 5 of the 10.

The other mystery I haven’t solved (yet) is why my install of CR 2016 still shows the recent files in the “File” menu,  while many Crystal installs do not.

In older versions, all you had to do to retrieve your Crystal Reports license key was go into Help > About. That screen would show the key and your registration number (if you registered the software). In more recent versions the key is no longer there. There is a license manager under the [Help] menu but it only shows you the first few and last few characters of your license key. I assume this was intended as a security measure.  However, if you need to reinstall Crystal Reports when you upgrade your hardware you might struggle a bit. Here are three other ways to find your key:

1.  Check your Email. Most installs are downloads and the key is Emailed to the person making the purchase. You might have received that Email or had it forwarded to you.
2.  Call SAP Sales. If you purchased it directly from SAP (the most common option these days) they should be able to look up your account and give you the key.
3. Or, my favorite, pull it from the registry. You will probably find it in this registry key:

HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/SAP BusinessObjects/
Suite XI 4.0/Crystal Reports/Keycodes/CR Dev

The registry key will contain the license key followed by an 8-digit numeric date, separated by a colon.

I was recently trying to create a report and was having trouble finding a specific table. I knew the field name, but it wasn’t in any of the tables where I expected it to exist. Lets just say the table names in this database are cryptic.

Fortunately, most of the mainstream databases allow you to query the system tables to list all the tables and fields. I found some great SQL online and created a report to read the table structures and search for the field I needed. The query allowed me to create a searchable data dictionary report, or schema, for this database.

The SQL example I found is on a site that lists similar SQL for other databases as well:

This example is for MS SQL Server:
https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

This example is for Oracle:
https://dataedo.com/kb/query/oracle/list-table-columns-in-database

This example is for MySQL:
https://dataedo.com/kb/query/mysql/list-table-columns-in-database

This example is for Pervasive:
https://communities.actian.com/s/question/0D53300003xbnTrCAI/how-to-query-schema

So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.