Contents for May 2012:
** Update to server-based scheduler comparison
** Custom Function lets your report send a 'tweet'
** My library of Crystal Reports materials
** New specialized tool to help you "Set Location"
** Do you have your reports backed up?
** Let me create your Crystal Reports
** New version of rePORTAL, and a 20% discount
** New features in Find it EZ, and a 25% discount
** Open enrollment Crystal classes in Frederick, MD
** Using formula fields and calculations in the selection formula
** Exporting to Excel when you have a subreport.
** Calculating the first weekday of the month

Update to server-based scheduler comparison

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.

Custom Function lets your report send a 'tweet'

Every fall I do a review of custom functions libraries that you can add into Crystal Reports. These are formula functions that are added into your formula editor and allow your formulas to do all sorts of wonderful things, like interacting with other programs and pulling in information from unusual sources.  It looks like I will be adding a new one in the near future.

Pursuit Technology, a consulting group in the UK, has recently released a function that allows your report to authenticate into your Twitter account and send a 'tweet' notification directly from a report formula.  The content of the tweet can be calculated in the report formula.  This can be handy if you use twitter as a messaging or notification system.

The product is called Crystal Tweet and can be used for free or purchased for about $65 (£40).  The free version adds a hash tag to all of your tweets and also can't be directed to specific account.

I don't do anything currently with Twitter, but if you have occasion to try it out, please drop me a line.

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.

New specialized tool to help you "Set Location"

The folks at R-Tag.com have just released a new tool called CR Data Source which is designed to do just one thing. It lets you perform a "Set Location" on a folder full of reports, all in one stroke. The tool does just this one task, but then again it only costs $29, so it will probably be a popular choice for certain users. If you look at my review of RPT management utilities you will only find one other tool that does this, and that tool does lots of other things and costs about $400.

The current limitations are:

    It only works with ODBC/OLEDB
    It can be used only on reports that have a single data source.

But even with these restrictions, the tool will still work on most reports.  And, if the tool catches on I am sure the folks at R-tag would be open to suggested improvements.  As always, if you try this tool please let me know what you thought.

Do you have your reports backed up?

Every few months one of my customers will ask me if I have copies of their reports.  Usually a hard drive crash or some other system failure has made some or all of their reports disappear.  Only then do they realize that they have no backups.  When I work on reports in my office I have copies to replace the lost files.  But I am doing more work via remote control now.  In those cases I work directly on the customer's workstation, so I don't have copies of the reports to use as a backup. So, do you have recent backups of your reports?

If you do, it is just as important that you do a pretend restore periodically.  That is the only way to confirm that your backups are any good.  I remember one customer who used a tape drive for backups.  He had followed an elaborate rotation schedule for the tapes and dutifully swapped the tapes out every week.  But when I checked one of the tapes I discovered that the  backup program had stopped running several months back.  No one realized that the tapes hadn't been updated in months.  Another client was told that her IT department was managing her backups, but it turns out they only backed-up the shared network drives.  Only after her local hard drive failed did she realize that she had no backup of her local files.

And to help you remember, March 31st has been designated as World Backup Day (so you won't be an April Fool).  The official site has all sorts of info on backing up your data. Do us both a favor and backup your reports regularly.

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.

New version of rePORTAL, and a 20% discount

rePORTAL Software has just released v5 of rePORTAL, and they are offering a limited time 20% discount through June 15.

rePORTAL is a a web portal and scheduler that distributes reports from Crystal Reports as well as SSRS and FRx.  You can read about this tool and other similar tools in my comparison of server-based schedulers. The new features include:
 
    Native support for 64 bit operating systems
    Support output to Fax and SMS
    Enhanced support for Crystal export options
    Launching batch and command files before or after scheduled events
    A customizable user interface to highlight favorite items

New features in Find it EZ, and a 25% discount

If you have been considering purchasing Find it EZ then you might want to do it in the next few weeks. The developers of Find it EZ are offering my readers a 25% discount on purchases through 6/20/2012. Use this link to get the discount.

Find it EZ is a source code scanning tool that I have included in my comparison of RPT management utilities. It allows you to search your entire application, including your RPT files, for the use of specific tables, fields and text. There are specialized searches for RPT files that will specific values in 'saved data' and even for specific phrases in the SQL that CR generates to  send to the database.

The current promotion is to highlight the release of version 4.0 which adds new features like team collaboration and the ability to save, annotate and search within results.  Their future update schedule includes:

    Searching repositories in Business Objects Enterprise/Crystal Enterprise
    Searching the contents of RPT files within a repository
    The launch of a cloud edition to search live SQL Server or Azure databases

I will let you know when these updates are completed.

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

Using formula fields and calculations in the selection formula

I recently needed a selection formula that automatically selected the last "half month".   In other words if the report was run in the first half of a month it should return the last half of the previous month.  If the report was run in the last half of a month then the report should return the first half of that same month.  So I wrote the following selection formula:

if Day (CurrentDate) > 15
then ( day ({charges.date}) <=15 and {charges.date} in MonthToDate)
else (day ({charges.date}) >15 and {charges.date} in LastFullMonth)

This worked correctly, but I knew that it would not be very fast, because it would not pass the date criteria to the database (SQL Query).  The database would have to send back ALL of the dates, and CR would have to apply the date rule locally.  This is because CR won't translate the DAY() function, and several other functions, into SQL.  As a short-term fix I added a second criteria.  First I turned the formula above into a formula field called {@DateCriteria} and then I changed my selection formula to say:

{charges.date} > CurrentDate - 60
AND {@DateCriteria}

The first line above does what I call a 'rough cut'.  It lets the database return a small number of records, but still ensures that the report gets all of the records that it needs.  Then the report can use the local formula to eliminate any unneeded records that were included in the rough cut.  It is logically redundant but in some cases it is the most efficient approach.  

But in this case I found an even better solution.  I wanted to display the report's date range in the page header, so I created two formulas for StartDate and EndDate:

//@startdate
if Day (CurrentDate) > 15
then minimum (MonthToDate)
else minimum (LastFullMonth) + 15

//@enddate
if Day (CurrentDate) > 15
then minimum (MonthToDate) + 14
else minimum (MonthToDate) -1

These calculate the dates for the beginning and ending of the desired range.   Once I had finished them I realized that because these two formulas do not require any data from the database, so they can be calculated before the selection formula happens (BeforeReadingRecords).  That meant that I could use them in my selection formula and the values would get sent to the database just as if I had hard coded them.  So I simplified my selection formula to:

{charges.date} in {@Start Date} to {@End Date}

Checking the SQL I could see that the those dates were being sent to the WHERE clause.

So if find that a calculation in your selection formula is slowing thing down, see if part of that calculation can be done without the database.  You can still use parameter values, or today's date and time or similar things.  You may find that you can calculate the literal values outside the selection formula, and then use the results in the criteria to make your query run faster.

And if you can't do that, then you might also try adding a redundant rule to the selection formula to do a rough cut.

If you need help implementing one of these techniques give me a call.

Exporting to Excel when you have a subreport.

Exporting Crystal Reports to Excel can be tricky. I have written several articles on techniques to make it easier. But it gets even trickier when a subreport is involved.

Without a subreport I have found that you can get a clean export by using the "Data Only" option. But this doesn't work with a subreport. I tried two different methods with the "Data Only" export that would not work.

1) Line up the subreport and leave it visible. This adds extra rows to the export and moves the subreport values down one row.

2) Use a 'stealth' subreport which runs invisibly in the background, pass the values to formulas in the main report using shared variables. But this also adds extra rows to the export.

The only method I found that worked was to use the regular Excel Export instead of "Data Only".  But you have to line up everything very carefully, including main report fields, the subreport, and the objects within the subreport) so that they are in line with the main report objects.  Line them up carefully and they will export to excel with subreport objects aligned correctly.

It is pretty finicky, but it is the only method I could get to work.

Calculating the first weekday of the month

One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say "If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:

If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

This worked fine for several months until we got to April.  Because the report only runs on weekdays, and because April first was a Sunday, the March report didn't get run.  Nothing ran on Sunday, and the report that ran on Monday April 2 was for April.  So we needed to tweak the logic and calculate the first weekday of the month.  So I wrote a formula called "FirstWeekDay" that looked like this:

If DayOfWeek (Minimum (MonthToDate)) = 7 then 3 else
If DayOfWeek (Minimum (MonthToDate)) = 1 then 2 else 1

It returns a number from 1 to 3 which represents the first weekday of the current month.  Then I replaced the '1′ in the selection formula with my new formula field, like this:

If Day (currentDate) = {@FirstWeekDay}
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

Contact Information: