Archive for January, 2021



Web based deployment options compared (2021)

Tuesday 26 January 2021 @ 9:25 pm

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also provide web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.

I have created a page on my blog that lists and compares these products, and I update it every January with info from the vendors. This year the list features 8 products:

Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Server – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
Bezlio – a SaaS Web viewer

The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (xls) that shows some of the specifics for comparison, including prices.

If you have any feedback to share on these tools I would be happy to hear from you.




Update to my SQL functions “cheat sheet”

Friday 22 January 2021 @ 11:25 pm

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.




“Convert Null to Default” breaks the selection formula

Monday 18 January 2021 @ 1:15 am

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.




New Web Widget features of Visual Cut

Sunday 10 January 2021 @ 10:22 pm

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.




Jeff-Net

Recrystallize Pro

Crystal Reports Server