Crystal Reports:
Extracting a single digit from a decimal number

You can do this with a simple formula that uses the Totext function and a substring extraction The formula would have the expression

Totext({table.fieldname},"000000.0000") [5] //

to extract the ‘tens’ from a formula or

Totext({table.fieldname},"000000.0000") [8] // 

to extract the first digit after the decimal point. If your data had the value 1234.56 then the above examples would return "3" in the first formula and "5" in the second.

If your data had values greater than 1,000,000 or more decimal places, then you would need the formula to handle a larger value range with a longer format string ("000000000000.00000000") and a slightly different value for the [substring position].

I strongly recommend using the ToText function for problems like this. It has extensive features for changing the format of your data. There is a lot of good information in the help files, and all well worth reading.

Now, this brings us to the question of why you want such precision in the first place. Are you really answering any business intelligence questions with the results? Can a manager or executive use the results to adjust company strategy or better allocate limited resources?

You need to ensure your reports contain business information, not business data. They need to be decision tools, not intermediary data sources.

The data approach

The normal approach is to start with the database and push data to the users. No matter how you dress up data, you still have data. You do not have information. Data are raw materials and information is a finished product.

Suppose you walk into a Toyota dealer and say you need transportation. Do you expect to walk out of there with a container of parts, or to drive out of there in a car? That is the difference between data and information.

When you shove data at people, they try to assemble the data into information. Which is why you have all these folks wasting millions of salary-hours manipulating spreadsheets instead of doing the jobs they are paid to do. Which is why you have information silos. Which is why you have all sorts of other problems, which is why your job is less secure than it should be.

End-users need analysis, trends, conclusions, snapshots, summaries, thumbnails, overviews, projections, comparisons, and other things that are very different from data. When they don't have those things, out come the spreadsheets.

The report approach

Instead of starting with the database, start with the business questions. Talk with the senior executives (who may or may not be on your existing distribution list), and ask each one to tell you what the top three business questions are. If they give you more, that's OK. Compile a list, and see what data you would need for you to answer those questions with your reports.

Next, repeat this process with the people who are already on your distribution list. You are now ready to determine what will be reported.

Why talk with the senior executives, first? Those are the folks who run your company, so figure out what they want. Providing that helps secure your job and future raises in no small way. But it also helps you build the correct framework for your entire process, so that all users are marching to the same tune. You have to start at the beginning, not in the middle. By definition, middle managers can't see the big picture.

This raises another point. Surveys conducted between 2005 and 2008 showed that senior executives rarely have an accurate picture of their organization or the conditions under which it operates. They have a much rosier view, because people generally try to please them. These same people lack the time to dig through the data to see the real picture. In most companies, senior executives also lack the skills to do so. This means the data-oriented reports they get are essentially useless. If you are in charge of those reports, what does this say about your value to the company?

If you provide the senior executives with the business information they need, and you provide middle managers with the business information they need plus the business information the senior executives are working from, how do you think this will affect the effectiveness of management to make good decisions?

Instead of working from the detail level up, work from the information level down. Determine what information people really need. Then, use the power of Crystal Reports to assemble that information from the data you can get. 

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.