If you have been to my Website in the past week you may have noticed that it has a new look. My focus has always been on content, but after a helpful nudge from a colleague I realized that my ‘classic’ style (circa 1997) was not showcasing the content very well. So I took advantage of a slow week, learned some new software, and gave the site an update.  I even have a Twitter account now (see the search bar above).

So far the feedback has been positive. And for me, the new software (Web Expressions 4) makes running the site much easier, so I am happy with the result.

However, there are over 200 pages so it is likely that I missed a few things, especially related to incoming links from external sites. If you see anything broken please let me know.

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

There is one new product this year for a total of 11 products. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

I have a formula for calculating an age integer that has been on my site for a decade.  Yesterday I thought I had found a bug in it.  After I fixed the formula I did some more research and found that the original was correct in most situations. The question centers on cases, when a person is born on a ‘leap-day’ (February 29) and when exactly they turn a year older in non-leap years where there is no date of February 29.  In the UK and in Hong Kong their age would increment on March 1. In New Zealand or Taiwan their age would increment on February 28.  In many other jurisdictions, like most of the US, this isn’t specified anywhere, although the norm seems to be March 1.  My original formula will increment them to the next year on March 1.  But I have added an optional 5 lines of code to the formula for those who want the change to happen on 2/28.

I recently worked with a customer who needed to incorporate some Excel data into a report. Excel tables are not my first choice, but sometimes it is the only practical solution for the client. Since these were XLSX files we setup up an ODBC connection. But when we connected from Crystal we could not see any tables under that ODBC connection. We were thinking about re-saving it as an XLS when I did a quick online search and found the answer we needed in a forum post.

We had to go into File > Options > Database tab. Then under Tables and Views we had to add a check mark for ‘system tables’. Then the spreadsheet table showed up. I was surprised, because I have created many reports that read spreadsheets and I don’t ever remember having to do that. It is possible that there was something unique in that environment that required this change, but I never argue with success. And, if you ever run into something similar you have one more thing to check.

This week we have a clever trick that was shared by Larry McCulloch of Lares Research as part of a Linked in discussion. The question was how to have the report automatically show the database being read. Apparently someone had run a report in a training/testing database and it had gotten mixed up with reports coming from the live database. If Crystal knew which database it was reading it could flag reports that were coming from someplace other than ‘live’. Larry’s suggestion was to add the following SQL expression in SQL Server environments):

{fn DATABASE()}

Then people suggested various was of doing the same thing in Oracle environments:

(SELECT ora_database_name FROM dual)

(SELECT sys_context ( 'userenv' , 'db_name' ) FROM dual )

(SELECT global_name FROM global_name )

I tested the SQL Server example but I don’t have an Oracle environment to test the others. If you try them or have some feedback, let me know.

update 3/25/2014 – I heard from an Oracle user that all three examples work.

update 10/12/2015 – in SQL Server you can also use:  ( SELECT DB_NAME()  )