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, “it’s 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 some situations where Continue Reading »
Table-based reports vs custom written SQL

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

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.

I found an interesting behavior the other day.  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 Continue Reading »
Strange behavior with IIF

I got a really strange call yesterday. Someone called my office asking to speak to Crystal Coral. When I told them that there wasn’t a person by that name they asked if they had reached Ken Hamady, LLC. When I said,  “yes” they insisted that there was a person by that name who worked here, and that Crystal was the ‘Report Manager’.  When I asked them to spell the last name I realized they were talking about Crystal Corral. I explained to them that this was the name of 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.

But today I tried to find the web page that might be confusing people.  I did a web search and was surprised to find a site called Jigsaw hosting a business card for Crystal Corral.  She apparently works for me as my “Report Manager” and her address and phone number match my business. I think someone’s data scraper software might need some tuning.

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

Well at least they are telling the truth. This contact is certainly not found anywhere else.

[update 12/19/2012 – apparently they have removed Crystal’s business card from the site, but it still shows up in a Google search. ]

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 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: Continue Reading »
A better way to suppress sections