Crystal Reports: Split a String

Room No. from "4" to "6".

Room No. from "56" to "Store"

The request was to extract the values from between the quotes. After several approaches the simplest

solution was to use this formula to extract the first value.

split({@yourfield},'"')[2]

And the second value was extracted with

split({@yourfield},'"')[4]

The way this works is to split the field into a string array using the quote delimiter. Then the first member of the array is the value prior to the first quote, and can be ignored. The first formula then returns the second value in the array.

The third value in the array is the value between the two quoted values and the fourth value in the array is the second quoted value and is returned by the second formula.

Thanks CoSpringsGuy for an interesting solution to a complicated problem.

An even better solution to the long string problem is to rethink the report. If your report is displaying long strings, then probably your report design focus has been on presenting data rather than meeting the information needs of the report user.

Unfortunately, most people don't really read anymore. If they see a long data string, they'll visually scan past it. It's just meaningless clutter on the page, for them.

You may think your highly-paid executives are mulling over critical business information, carefully digesting what they read so they can make solid business decisions. But that is seldom the case in any organization. Executives lead fragmented work days, and the higher up the chain they are the more this tends to be true. It does work against their effectiveness, but that issue doesn't cause them to change their behavior. They react, instead of respond, to the many demands placed on them.

You can help these executives by crystallizing the information they need. Crystal Reports isn't meant to just reformat data, but to crystallize information. Make that fact your driving principle behind your report design.

So rather than toss that long string out there, ask "What does that long string represent?" It's the representation you want the report to convey to the reader. Not the actual data.

Some research tips:

• Learn more about your company's products and customers. Take a sales rep to lunch and just talk. This will give you keen insights that you won't get any other way. You'll be more in tune with the needs of the report users, making you more valuable to the company as you use what you've learned.
   
• Arrange tours. Ask a department head or work group leader to give you a 30-minute infotour of what they do. Yes, this is a big chunk of time but people love to be valued for what they do. Your showing this interest will help you build a valuable contact in the company if you choose the right department. Make sure you are clear upfront that you want to understand "the process" and make sure it's a process related to something addressed by the reports you are designing.
  
  For example, one report is about accounts payable status. So ask the supervisor over accounts payable to give you a walk-through tour of how AP does what it does. Don't just drop by and expect the supervisor to drop everything to give you an ad hoc tour. Drop by and explain what you want, then say you'll follow up with an e-mail and sample report to schedule a time to do this. The value to the AP supervisor, beyond being appreciated, is better reporting related to the AP function.
   
• Read everything on your company's Website. It's astounding how few employees do this. You can be well informed, just be reading that information. You may even think of things that you can contribute, thus making you a more valuable member of the team.
   
• Look at the Websites of your competitors. This will make you even more well-informed, and you'll have an industry perspective matched by few people in your company or in your industry. That can come in handy if you decide to look for opportunities outside your company, too.
   
• Bump into key people "accidentally" on purpose. Ask them for their feedback on the reports. No matter what they say, express your gratitude and ask something like, "Can you give me five minutes to show me from an actual report?" In addition to getting the unvarnished truth (or, in some cases, an idiotic opinion), you make a connection with a coworker. These connections can be very valuable in the future. Make plenty of them.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.