The Crystal Reports Underground News - Volume 2015.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2015:
** Updated comparison of Crystal Reports viewers (2015)
** Comparing Crystal to other reporting tools
** My library of Crystal Reports materials
** One useful free open source application (Crystal Report Exporter)
** Another useful free (or paid) application (DocumentBurster)
** Let me create your Crystal Reports
** Suppress trailing zeros in decimal values
** Export subreports to Excel with TTX format
** Open enrollment Crystal classes in Frederick, MD
** Missing dll for reading Windows folders
** The function to nowhere.
Gems from the Archives:
SQL Expressions that do Correlated Subqueries (Nov 2004)
When using a Command can Slow Things Down (Nov 2004)
Updated comparison of Crystal Reports viewers (2015)
You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?
The most cost effective method for letting a user run reports is to install a third-party client-based viewer. They are offered by a dozen different vendors. Don't get sidetracked by the "viewer" that is put out by SAP because that tool won't refresh reports. Every viewer in my list allows you to refresh reports.
Every September I compare the features of these viewers and post the results. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (PDF) that shows some of the specifics for comparison like prices. I have even included a glossary of features in case you aren't familiar with the terminology.
This year there are 11 vendors in the review but the vendor for EasyView (EasyStreet Software) is still MIA**. I have not received a response since 2012 and neither have their customers. The Better Business Bureau has them rated "F" so don't waste your time, even though the web site appears to be running.
The other vendors are:
Crystal Corral by Groff Automation
cView by Chelsea Technologies
ViewerFX by Origin Software
Crystal Kiwi Viewer by Crystal Kiwi
Report Viewer Pro by Report Viewer Limited
RV by Climate 27
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW
If you have feedback to share on any of these products, I would love to know what you think.
Comparing Crystal to other reporting tools
Three months ago I mentioned plans for a new comparison matrix. The goal of this new matrix is to compare the features of Crystal Reports to the features of other reporting tools. I hope to find the right list of features to highlight the key differences in the products.
I am now ready to release the first draft of the matrix to get things rolling. I have also published a glossary defining the features included in this current draft. There are still blanks and questions, and the feature list will evolve, but I think we have a good start. The current matrix includes Crystal Reports and 5 other competing products:
SQL Server Reporting Services (Report Builder)
MS Access (reporting feature)
QlikView
List and Label
R&R ReportWorks
There are also blank columns for Oracle BI Publisher and Tableau. I plan to start those next (with some help). If you are proficient in any of these tools (or another competing tool) here are the ways that you can help out:
You can fill in part or all of an empty column
You can review columns that are complete and see if any features are marked incorrectly.
You can suggest new feature rows that you think will highlight the differences in the products.
You can suggest an additional tool like the ones below.
LogiXML Ad Hoc
Cognos Impromptu
Indicee
Windward Reports
Target Reports
Zoho reports
Birt
Birst
Fast Reports
I will add tools based on interest level, and the availability of someone to review the features.
My complete 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 ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
One useful free open source application - Crystal Reports Exporter
I just discovered an open source project on SourceForge that might be of interest to someone.
Crystal Report Exporter allows you to run a report and export it to several different formats, all without having Crystal Reports installed. You write a short command line script which can be put into a batch file. The batch file can be run from a scheduler or run as a shortcut. If you want to try it you can download the file from SourceForge, but you will find instructions and examples on the developer's website.
It is free so don't expect any significant support. I have not yet tried it yet so if anyone has tried it or is interested in trying it, please let me know what you think. Note that the same thing can be done with several of the viewers or schedulers in my annual comparisons. You can look at any of the ones that support command line input for similar capabilities. But this one does come with source code and has no license restrictions at all so that might appeal to users in specific situations.
Another useful free (or paid) application (DocumentBurster)
DocumentBurster is a PDF (or Excel) bursting engine. It can take any multi-page PDF and burst it into separate files or folders, Email them out or deliver them via FTP. It also has command line support so it can be scheduled or run from a shortcut. DocumentBurster does not have an engine to run RPT files so the PDF has to be generated from Crystal, a Crystal viewer or the export tool mentioned in my previous post. All you have to do is make sure that each page of the PDF has a specially formatted (invisible) field. This field tells DocumentBurster which pages go together during the burst. Since the only input you need is a PDF you can use DocumentBurster to burst documents generated by any software package, not just Crystal Reports.
Version 6.2 has been released under the AGPL 3.0 license through SourceForge. That means, among other things, that you can use it for free as long as you don't expect to receive support from the developer. Version 6.3.3 can be purchased from the vendor for $595 which entitles you to one year of support and updates. The web site also lists a free trial option that is good for 25 downloads, but some will probably use the SourceForge edition for their trial.
I have not used it yet but would be very interested in hearing from someone who has. The complete User Guide is also online.
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.
Suppress trailing zeros in decimal values
Formula #15 on my formulas page has always allowed you to convert a number into text and have it not display zero decimals on the right end. So if you had the following three numbers:
1.0500
1.4050
1.2345
They would display like this:
1.05
1.405
1.2345
But the original formula could only handle 6 decimals. This week I need it to go out further and so I decided to improve the formula. Now it can handle any number of digits and you don't have to convert the field to text if you don't want to. There are now two formulas involved. The first just determines how many non-zero places need to be displayed for the field in each row:
//Formula @digits
Local NumberVar X := {Your.Field};
Local stringVar Y := StrReverse (Totext (X - Truncate (X) , 9 , ''));
if Val (Y) = 0
then 0
else Length(Totext ( Val (Y) , 0 , ''))
You can then use this to display your original field with the appropriate number of digits. There are two different methods to use this formula:
1) Format the original field by selecting: [ Format > Field > Number tab > Customize button ]. Then next to the decimals property click the [X+2] condition button and enter the formula field created above {@Digits} and nothing else. This will adjust the number of decimals displayed for each record, based on the number of digits needed.
2) Alternately you can use the first formula to create a separate formula field that converts {Your.Field} into text with the appropriate decimals:
Totext ( {Your.Field} , {@Digits} )
This creates a separate string field. You can still use the original numeric in any calculations.
Export subreports to Excel with TTX format
I have written before (several times in fact) about the challenges of exporting to Excel when you have subreports. It is difficult to avoid merge cells unless you get the alignment just right. Recently one of my readers told me that he exports to TTX format in this situation and finds it works better. I did a quick test using a report with a subreport on GF1. The subreport was just a bit below the other fields. When exported to:
1) Excel there were extra merge rows between the data rows
2) Excel (data only) the subreport was on a separate row
3) Tab Separated (TTX) the file would open cleanly in Excel
I am sure there are other variables but if you need to export to Excel format and there is a subreport involved, this is one more option to try.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my January classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
Missing dll for reading Windows folders
One of my customers received an upgrade from CR XI to CR 2011 when he upgraded to SAGE 100 software. He was surprised that the new version was missing the DB connector he used to report off of his file system for document management and version control. I have written about reports that read a Windows folder before. I suggested that he try a custom install to make sure that the option was checked and he reported that there was no option for this driver in his install.
So I had him run one of the existing reports that used this connection to see if it would run. CR gave an error that said he was missing the CRDB_FileSystem.dll. So I told him to move the CRDB_FileSystem.dll from the XI environment to the 2011 environment and then the report ran with no problems.
I am not sure if this dll was dropped from the SAGE distribution, or dropped from the 2011 upgrade but if you are using this type of connection and lose it on an upgrade, you should be able to get it back with the dll from your old environment.
The functions to nowhere
A customer recently asked me how to use the vertical alignment formula functions in Crystal reports. He had found them on-line and was surprised that there were such functions available. So was I. But then I typed the following functions into a formula:
crTopAligned
crCenteredVertically
crBottomAligned
Sure enough, all three of them turned blue and were recognized by Crystal Reports as valid functions. But after the initial excitement I realized that having a function does no good unless there is a formatting property where the function can be used. For instance the function CrRed can be used in any color condition formula, and the function crCenteredHorizontally can be used in the condition formula for horizontal alignment. But there is no vertical alignment formatting property in Crystal Reports. So after some more on-line research we figured that there must have been a plan to add vertical alignment options to Crystal. These function constants were added for them. But then the property never made it into the release and so the functions were orphaned.
And since SAP isn't planning any major enhancements for standalone CR, I doubt that these functions will find a home any time soon.
Gems from the Archives:
SQL Expressions that do Correlated Subqueries (Nov 2004)
When using a Command can Slow Things Down (Nov 2004)
Removal instructions
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
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 2015 by Ken Hamady
All rights reserved - Republishing this material requires written permission