Contents for January 2013:

** Crystal Reports Server-based deployment options (2013)
** A better way to suppress sections
** My library of Crystal Reports materials
** Table-based reports vs custom written SQL
** Days between (excluding Sundays)
** Let me create your Crystal Reports
** Strange behavior with IIF
** Mixing Number and Currency Values
** Open enrollment Crystal classes in Frederick, MD
** Comparing Pentaho to JasperSoft
** My new employee, "Crystal" (?)

Crystal Reports server-based deployment options (2013)

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

There is a page on my blog that lists and compares these products, and I update it every January. This year the list includes the seven products from last year and one new product that was just released:

Report Runner Web Portal – a traditional Web portal
Visual Access Report Server – a traditional Web portal
Ripplestone– a traditional Web portal
RVweb – a traditional Web portal
rePORTAL – 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 features vary and they are aimed at different types of users. The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices. If you have any feedback to share on these tools I would be happy to hear form you.

A better way to suppress sections

Crystal allows you to write a formula to conditionally suppress a section. Recently I have found that there are several advantages to putting the suppress logic in a separate formula field instead of directly into the condition formula.  I then use that formula field inside my suppress condition. I find that this method makes it:

1) Easier to write
I find it easier to think in terms of what I want to see rather than what I want to suppress, especially when the logic gets complex.  So I write a Boolean formula called "Show Details" that will display TRUE on records that should be visible and FALSE on the records I want to suppress.  My section suppress condition is then:

    not {@show details}

2) Easier to test.
If you put the logic into the suppress condition formula, testing to see if it works can be a challenge.  If there is a typo that causes it to suppress too many records, it may not be obvious right away (you won't notice something that is suppressed). But, if you put the criteria into a formula field you put the formula directly onto the section without suppressing anything. The formula will print TRUE and FALSE values and you can review them to see if the values are on the correct records.

3) Easier to change
If you want to change a suppress condition you have to open the section expert, highlight the specific section and click the suppress condition button. Then when you are done you have to save the formula, and then click 'OK' to exit the Section Expert. But if the logic is in a formula field you simply right-click on the formula and select 'Edit'.  When you save the formula you are done. It might be just a few extra clicks, but when you are testing you may have to go into that formula a dozen times.  

This is especially important if you plan to use the same logic to control several different sections. You can write one formula that has the logic and refer to it in multiple suppress conditions. Any changes can then be done in one place.


4) Easier to re-use.
Some times other calculations need to take into account which records are suppressed. By creating a "Show Details" field you can incorporate that logic into other formulas like this:

If {@Amount} > 100
and {@Show Details}
then ........

When you change the logic in the 'Show Details' formula, all the other formulas will respond accordingly.

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.

Table-based reports vs custom written SQL

Two different customers contacted me recently with similar questions.

The first customer had a subreport that connected directly to the tables. She thought she could speed it up by changing the subreport to use a SQL command instead of tables.  But the command made the subreport take even longer.

The second customer asked me why her ERP vendor had created all of the reports using tables instead of using views and stored procedures. She quoted her consultant who said using tables was like, "sending the whole mine to Crystal instead of a wheelbarrow."

But I never assume that handwritten SQL (view, stored procedure or command object) will improve the performance of a report. There are instances writing your own SQL can dramatically change the performance.  There are even instances where requirements can only be met by writing the query directly in SQL. But in my experience these are in the minority. In most cases I have found that the performance of a table-based report will be comparable to the performance of other methods. This is because Crystal Reports can generate workable SQL based on your linked tables and selection criteria. To improve the performance you would have to write a better query than the CR engine.  There isn't always enough improvement available to make this worthwhile.

Take the subreport example above. The subreport using a command should have had roughly the same performance as the original table-based report. The reason this customer saw such slow performance when using the command was because the subreport parameters went into the selection formula instead of directly into the command's WHERE clause. The command couldn't apply the key criteria and had to "send the whole mine" back to Crystal. We could have fixed the command, but all that effort would have put the performance level back where we were with tables, so we decided to go back to the original subreport.

Also, what that consultant describes really can happen – but it isn't directly related to tables vs SQL. Instead, I usually find that this problem occurs when the selection formula has functions applied to database fields.  Functions can prevent the criteria from passing to the SQL. You can often fix this by changing your selection formula so that the functions are used with the literal values rather than the database field. In many cases it only takes a few tweaks to make a table based report behave like a report using custom SQL, without losing the flexibility of using the tables directly.

So, if you have a report that is taking to long, or if you want to know how to optimize your report using tables or SQL, give me a call.

Days between (excluding Sundays)

I recently needed a formula to calculate the number of days in a date range, while excluding all of the Sundays in that range. My plan was to calculate all the dates in the range, then calculate the number of Sundays in the same range, then calculate the difference. To get all the dates I would use:

    {@EndDate} - {@StartDate}

Depending on your environment, you may need to add one day to the result. In an environment that involves overnight stays (hospitals, hotels, etc) anyone who checks in one day and checks out the next day would only be counted as one day, so the formula above would be correct. But if you are talking about most other things that start on one day and end on another day (attending a class, dates of coverage, etc) you will have to add one day to the result like this:

{@EndDate} - {@StartDate} + 1

Then, to calculate the number of Sundays to exclude (or any day of the week to exclude) you can use one of these examples:

DateDiff ("ww",{@StartDate}-1, {@EndDate}, 1)

DateDiff ("ww",{@StartDate}-1, {@EndDate}, CrSunday)

The number '1' at the end of the first example is the same as saying "CrSunday".  You can use other numbers or any day of the week.

But notice the "-1″ in the middle of the formula. I have found that if the Start Date happens to fall on a Sunday it will not counted. The End Date isn't affected for some reason. So if you had an 8 day period starting on a Sunday, and used the formula above it would find 2.  But if you took out the "-1" it would only find one. So if you want it to count both the beginning and ending Sunday you need to subtract one from the Start Date.

So the final formula in most environments would be:

        {@EndDate} - {@StartDate} + 1
        - DateDiff ("ww",{@StartDate}-1, {@EndDate}, CrSunday)

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.

Strange behavior with IIF

A customer had written a formula that was hitting null values and failing.  Normally I would do something like this:

If  IsNull ({FieldA}) then {FieldB} else {FieldA}

But this customer had written it using IIF like this:

IIF (IsNull ({FieldA}) ,{FieldB} , {FieldA})

I expected it to do the same thing and was surprised to find that IIF doesn't work the same as IF THEN.  With some testing I found that it works fine with literal values like this

IIF (IsNull ({FieldA}) ,"X" , "Y" )

Just not when you use fields as the 'then' and 'else' values. Of course if you are using a more recent version of CR you can flip the switch at the top of the formula editor from "Exception for Nulls" to "Default value for Nulls".  The other advantage of this is you can now change the formula to say:

IIF ({FieldA} = "" , {FieldB} , {FieldA})

This will cover both Null values and empty strings. And if you want to also include spaces you can use:

IIF ({FieldA} < "0" , {FieldB} , {FieldA})

Combine this with "Default values for Nulls" and you are catching Null, blank and any number of spaces, all at the same time.

But my preference is still to use IF THEN ELSE. I find it much easier to read, especially when I have to nest or chain several sets together.

Mixing number and currency values

A user was getting an error in a Crystal formula and asked me to take a look. The formula looked like this:

    {FieldA} / {FieldB} * 100

She was dividing one column by another to get a percentage and Crystal responded with "a number is required here" – pointing {FieldB}. I checked the data types of the fields, and found that {FieldA} was a numeric but that {FieldB} was a currency field. Apparently you can't divide a numeric by a Currency. That was news to me so I did some testing and learned a few things:

1) You can divide a currency value by a numeric value or by another currency value with no error.
2) You can use % instead of / and the data types don't matter
3) You can convert a currency value to a numeric value by using the function ToNumber() but NOT by using the function Val().  The two functions are normally roughly equivalent.

So there are two options that will work to accomplish our requirement:

    {FieldA} / ToNumber ({FieldB}) * 100

or the much simpler:

    {FieldA} % {FieldB}

In this last option the percent sign automatically moves the decimal two places so there is no need to multiply the result by 100.

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

Comparing Pentaho to JasperSoft

Somewhere around 1996, the Crystal Reports part of my business took over from the tools I was using before. Ever since then I have been looking out for the next tool to take the lead. So I like to stay exposed to the alternatives to the Crystal Reports product line.  With that in mind I found an interesting article comparing Pentaho and JasperSoft as alternatives to the Business Objects and Oracle product lines. These are not so much competitors to stand-alone Crystal Reports, but are more comparable to the server level tools like CR Server or BO Enterprise.  Both Pentaho and JasperSoft  offer a free 'open source'  version as well as a commercial version, which makes them appealing to small businesses.   Of course the cost of the software is only part of the equation and you have to factor in the time it takes to install and configure these tools, and the overhead to maintain them.  I find that very few of my customers need  'full stack' solutions like these, but I did find it an interesting read.

My new employee, "Crystal"

Someone called my office last month asking to speak to Crystal Coral. When I told them that there wasn't a person by that name they insisted  that Crystal was the 'Report Manager' at Ken Hamady, LLC.  When I asked them to spell the last name I realized they were talking about Crystal Corral. I explained that this was a software package listed on my site, not the name of a person.  We both had a good laugh and he went on to his next cold call.

Just for fun, I did a web search and was surprised to find a site called Jigsaw hosting a business card for "Crystal Corral".  Her title is "Report Manager" at Ken Hamady, LLC and her address and phone number match my business.  Below the card there is some marketing text to explain why you should sign up for Jigsaw:

"… Jigsaw has the business contacts found nowhere else."

I cant' argue with that. 

Contact Information: