The Crystal Reports Underground News - Volume 2021.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2021:
** Web based deployment options compared (2021)
** SQL to create data dictionary reports
** My library of Crystal Reports materials
** New Web Widget features of Visual Cut
** Missing the recent file list
** Let me create your Crystal Reports
** IF THEN vs SELECT CASE
** Update to my SQL functions “cheat sheet”
** Individual Training with an expert.
** “Convert Null to Default” breaks the selection formula
** How to retrieve your Crystal Reports license key
Gems from the Archives
Percentages in Crystal Reports vs Excel (2010.07)
Preventing Drill-Down (2010.07)
Web based deployment options compared (2021)
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.
SQL to create data dictionary reports
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
So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.
My online library of Crystal Reports materials
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
New Web Widget features of Visual Cut
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.
Missing the recent file list
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 report 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, only shows 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.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
IF THEN vs SELECT CASE
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
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.
Update to my SQL functions “cheat sheet”
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.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
“Convert Null to Default” breaks the selection formula
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.
How to retrieve your Crystal Reports license key
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:
- 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.
- 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.
- 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.
Gems from the Archives
Percentages in Crystal Reports vs Excel (2010.07)
Preventing Drill-Down (2010.07)
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2021 by Ken Hamady
All rights reserved - Republishing this material requires written permission