Simplify and Enhance Your Reporting with Character-based Bar Charts

Introduction
ack in January of 2002, I was thumbing through the December issue of elementkjournal’s, “Inside Microsoft Access”, when I came across the MSDN article entitled, “Graphically Display Percentages on Forms and Feports” by Sean Kavanagh. The technique shown in the article was so simple and straightforward, yet so elegant and flexible, that it sent me on a mission to find out if it could be extended to other packages. What was Sean’s technique? Simple. Pick a text character and repeat it as many times as necessary to create a bar chart. While the article’s specific implementation used Micrsoft Access’s built-in String() function, you can apply the same general approach in many different environments.

In fact, the capability to add visually appealing text-based bar charts to reports and applications already exists in nearly every software package?and you can apply it without much effort. Including a visual data representation in a report or analysis, can aid users in the interpretation of the data, and can often provide insights that may not otherwise have been obvious. The purpose of many data-driven applications and, certainly, of most canned reports, is to assist in the conversion of raw data into information?and it’s precisely this idea of conversion of data into information that makes the topic interesting and useful. The technique is especially valuable in database applications where SQL queries might be the final output and are, inherently, tabular.

The technique outlined in this paper is simple: (1) use special text characters to serve as symbols and (2) take advantage of a particular software or programming language’s built-in ability to repeat these characters as many times as necessary.

For example, Figure 1 might represent the average rating of 50 blindfolded, taste-testers randomly given three different colas. The testers were asked to rate, on a scale of 1 to 5, how well they liked the taste. The rating results may end up as an executive summary in a word processor document, or maintained and updated in a spreadsheet, or distributed via email?it doesn’t matter. No matter which technology you’re using, this technique will work?and without using large embedded or external images or complicated graphic commands.

Figure 1. Hypothetical mean rating for three brands of cola.

Producing Charts
You produce a chart using a two-step process. First, choose a character (such as “*“, “x“, etc.) to serve as a marker. Second, repeat the character as many times as necessary. For the cola example, the maximum mean rating for any one of the cola brands is five. Thus, the maximum number of characters required to create a bar chart for the ratings should be some multiple of five. (The sample in Figure 1 used x3.) That is, a mean of five would equate to a bar chart 15 characters wide and any mean less than five is then scaled proportionately. In symbols, that means NumChars = 15 x (Mean / 5.0). So, how wide should the associated chart be when the mean is one? Well, according to the logic you’ve already seen, you would calculate a mean of one as 1/5=0.20, so you would expect to make the chart 0.20 x 15 = 3 characters wide. But, you probably don’t want that in this case. Most likely, you would want to have a mean of one have a zero-length bar?that is, no display at all. You can accommodate that by making an adjustment to the calculation: NumChars = 15 x ((Mean – 1.0) / (5.0 – 1.0)).

The take-away point here is not how to derive a deterministic function for how many characters to type out when a 1-5 scale is used, but rather to illustrate the idea that you can choose any text character you like and repeat it appropriately to produce bar charts that can help derive meaning out of summarized, tabular data.

ASCII Characters
Thus far, the term “text characters” means nothing more than one-byte ASCII characters. ASCII stands for American Standard Code for Information Interchange and was published in 1968 by the American National Standards Institute (ANSI). It was intended to define “standard character codes for interchanging data that could handle the full character set of an English-language typewriter” (see A Brief History of Character Codes). In essence, the standard gives each typical character found on a keyboard a numeric code between 0-127. In addition, an extended ASCII character set (codes 128-255) includes characters not typically seen on a standard, English keyboard; such as Latin characters, trademark symbols, and smiley faces. For better or worse, these extended characters are highly dependent upon the particular computer’s operating system, language setting, and the current font. But that scheme doesn’t take many foreign language characters and symbols into account. Consequently, several different “standard” character sets arose to meet different needs. For a sample listing, see The ISO 8859 Alphabet Soup.

Unicode Characters
Unicode Standard was born out of an attempt to create a single multinational and universal character set that could support all human languages. The Unicode Consortium is a non-profit organization founded to develop, extend and promote the use of this Unicode Standard (http://www.unicode.org). However, the Unicode character set is not just another character set, it goes far beyond that. Because it uses two bytes to represent characters, instead of getting only 255 possible characters to choose from, there are 65,536 possible characters! The best part is that many of these characters are not just linguistic, but also symbolic. For example, there’s subsets for mathematical symbols, block elements, and geometric shapes. For a complete list, see http://www.alanwood.net/unicode. The Unicode block character used in the cola example, has the value 9608 (or 25F4 in hexidecimal).

Producing Characters
The rendering of text characters to create bar charts is, of course, dependent on the font used. While the “what” to be displayed is derived from the actual character chosen from the ASCII or Unicode character set, the “how” is the result of the font used. For example, the percent character (%) in an Arial font looks slightly different from the same character in Times New Roman. Also, be aware that not all fonts support all characters sets (especially the emerging Unicode character set. However, most newer fonts do).

You can type the bars directly into documents, store a character in a variable so you can use it on demand whenever needed, or generate the character dynamically using a formula or function. Most tools and languages have built-in functions for generating ASCII and Unicode characters. The last method is the most flexible, because you can use conditional formatting to create the chart.

In summary, Table 1 below lists several popular programs and languages that have such functions for generating characters.

Table1. Built-in functions for generating characters dynamically where X = the numeric character code (decimal format).

Program or Language

ASCII

UNICODE

Crystal Reports

chr(X)

Unicode not supported

HTML

&#X;

&#X;

Java

(char)X

(char)X

JavaScript

String.fromCharCode(X)

String.fromCharCode(X)

Lotus Notes (Formula Language)

@char(X)

no built-in function

Lotus Notes (Lotus Script)

 chr(X)

uchr(X)

Microsoft Access

chr(X)

chrw(x)

Microsoft Excel

char(X)

no built-in function

Oracle

chr(X)

nchr(X)

Perl

chr(X)

chr(X)

PHP

chr(X)

no built-in function

SAS

byte(X)

no built-in function

S-Plus

no built-in function

no built-in function

SPSS

no built-in function

no built-in function

SQL Server

char(x)

nchar(X)

Visual Basic (incl. VBA, VBScript)

chr(X)

chrw(x)

It is worth noting that just because a program or language does not have a built-in function to dynamically create ASCII or Unicode characters does not necessarily mean that you can’t use these special characters?you’d just need to store them in string variables or type them in manually.

Repeating Characters
Now for the fun part. After creating or selecting a symbol you need to repeat it as many times as needed. In the cola example above, the calculation used the average rating (1-5) of each brand. In a different application, where there is no bound on the maximum value, the number of characters might be the actual count and not a ratio of the maximum (possible) value. Regardless, the number of characters to plot will be dictated in the context of the problem. In some applications, the number will be known and fixed (like an executive summary email to shareholders). In other applications, like a database query, the number will change dynamically per record. Thus, there are two ways to create repeating characters:static and dynamic.

In either case, many programs and languages also have built-in functions for repeating characters a specified number of times. When the number of times to repeat needs to be dynamically set, you can pass the parameter by reference to another variable, such as a spreadsheet cell reference. But even if a program or language doesn’t have a native function to create a string containing a specific number of characters, you can use a looping routine in a user-defined function that repeatedly concatenates your symbol character onto a string variable until it has grown to the desired length. Table 2 illustrates the various built-in functions in several common packages and languages.

Table2. Built-in functions for repeating characters a specified number of times where c = character (i.e. could be a string literal, a variable, a spreadsheet cell reference, or a database field), and n = number of times to repeat character (i.e. could be also be a variable, cell reference, or database field)

Program or Language

Function

Crystal Reports

ReplicateString(c,n)

HTML

no built-in function

Java

no built-in function

JavaScript

no built-in function

Lotus Notes (Formula Language)

@repeat(c;n)

Lotus Notes (Lotus Script)

ustring(n,c)

Microsoft Access

string(n,c)

Microsoft Excel

rept(c,n)

Oracle

no built-in function

Perl

no built-in function

PHP

str_repeat(c,n)

SAS

repeat(c,n)

S-Plus

no built-in function

SPSS

no built-in function

SQL Server

replicate(c,n)

Visual Basic (incl. VBA, VBScript)

string(n,c)

Examples
Here’s a handful of sample examples that may help illustrate the potential of this method.

Email Example. Figure 2 illustrates the cola example applied in an email situation. In this case, the chart was hand-typed into a Microsoft Outlook email using Arial font and repeating the Unicode block character 9608.

Figure 2. A hypothetical rich-text email for the cola example.

Microsoft SQL Server Example. Figure 3 might be a typical query where thousands of products in a large organization are offerred and a database manager would like to scan visually for outlying observations to ensure data integrity in the underlying database (of course, in reality, simply computing standardized z-scores and selecting them out would be more probable). An additional application might be for, if the final output is not a query, but a data-driven Web page, this “chart” gets simply passed into the Web page as a string, saving the Web server precious resources. (Note: if using unicode characters in a Web page, the meta tag should always be used within the section of the page)

Figure 3. Bar charts added to a tabular SQL query help identify outlying observations in a glance.

Microsoft Excel Example. Figure 4 depicts a classic application of how this general method can be most valuable. Typically, users create Excel chart objects to visually represent their data. While Excel charts are very powerful, they can also be bulky and require considerable effort to arrange “just right” within the worksheet at the intended location. However, by using Excel’s =REPT() function to repeat characters, you can treat a single cell like a stand-alone bar chart. In fact, you can refer to an adjacent cell in the formula to control how many characters to display, thus making the chart highly dynamic.

Figure 4. Two charts sized dynamically in Excel based on adjacent cell values eliminate the heavy overhead or hassle of Excel’s chart objects.

Java Example. Figure 5 shows a sample dialog box that might appear within a Java application or applet on a Web page. Again, the idea is highly extensible to most any type of situation wherein data is summarized and presented&#151and without the need for complicated external graphics.

Figure 5. Here’s a Java application with text charts providing the illusion of graphics.

SAS Example. Figure 6 is another demonstration of the cola example. By taking advantage of SAS’ REPEAT() function, you can update a data set any time to append a new, calculated string variable (e.g., Graph$ in this case) which creates the graph. The advantage here is that the chart follows the data around anywhere?including if the dataset is desired to be exported out to another application. This is true, in general, of the other examples. The graphs become just another field that can be formatted, manipulated, and exported, as necessary.

Figure 6. SAS example of plotting the cola example.

This paper has just scratched the surface on a very simple method for adding a visual dimension (and appeal) to documents and applications wherein data is presented. It’s often extremely easy and adds tremendous value for very little effort or cost. Different software applications and programming languages than those presented here most likely support this idea directly via built-in functions or indirectly via user-defined functions or some macro language. The key is to just experiment and have fun, trying different fonts and character sets supported by the intended operating system and application.

Lastly, because the graphs are often string fields, spreadsheet cells, or variables (depending on the application), they become highly mobile for use in other applications. This is not generally true of more traditionally-generated, graphical reports. In conclusion, to the analyst or statistical programmer, whose primary objective is to help turn data into insight, this is a nice, generalized approach to keep in mind and have in one’s toolbox.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job