Contents for May 2013:
** Server-based scheduler comparison (2013)
** Crystal Reports vs SSRS
** Next week's classes in Frederick, MD
** Formula for age in years and months
** Date groups that don't do what you expect
** Let me create your Crystal Reports
** Saved data vanishes when you narrow the criteria.
** Copying text into Crystal Reports
** My library of Crystal Reports materials
** SQL union vs cross join
** Better way to change the row/column field of a cross-tab

Other recent blog articles:
Using SQL commands in ABRA HR

Gems from the Archives (May 2002)

Server-based scheduler comparison (2013)

I have just updated my comparison of server-based scheduling tools for 2012. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on a server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 8 products on the list this year with a few feature updates and price changes since last year.  The article provides a brief overview of each product.  It also links to a feature matrix that compares roughly 70 features of these tools.  There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a good place to start.

Crystal Reports vs SSRS

When I started teaching Crystal Reports in 1995 I was also teaching another tool called R&R. In comparing the two I found that R&R had about 20 features that were missing from the 1995 version of Crystal.  Crystal had five features I liked that were missing in R&R. But within a year the numbers had flipped. Most of the R&R features that I had mentioned were now included in Crystal and that started my transition. So yesterday, when I made my first attempt at comparing Crystal Reports and SQL Server Reporting Services (SSRS) there was a bit of déjà vu.

For the past 8 years I have been able to happily ignore SSRS, without even feeling the need to look at it. But three recent events have me paying more attention to SSRS:

1) Someone recently asked me if I had a more up to date comparison of Crystal and SSRS. The comparisons I link to are from 2005 so I started looking for more current information.

2) In the past month I have been contacted by 2 RPT conversion vendors who want me to mention their services. These vendors have developed software that helps them convert your RPT files into SSRS RDL files (with varying degrees of success).

3) I learned from one of these vendors that SSRS has a free, stand-alone "Report Builder" that allows you to create and run RDL-based reports. You don't need to install SQL Server or create a .NET application. All you need to install it is the (free) .NET framework (3.5).

From my quick experiment with the Report Builder I can see that SSRS has a long way to go before I start my next transition. But now I can judge for myself the differences between Crystal and SSRS. Here is my opinion in a nutshell:

SSRS provides a rich development environment for programmers but it has a limited feature set for report developers. The GUI is geared toward programmers. It is currently free or reasonable to deploy.

Crystal has a rich reporting feature set and a GUI designed for report builders. It also has a mature ecosystem with lots of 3rd party products geared toward end users. But it has an SDK that developers hate, coupled with expensive and proprietary web deployment options.

I think this explains why many programmers hate Crystal with a passion at the same time that Crystal maintains its position as the market leader.

In the next few months I expect to spend more time road testing SSRS and writing about the differences. But as a starting point you can look at this excellent list of Crystal features missing in SSRS. It was created by one of the CR to SSRS conversion vendors which is why it is both knowledgeable and objective, probably more objective than even I could be.

Next week's classes in Frederick, MD

Come join us next week for my Crystal Reports class and learn how to make Crystal Reports work for you.  My Intro class will make sure you know all of the basics, including 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.

Formula for age in years and months

One of my readers was struggling with this formula. I have had a version for the "years" part posted on my formulas page, but I hadn't tried it for months.   The formula below will take a date of birth and any 'as of' date and calculate the age in years and months. The 'as of' date could be simply today's date (CurrentDate), or it could be a transaction or visit date from the database. Note that the formula only counts complete years and complete months. It adjusts for shorter months so that a child born on 12/31 would turn two months old on 2/28 (or 2/29 on leap years). I even added a few refinements like suppressing the years value when it is zero and adding the plural 's' only when the years or months are greater than 1.

You can copy it into any report but you should only need to modify lines 2 and 3. If you find a situation where it doesn't work, please let me know.

WhileReadingRecords;
DateVar Birth:= Date ({Your DOB Field}); //put your DOB here
DateVar Ann := Date ({Visit Date}); //put your 'as-of'date here

NumberVar y :=
(if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1);

NumberVar m :=
(if Day (Ann) >= Day (Birth)
or Month (Ann) <> Month (Ann+1)
then DateDiff ('m', Birth, Ann)
else DateDiff ('m', Birth, Ann) -1) ;

m:= Remainder (m,12);

(if y >0
then Totext (y,0,'') & ' yr' &
(if y>1 then 's' else '') &
' and '
else "")

& Totext (m,0,'') & ' mth' &
(if m>1 then 's' else '')

Date groups that don't do what you expect

I received this question from a customer and it isn't the first time, so I thought I would write about it. He asked me why:

1) The group tree was missing many dates where data existed in the report.
2) The group tree was including dates that were outside the range of the report.

The answer is pretty simple. In Crystal, whenever you group on a date field, the default setting for the new date groups is "for each week". Don't ask me why. You don't even see this while you are adding the group, unless you go into 'Group Options'.  Now I am sure that lots of reports need to group by week, but most reports I do group by day or by month. Nevertheless, CR has defaulted to weeks for a long time.  It is simple to change to another period, as long as you know to go into the "group options" for that group.

One reason this causes confusion is that (unlike a month grouping) weekly grouping looks like daily grouping with missing dates. The reason for missing dates is that you see only the Sunday's.

And because CR groups using calendar weeks, the group name will always be the Sunday of that week, even if it is outside the report date range.  This explains issue number 2, above. Say you run a report for April 2013 and group by the date field. Crystal defaults to grouping by calendar week and the first week of April will be labeled as 3/31/2013.  Even though that date doesn't exist in the report, that week does exist, so the label will be that Sunday.

So if you get a strange group tree, check your group options.  And, on a slightly related note, if you ever want to have a weekly group that runs Monday to Sunday or something other than calendar week, you read the solution I wrote up a few years ago.

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.

Saved data vanishes when you narrow the criteria

A user in Tek-Tips was puzzled. He was viewing reports with saved data and changing the parameters to filter the report in different ways. When the saved data was generated today, he could change the parameters and filter the data as expected. But if he tried to do this on an older report, the saved data would go away.  I have run into this before and so I knew the cause, a date-sensitive line in the filter, like:

{db.DateField} in LastFullMonth

When the report is refreshed, it uses today's date to calculate the beginning and ending of the prior month and filters the data to those dates. So a report run in April would only include March data.

But what if that report is saved with data and then reopened in May? It will have March saved data but the filter will now be looking for April data. As soon as you try to change another part of the selection formula, Crystal will apply the updated formula to the data, which includes the updated date rule. It will try to find April records when all it has are March records and zero records will qualify.

But there is a workaround. Just change this line of the selection formula:

{db.DateField} in LastFullMonth

to say this:

( If DataDate < CurrentDate – 5  
then true  
else {db.DateField} in LastFullMonth )

This checks to see how old the saved data is and if it is more than 5 days old it will ignore the date filter. And because the saved data has already had the correct date filter applied, the date rule is no longer needed. You can change the number 5 to whatever works for you.

One other note.  The Tek-Tips user was looking at the saved data through InfoView, which is a web based viewer built into Business Objects Enterprise.  But the same thing can happen in any viewer, even in the normal preview of Crystal Reports.

Copying text into Crystal Reports

I try to avoid copying text directly from a formatted document (Word, PDF, etc) directly into a Crystal Reports text object. While Crystal can accept many of the formatting characters from other applications, they can sometimes cause unpredictable behavior.

I recently had a customer show me a text object in Crystal that refused to display the first few characters of the text. We would see these character when the text object was in 'edit' mode, but they would disappear when we exited and went to preview. We couldn't find any visible formatting characters that were causing this behavior, but if we copied the text to a new text object the behavior followed. I finally solved it by copying the text to Notepad, and then copying from Notepad to a new text object. This stripped out all the hidden formatting.

So now whenever I have to transfer formatted text to a report, I first paste it into a simple text editor like Notepad. Then I will copy the text and paste that into Crystal. I can then add any needed formatting within Crystal Reports.

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.

SQL union vs cross join

I often have reports that require me to include multiple copies of the same records. Normally this means using a command object in Crystal, rather than going directly to the tables. And in most cases I write a UNION query which appends one set of records to itself multiple times. But last month I had a report that required 24 copies of the same 2-year dataset.

I started testing the performance and found that getting a single copy of the data took nearly 5 minutes, partly because the data was coming from a view. I then found that adding a UNION of a second copy of the data added another 4 minutes to the query, and each additional UNION added 4 more minutes. It would take nearly 2 hours to get 24 copies of the data, so I had to scrap the UNION idea.

Then I wondered if using a CROSS JOIN might be faster. A CROSS JOIN is when you add a table to a report, but you leave it unlinked. Normally this is a very bad thing to do but it is handy when you need duplicate data.

So I found an unrelated table that had a column of consecutive numbers and wrote a query that selected the numbers from 1 to 7. Then I added the SQL for that small query as a cross-join in my command. The result was 7 copies of every record in the dataset, retrieved in about 5 minutes. Even when I scaled it up to generate all 24 copies of the data, it only took about 7 minutes total. Obviously much better than 2 hours for the UNION.

Now maybe this would be obvious to someone who works primarily writing SQL, but the magnitude of the difference was a surprise to me. I will be using CROSS JOINS whenever possible, now.

Better way to change the row/column field of a cross-tab

I use cross-tabs quite a bit and there is one thing I find frustrating. It is when you have a perfectly laid out cross-tab and realize that the row or column field needs to be changed. Changing a row or column field tends to reset the formatting of the cross-tab cells and often changes their size as well.

Recently I was trying to change a cross-tab row from one formula to another formula I realized that I could avoid losing my formatting if I just copied the contents of each formula into the other - in essence reversing the formulas. Once I had reversed the contents, I also renamed the formulas so that the names matched the contents as before. The cross-tab didn't see either of these changes as a field change and so the formatting wasn't affected.

So, now whenever I create a complex cross-tab, I create dedicated formulas for the row and column fields. I usually name them Xtab Row 1, Xtab Row 2, etc. That way I can change the contents to any field or calculation,   without having to worry about the cross-tab losing any formatting.

This is similar to the technique I called 'feeder' formulas in a prior article.

Other recent blog articles:
    Using SQL commands in ABRA HR

Gems from the archives (May 2002):
    Using PeachTree UFL functions for Crystal Reports
    Adding a specific number of business days to a date
    Formula for finding the last Friday (or any day) of month
 

Contact Information: