Today I had to use a SQL Command to create a report. This isn’t unusual since a query of even moderate complexity will often require that you use a SQL command. But, the extra challenge today was that the database was DB2 and I don’t often work with that flavor of SQL.

Even though SQL is considered a “standard” language, every database has different function names and different syntax. A few years ago I created a “cheat sheet” that lists the most common SQL functions for the 7 SQL flavors I encounter the most:

• SQL Server
• MySQL
• Oracle
• MS Access
• Pervasive
• Progress
• PostgeSQL

I added an 8th column for DB2 a while ago, but then I didn’t get any opportunities to use DB2 SQL so that column remained empty. Today while I was testing functions and syntax for my new command I decided to see how much of that DB2 column I could fill in.  I did some and then Greg Nash from Australia provided some more. It is now fairly complete and I have posted the updated sheet on my site. Please download a copy if you think it will be useful.

Recent versions of Crystal allow you to automatically “Convert Nulls” in specific formulas. The setting is on the formula editor tool bar as a drop down.  You can change “Exceptions for Nulls” to “Convert Null Values to Default values”. Crystal then automatically replaces any null values encountered in the formula with an empty string [""]. This prevents your formulas from returning a blank whenever they encounter a null value.

This works great for formula fields, but not so great in the record selection formula. This problem is that this setting doesn’t get converted into SQL.

Let me give a simple example from the Xtreme sample data. There are 10 records in the customer table that have a null value for Postal Code. If I write a formula field that says:

if IsNull ({Customer.Postal Code})
then "Unknown"
else {Customer.Postal Code}

It will show TRUE for these 10 records. I could also write a formula that says:

if {Customer.Postal Code} = ""
then "Unknown"
else {Customer.Postal Code}

These 10 records will show blank at first, but if I set that formula to “Convert Null to Default”. Then it will also show TRUE for those 10 records. Now lets say I use this as my selection formula:

IsNull ({Customer.Postal Code})

That will pass to the WHERE clause as:

`Customer`.`Postal Code` IS NULL

and it will select those 10 records. But, if I write the selection formula like this:

{Customer.Postal Code} = ""

Even if I tell the selection formula to “Convert Null to Default”, the WHERE clause will be:

`Customer`.`Postal Code` = ''

There are no records that meet that criteria and so no records will be selected.  The key here is that using “Convert Null to Default” works within Crystal, but is not translated to SQL. That is why I avoid using this setting in the record selection formula.

I recently got to experiment with the new “Web Widget” features of Visual Cut. This adds two additional export options that allow you to create either an interactive Web Grid or an interactive Web Pivot Table. These files can then be uploaded to virtually any web site, allowing users to interact with the data.

To make this work the report should be very simple, either details with column headings or a single group level with column headings (and hidden details). You export the report to one of these formats and Visual Cut creates an HTML template file with a JSON file to hold the data. As a test I exported a simple report and then uploaded these files to my own web site. In my test I was able to interact with the Pivot table to adjust Rows and Columns, add filter and totals, and add a chart to visualize the data.

My test only scratched the surface. To learn more about what you can do with this new feature you can watch the demo videos (Web Grid Video and Web Pivot Table Video) or read the new section in the Visual Cut user manual. You can also try out the demo pages for either the Web Grid or the Web Pivot Table.