Contents for January 2011:
** Crystal Reports 2011 (coming soon)
** Take my new Crystal Reports test
** Open enrollment Crystal classes in Frederick, MD
** Export to a PDF that allows for drill-down
** Server based deployment Options compared (2011)
** Let me create your Crystal Reports
** Using Approximate Joins
** Does IsNull() let you filter a Left Outer Join?
** My library of Crystal Reports materials

Other recent blog articles:
    Sample "Time Master" Report from SAP
    Missing export formats in an application
    Grey background when printing an image
 

Crystal Reports 2011 Coming soon

Crystal Reports 2011 just finished beta and is coming out within the next 6 months (Q2).  Don't expect dramatic changes.   You can read about the new features in this PDF.  There is also a slightly different edition of CR 2011 for use within Business Objects Enterprise (see this video).  

The standalone edition has the following new features:
1) Read-only RPTs that can only be opened in the CR viewer
2) New XLSX Excel export format

The enterprise edition talks about:
1) A new look and feel (more consistent with other SAP and BO tools)
2) Tighter integration with universes and business views
3) A translation manager that allows you to store multiple versions of report labels, each in a different language.

One feature in the Enterprise edition that got my interest was the "Smart Guidelines".  These allow you to insert, remove or adjust one column and have the other columns adjust automatically to make room or fill in the gap.  To see this feature in action watch the video linked above and skip to the 4:00 mark.  Hopefully this is included in the standalone version.

Another interesting feature is the "Read Only" RPT file.  This is something that has been available for years in Data Link Viewer. It allows users to run reports without having the ability to open the RPT files in the designer.  This protects proprietary calculations and also prevents unauthorized changes.  The main problem withe SAP's version is that it can only be opened in their viewer, which can't refresh data for most users.  Data Link Viewer's read-only report format (RPZ) can be opened and refreshed within DLV.

Go to my blog post for more links to discussions of new features.
 

Take my new Crystal Reports test

I have developed two new Crystal Reports tests (Intro and Advanced) and I am looking for users at both levels to help me calibrate the questions. If you want to take one or both tests you can download the test files and answer sheet here. 

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

Export to a PDF that allows for drill-down

Wouldn't it be great if you could export your reports to PDF format and still have drill-down functionality? I just learned that you can.  Ido Millet of Millet software recently sent me a Sample PDF that was generated using Visual Cut and Crystal Reports. This PDF is a one-page summary that allows you to drill down on any one of 8 product categories to see the products within that category. Then you can drill down again on a single product to see the details for that product.  I was impressed by the fact that this was all contained within one PDF.  If you own or are evaluating Visual Cut you can request a zip file that has the PDF and source files or you can watch a 5-minute video that gives an overview of the process.

So how does it work? Inside this single PDF file there is one main PDF document and many other embedded PDF documents. All of the embedded PDFs were at one point separate PDF files. Visual Cut can automatically generate all these individual PDFs and then automatically incorporated them into the master PDF file. Visual Cut also creates links in a specified clickable spot in the main PDF and these links point to the appropriate 'sub' PDFs.

There are more details on the process for creating this PDF in my Blog post. If you would like some help implementing this in one of your projects, give me a call.

Server based deployment Options compared 2011

There are many ways to deploy Crystal Reports to users.  I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output.  But there are environments where a server based option is necessary.  The 'official' options from SAP are Crystal Reports Server and BO Enterprise.  You can read about these on the SAP website under the heading "SAP Business Objects portfolio".  But there are other, less expensive products out there that many users never see. These are third party products that allow you to centrally manage your report deployment from your server.  

I first wrote about these tools last January and have just updated the list with two new tools. The list now includes:

Ripplestone – a traditional Web portal
rePORTAL - a traditional Web portal
Report Runner Web Portal - a traditional Web portal
RV for Windows Pro - a server-based viewer
ReCrystallize Pro – a Web launch page generator.
Report Launch – a bridge between BO server products and server based applications.

The products vary widely and are aimed at several different markets. My blog article contains a brief rundown on what the products do and links to all of the tools. I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices.  If you have recently tried one of these products, or are currently using one, I would love to ask you a few questions.
 

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.

Using Approximate Joins

Most of the time when you drag a link between two tables you can leave the default join options alone (inner join/equal comparison).  But in class I am asked why there are comparisons for things like Greater Than or Less Than and when they would be used.  The primary answer is "not often".  But there is one scenario worth mentioning that requires the use of these approximate  joins.  It has to do with records that have a low and high value and where the match is between them. One examples would be matching an insurance claim with the correct policy period based on the effective and expiration dates. Or, having finding the appropriate discount percentage based on the volume of a sale.  Lets take this last one as an example.

Say you want to offer discounts based on the quantity ordered.  We might want no discount for less than 5, a 2% discount between 5 and 15, a 5% discount between 16 and 50, and a 10% discount for anything above 50 items.   Your discount table might be set up like this:

Low – High – Discount
0         4            .00
5        15           .02
16      50           .05
51      999         .10

With a normal "equal" join you would have to create a separate record for every possible quantity.  This would be tiresome even with a simple integer field like a quantity.  It would be practically impossible if the low and high fields are based on dollar amounts or DateTime stamps.   But with an approximate join the example above would be fairly simple.  Here are the steps.

1) Open the linking expert

2) Drag the Quantity field from the Orders table to the Discount table and drop it onto the "Low" field.  A connecting line appears. Change the join comparison to be "Greater or Equal"

3) Drag the Quantity field from the Orders table to the Discount table and drop it onto the "High" field. A second connecting line appears.  Change the join comparison for the second line to be "Less or Equal".

Now if you look at the SQL you will see that the query defines matches as a Quantity being >= to the low value and <= the high value.  In many cases you could stop here.

But what if different product categories had their own discount ranges.  Now you would need another column in the discount matrix for product category.  You would add a third connecting line in the visual linking expert that would go from the product category in orders to the product category in the discount table. This comparison would be set to Equal. Note that all three lines have a different comparison. If every product category in the Orders table has a match in the discount table you can stop here.

But what if there might be product categories that are not represented in the discount table?  You would need to change the join type to be LEFT OUTER, making the discount table optional. You will find that the join type only has to be changed in one of the three connector lines.  Unlike the comparison, changing one connector line from INNER to LEFT OUTER will affect all the others.  

For more information on joins, and how Crystal interacts with SQL, you should check out:

The Expert's Guide to SQL Expressions, Options and Commands

Does IsNull() let you filter a Left Outer Join?

Here is the summary version of this article:  No.

But twice in the past 2 months I have read people giving this advice to users online.  Before I give the detailed explanation let me provide some background for those who are less familiar with SQL:

When you link two tables in Crystal, the default join between the tables is called an INNER JOIN.  This will only return the records that are included in BOTH tables.  So if you link the Customer table to the Order table you will only get customers who have orders (and orders that have customers).  A customer with no orders would not show in a report using an INNER JOIN.

IF you want to include all customers, even those without orders, you have to use an OUTER JOIN.  Normally this would be a LEFT OUTER JOIN from the Customer table to the Order table.  This makes the Order table optional and now all customers show up at least once – even those without any orders.  The customer with no orders will have only one record and it will have NULL values for all the Order columns, like the order date.

The confusion starts when you try to filter the Order table, like adding a date range.  As soon as you add criteria to any field from the 'optional' table (in this case the Order table) your join will start to behave like an INNER JOIN.  This is because a record with a Null date can't be included in any date range.  So, if you take the LEFT OUTER JOIN report described above, and add a rule that says the order date has to be in October 2010, then you are going to lose two groups of records from this report:

1) All the records with the NULL dates (no orders ever)

2) All the records that have dates, but that aren't in October 2010

So this criteria will cause the report to drop customers who had no orders at all – even with the OUTER JOIN.  It will also drop some customers who do have orders – if none of those orders are in October 2010.  For example a customer with one order in September 2010 is not going to show up in the report any more.

So how do you keep all customers in the report and still only show activity for the target time period?  There are a couple of advanced solutions that work (see below) but there is also the simple but risky suggestion I heard recently.  It involves changing your selection formula from this:

{Services.DATE} in Date (2010,10,1) to (2010,10,31)

to this:

IsNull ({Services.DATE}) or {Services.DATE} in Date (2010,10,1) to (2010,10,31)

The thinking behind this is that a customer that doesn't have October dates will therefore be a Null value and so he will be picked up by the first rule.  The problem is that this is only true some of the time.  If you go back to what I described above, there are two groups of customers that drop off when you add a date range.  The IsNull() only solves the problem for group one - customers with no orders at all.  It does not solve the problem for group two - customers who have orders in other months but none in the target month.   A customer who has one order in September will not have any records with a Null date, so his record doesn't meet either criteria.  You lose that record and therefore you lose that customer.

So what other methods can you use?  I have used three methods in different scenarios.  The first is the best but involves some SQL knowledge.  The other two are only workable in smaller results because they can really slow things down:

1) Use a SQL Command and put the date criteria into the FROM clause rather than the WHERE clause

2) Create the complete customer list in the main report, and create a linked subreport to find the orders for each customer

3) Leave all the orders in the report and suppress the details on orders that you don't want to see, rather than using a filter.

If you would like some help applying one of these methods to a report in your environment, give me call.

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.

Other recent blog articles:

    Student Edition of Crystal for $100?
    Sample "Time Master" Report from SAP
    Missing export formats in an application
    Grey background when printing an image 

Contact Information: