Contents for March 2011:  

    Update to my comparison of desktop-based schedulers
    Crystal Reports suggestion box
    My library of Crystal Reports materials
    Drawing tables in a Crystal Report
    Generating a UNION from the Linking window
    Let me create your Crystal Reports
    Formatting chart dates
    Number formats in the legend of a pie chart
    Open enrollment Crystal classes in Frederick, MD

Other recent blog articles:
    Row Level Security in CR Server
    Dynamic parameters stop working with CR 2008 SP3?

Update to my comparison of desktop-based schedulers

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn't provide a way to do this (unless you upgrade to CR Server or BO Enterprise).  But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more.

I first reviewed these products last March and figured that after a year it was time to revisit them.  There is one new product just completing beta testing and one free product that is looking for someone to take over the project.  Altogether, the updated page reviews eight desktop-based scheduling tools.  It provides you with their core features and prices so that you can narrow down your search.  I provide a brief introduction to each product and describe what sets that product apart.  I have also created a detailed feature matrix that shows some of the specifics for comparison, including prices and the install base of each product. This year I have also written a feature glossary to help explain what each feature means.  There are also links to the vendor websites so that you can get more information on any specific product, if needed.

Next month I will be updating a separate article covering server based scheduling tools.  If you think one person can manage all the scheduling you are probably fine with one of these desktop tools, regardless of the number of people receiving the scheduled output.  But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

Crystal Reports suggestion box

I recently learned that SAP has been testing a site for submitting, discussing and rating proposed product enhancements. It is called the Idea Place and it is a great idea.   Apparently this has been up since October but no one thought to tell me about it so that I could share it with you all.  (As Curly would say, "Da noive!")  So I spent a few hours making up for lost time and imparting my wisdom.

Feel free to join in the fun.  Anyone can read the posts but you have to register to share your opinions. It is still in Beta so it is a bit clunky to navigate and slow, but I expect that to improve in time.  The link above is for Crystal Reports but there are forums for all SAP products.

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 Techniques v1-v4  ($19 each)
    Expert's Guide to Cross-Tabs ($22)
    Expert's Guide to Totals ($24)  
    Expert's Guide to SQL Expressions, Options and Commands ($26)   
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to Formulas  ($36)
    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.

Drawing tables in a Crystal Report

One of my readers, David White, is a Crystal Developer in the UK.  Apparently his reports often include data presented in tables of figures with the numbers all separated by horizontal and vertical lines.  He was frustrated by the fact that Crystal Reports doesn't have a table-drawing object to draw these lines automatically.  And drawing a table using lines and boxes can be a challenge, especially if you want the lines equally spaced and the edges to meet precisely.  But over time he has developed a method that helps him generate tables that look great and don't involve premature hair loss.  He has written it up as an article, complete with screen shots, and generously allowed me to post it for my readers on my Library page.   The article is entitled Quest for the Perfect Table.

Note that this probably works best for tables that are contained within one section (like a report footer) and will therefore print on one page.  Continuous tables that have to split over multiple pages might need to use a different method.

Generating a UNION from the Linking window

A few years ago I wrote a post about Linking vs Appending tables.  I explained that CR doesn't really provide a simple way to append the rows of one table to the rows of another. Normally it requires writing some SQL in a command object. But I recently heard from Gordon Portanier of Crystalize in Canada. He had told this same thing to a pair of advanced students.  They promptly showed him (and now me) how to accomplish a UNION of two tables without having to use a Command object or write SQL. It involves using an intentionally bad link and setting CR to use a FULL OUTER JOIN. Here is the scenario.

Say you have a table of charges and a table of payments and you want to combine them into one table. But you don't want to use a normal link because some charges can be paid in multiple payments and some payments can apply to multiple charges. I would normally append the charges to the payments using a UNION.

But using this new method you would find a field in each table that is always filled in, but they must never have a matching value in the other table. For instance we could use the check number from payments and the charge code from charges. If you link these tables together, on these fields, using the default INNER JOIN then there will be no data returned.  This is because there are no matches between the check number and the charge code. But if you change the join to a FULL OUTER JOIN you will get all of the unmatched records on both sides. And because every record is unmatched you will get all the records from both sides.

One consideration before using this method is that not all databases support FULL OUTER JOINS. SQL Server, MySQL And Oracle do, but MS Access does not. Another consideration is that a UNION (when done correctly) will align the corresponding fields into one column.  To accomplish the same thing using the FULL OUTER JOIN method you will have to write a formula for each column. For instance to get a single date column you would need a formula like this:

//@date
If IsNull ({Payment.Date}) the {Charge.Date} else {Payment.Date}
You can then use this formula to sort the records from both tables into one chronological list.

But probably the most important consideration will be performance - especially on large data sets.  It is tricky to filter a table that is considered the OUTER table, and both of the tables in a FULL OUTER JOIN are OUTER.  That might force you to filter the data within the report, something that can have a dramatic effect on the time it takes for the database to return records.

So with these considerations in mind it would be preferred in most cases to use a true UNION.  But who knows when this alternative method might come in handy.

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

Crystal Reports makes it very easy to change the format of date fields and number fields.  So it is frustrating that we don't have direct formatting abilities inside charts.  If you make a bar chart that has one bar for each month you find that there is no way of directly setting the format for the date values that appear under each bar.

So I decided to take a slow period this week and figure out the options we have here.  I created a report with a rolling, "last 12 month" bar chart.  Then I added a  requirement that the bars should be labeled:

"Feb-10″, "Mar-10″, etc.

Here is what I found:

1) One thing that does NOT work is changing the default format in File > Options.  Dates in charts do not respond to this setting.

2) My second idea was to write a formula that returns the date in the string format shown above and then use that as my 'on change of' field in the chart.  The formula would look like this:

Totext ({Orders.Order Date}, 'MMM-yy')

The formula works fine, but since it has the month name first the monthly bars won't be chronological.  You could try using "specified order"  but this is not practical with rolling months, since new months are added every month.

3) A workable solution for Group Charts:
A group chart is where you group the report itself by month and take the bars directly from the report's groups and subtotals.  If your chart Is a group chart then the formula above will help.  But instead of grouping the report on this formula you group on your original date field with a break for each month.  Then you go into the options for that group, and on the "Options" tab you check "Customize Group Name Field".  Use the lower "formula" option and click the X-2 button on the right to go into the formula editor.

Here you can either write the formula above, or if you have already written it as a formula field, you simply double click that formula field and save it as the formula here.  The chart will now display the correct format without changing the chronological order.

4) Non-Group Charts:
In some cases you can't use a Group Chart, because the report needs to be grouped on another field.  You can still use the method described in item 3 by adding a hidden cross-tab to the report.  Add this cross-tab in a new subsection and use your original date field as the row or column field.  Set the cross-tab options for this field to break for each month.  And because cross-tab row and column fields give you the same option to customize the group name, you can follow the same steps as above.  Add your summary field to the Cross-tab and click OK.

Then, once the Cross-tab shows the correct 12 values, you can create a chart that is based on the Cross-tab. The chart expert has a cross-tab option on the "Data" step.  The order of the bars will come from the date field but the labels for the bars will come from the customized group name.  Once the chart is working you can suppress the cross-tab, but don't delete the cross-tab or the chart will vanish with it.

And, if you have never worked with Cross-tabs you should check out my Expert's Guide to Cross-tabs on my LIBRARY page.

Number formats in the legend of a pie chart

In a recent class I had the students create a pie chart.  I had them create a legend that showed both the number of units and the  percentage of the total.  For some reason one student's legend showed dollar signs next to the number of units.  I tried to show the students that this could be easily changed, and was a bit chagrined that I couldn't find a way to change the legend's numeric format.  So we all started looking and after 10 minutes one of the students had stumbled on the solution.  The challenge was because the legend format is tied to the "show values" format, even when the "show values" option is not active.  So to change the format of the legend value we had to:

1) Go into the Chart Expert and go to the Options tab.
2) Check the "show value" option.
3) Select the appropriate format.
4) Click OK to preview the chart.
5) Go back into the Chart Expert and go to the Options tab.
6) Uncheck the "show value" option.
7) Click OK to preview the chart.

The change will still apply to the legend even though the "show values" option is no longer active.

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 April 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:

    Row Level Security in CR Server
    Dynamic parameters stop working with CR 2008 SP3? 

Contact Information: