Crystal Reports: Upto and Upfrom

More thoughts on Select Case

While nested if …then …else …’ statements work very well, they become confusing for most people on that third nesting. When you must nest more than that to achieve a particular goal, it's certainly doable. Many good engineers have nested quite deeply in Excel, for example, with the results they intended. Ditto for Crystal Reports. But try going back after a few months, and see if you can follow the logic and the nesting trail.

This is where nesting starts to seem like an exercise in sadism if it's someone else's code or masochism if it's your own.

While you may be perfectly happy whipping out multiple nestings to get something done, you may later rue the day you did that. The alternative is to take the time to learn Select Case and then use it appropriately.

Be careful to use the correct syntax in your Select Case statement. This is what defines the field against which the Case will compare the criteria.

Follow each criterion with a colon, as in the examples above, e.g., Case 1: and after the colon type in the desired results in quotes. For Case 1, that would be "low" as in the example above.

You actually have a lot of leeway with what the results can be. The results can even be a formula. Experiment with this and see what you can do.

Now, this brings us back to the original issue of multiple nestings. Why do you need such convoluted logic to get a result? Perhaps the result is so far removed from the data that:

• It's not reliable, because the data do not really support it.
• It's not meaningful, because it is so many steps from the data.
• It doesn't answer a business question, and is thus just more clutter for the manager to deal with.

Maybe the real solution here is to question the desired result. Why does the report recipient need to know that? Sure, it's your job to provide people with the information they need. But perhaps what they request and what they need are not the same thing.

To find out, carefully interview the requestor. If there's not a requestor, find out why this result was put on the To Do list and see if that is a real business intelligence need. When you research by asking people, don't ask, "Why do you need to know this?" because you'll just get a defensive answer. Instead, explain that to get the result requires a very convoluted manipulation of the data and that makes you question how useful the result is. Ask what the business goal is and explain that you're trying to find an accurate and fairly direct way of supporting it.

You may find your job is greatly simplified. But if not, then turn to Select Case to avoid mind-numbing nesting structures.