Contents for May 2011:
** New features in Crystal Reports 2011
** License changes in Crystal Reports 2011
** Update to my comparison of server-based schedulers
** My library of Crystal Reports materials
** Tools for troubleshooting
** Case study for UNION of daily files
** New Crystal Reports licensing overview
** Let me create your Crystal Reports
** Formatting chart dates (part 2)
** Mysterious blank pages
** Open enrollment Crystal classes in Frederick, MD

Other recent blog articles:
    Crystal Reports 2011 release notes
    Evaluate CR Server "in the cloud" (or maybe not)
    Odd error using ODBC to IBM U2
    Classic VB runtime and PDF exports

New features in Crystal Reports 2011

We have waited 3 years for a new version of Crystal Reports, so you might be a bit disappointed at the list of new features.  They are the ability to:

1) Export to XLSX format which gets you past the 65K limit on rows.
2) Export to RPTR format, a format that can't be opened in the Crystal Designer, but can only be run in one of the report viewers.  This lets your users run your reports without giving them access to any proprietary information contained in the RPT file. For an explanation of why there are so few new features you can read this SAP blog post.

Also note that this is officially version 14, not version 13.  Version 13 is the developer engine in Visual Studio 2010..

Crystal Reports 2011 license changes

Crystal product managers and I have a tradition that we follow with every new version:

1) Whoever happens to own Crystal that week puts in excessive restrictions.
2) I point out that the restrictions are excessive.
3) They have a serious internal discussion.
4) Things (usually) change for the better.

This time the issue was the restrictions in section (7).  If you strip it down the long legal paragraph you get this sentence:

"Except as expressly permitted… you may not … use the Software to provide …. third party training, … or consulting services, or any other commercial service related to the Software …"

This same wording was included initially with CR 2008 and then removed after I started to write about it.  If these restrictions were ever enforced they would choke off the entire ecosystem that has given Crystal its position as market leader.  As I reread the license I got some hope from section 4:

"PRODUCT SPECIFIC USE RIGHTS. Additional terms relating to your use of the Software are found at www.sap.com/company/licenses/product-use-rights" …

But when I tried to follow that link to see what additional rights I had, things got bizarre.  You can read the details in my blog, but lets just say no mortal would ever find the information. And once found the information didn't address the new restrictions.

So I sent some feedback to the folks at SAP.  They had some internal discussions and now assure me that they intend to make some adjustments to the restrictions section.  The plan is to provide essentially the same user rights that we have in CR 2008.  And they are also going to make the link to the "user rights" document go directly to the document and include a meaningful table of contents.  All of this is slated to be updated sometime in July.

Update to my comparison of server-based schedulers

In my March newsletter I updated my review of desktop scheduling tools for Crystal Reports.  These tools allow you to automatically run a report, export it to a file and deliver it to an Email InBox at a set time every day or week.  This months review is also for scheduling tools, but while the March list was desktop-based schedulers, this list is server-based schedulers.

Most people who need to schedule reports will be fine using a desktop scheduler, even if they have to deliver reports to many recipients.  But in some environments there will be many people scheduling reports and in those cases it might make sense to upgrade to a server-based scheduler.  So my blog post compares eight server-based tools, providing you with their core features and prices so that you can narrow down your search.  Two of the tools are new this year:

The tools being compared are:
    Navarre Report Scheduler by PCS Programming
    cView SERVER by Chelsea Technologies
    Ps Report Scheduler by Perryman Software
    rePORTAL SC by rePORTAL Software
    Report Runner Batch Enterprise by Jeff-Net
    Liaison Messenger EDD by Liaison Software Corp
    Universal Report Server by VersaReports
    CRD Premium Edition by ChristianSteven Software

You can read a brief rundown of each product and download a feature comparison matrix (PDF) that gives the details.  And, with so many complex features I have written a feature glossary to define the key features.  

Finally, if you have tried one of these products and haven't given me your opinion yet, please do.  

My complete library of Crystal Reports materials:

Do you struggle with subreports?  Are you curious about cross-tabs? 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.

Tools for troubleshooting

Recently an unusual number of customers have brought me errors to troubleshoot.  Most of them involve errors outside of Crystal with things like missing DLL files or installs that seem the same but that behave differently.  So I was pleasantly surprised to find a recent blog post on the SAP web site that provides a list of free tools for helping to troubleshoot things like this.  There are 5 tools listed but I think two would apply most to the problems I faced this month:

1) Modules is a free utility from the SAP web site.  It allows you to run a report on two different systems (ie one that works and one that doesn't) and find all the files that are different in those two environments.  It makes a list of the files used in each environment and then compares to see which are different. It is an old tool but worked fine in my Win7 64 environment.

2) Depends.exe (Dependency walker) is another free tool that takes an EXE or DLL file and lists all of the other files that it relies on.  This is perfect for when you have a DLL that won't work or register correctly.  Often a missing dependent file is part of the problem.

And if you need to troubleshoot hangs and crashes, monitor HTTP / HTTPS traffic or monitor local file system and registry activity, the other tools should help.

Case study for UNION of daily files

I recently found a way to mix several concepts and solve a problem that others might be facing. The challenge came from a company who stores several hundred thousand log transactions each day. The way the system is set up, each day's transactions are stored in a separate SQL table. The table name is the date of the transaction.  What they wanted to do was to run reports that summarize these transactions for an entire month, with subtotals by category. This would require combining a month's worth of daily tables on the fly, and then getting subtotals by category.  At first I recommended a stored procedure because I didn't think we could calculate the table names on the fly in a SQL command.  But we aren't allowed to add SPs to the database.  Then I realized that by adding the report as a subreport we would have the chance to do the calculations we needed within Crystal.

So, my first solution was a 2-step process.   We created a summary report for one day with subtotals by category.  The report used a SQL command and we put a parameter field into the FROM clause where the table name would go.  That allowed us to run any day we wanted by entering the date into that parameter.

Then we created a container report with up to 31 details based on the days in the chosen month. It had a formula that calculated the table names for all of the dates in that month. We put the subreport in at the detail level and linked the formula to the parameter so that the report ran one subreport for each day and generated the category summary rows for each day. We exported this to a spreadsheet, creating a handful of rows for each day in the month, each row showing category totals.  Last we created a second report to read the summary rows in the spreadsheet and combine them to get category totals for the entire month.  It worked great, but the 2-step process wasn't ideal.

Then I thought through another approach that would eliminate the need for two steps – although it might be stretching SQL to make it work. Check out this blog post for more details.

New Crystal Reports licensing overview

Blair Wheadon of SAP has published an updated overview of Crystal Reports licensing terms.  He does a good job and covers multiple versions and editions.  So this is a good place to start if you have question on product licenses.  If this doesn't answer your questions I might be able to help (I actually read them).

My only other comment on this article is that SAP still has not given up trying to add a hefty fee to every Crystal Reports course book produced by anyone.  They try to do this by adding restrictions within the product license covering the use of screen shots.  So, Blair reminds us that 'commercial course material' is the only medium that is not allowed to use these images.  And if someone insists on creating course material for Crystal Reports, SAP will want to charge a significant fee.  One vendor who wanted to be 'authorized' was selling Crystal course materials for $65.  As soon as they were authorized their price went to $98, and they are now at $140 for a single book.  Fortunately there are several companies that ignore SAP on this and sell more reasonably priced books for $55 to $80.

Personally, I have never felt screen shots to be essential, so my course books have been without images for 16 years and have been used to teach thousands of satisfied students.

 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.

Formatting chart dates (part 2)

Last month I wrote a post to complain that Crystal didn't give you a way to directly control the format of dates along the bottom of a monthly bar chart. So, of course, this week I find something in Crystal Reports that I had never seen before, and which shows that I was only half correct.

A customer sent me a chart to troubleshoot and he had used a chart type called "Numeric Axis".  This type of chart is down the list pretty far, below Radar charts and Bubble charts so I have never experimented with it.   But when I changed the chart to a normal bar chart it became obvious why this type was used.  The number series along the bottom of the chart had changed to show decimals, and I now had no way of changing the format so that the decimals didn't show.  I hit undo and saw that this chart type allowed me to directly control the format of a series of numbers at the base of a series of bars or lines.

My first thought was why hadn't they done the same thing for dates.  My second thought was that maybe they had, so I went back into the options for these Numeric Axis charts.  Hidden in there were three options for date axis charts – Line, Bar and Area.   These options only give you 8 date formats to pick from, so you don't have as much control over the format as with the methods I described.  But they are worth mentioning, since they will probably work well enough in 80 percent of cases.

Mysterious blank pages

 I am used to helping users troubleshoot blank pages in their Crystal Reports.  The usual culprits are blank report footers or group footers that need to be suppressed.  Occasionally there is a page break that isn't put in correctly.  But I recently had one that I had not seen before.  In preview the report showed 3 pages.   But if the report was printed there were double the number of pages, with a blank page between each printed page.  The same doubling would happen when it was exported to a PDF.

What I found was an image object (an OLE object) that was just a hair wider than the printable area on the page.  In preview this would show as a white space to the right of the printed page, which was easy to overlook.  It also didn't affect the page count.   But when printed or exported to PDF it would alternate printed and blank pages and double the page count.  Once the OLE object was moved to within the printable area the blank pages went away.

This surprised me because normal CR objects that have a static size (fields, charts, boxes, etc) are just truncated if they stretch to the right too far.  The only object that I knew of that could generate these pages to the right were cross-tabs,  because their width is dynamic.   So if you have a mysterious extra page in your report this is one more thing to check.

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 June 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.

Or, if you want to schedule a class at your office, using your data, that is my specialty.  I also have several top-notch instructors in the the US, UK and Canada that can deliver my class at your location for a very competitive price.  Call or visit my web site for more details.

Other recent blog articles:

    Crystal Reports 2011 release notes
    Evaluate CR Server "in the cloud" (or maybe not)
    Odd error using ODBC to IBM U2
    Classic VB runtime and PDF exports

Contact Information: