Simplify and Enhance Your Reporting with Character-based Bar Charts

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.

devx-admin

devx-admin

Share the Post:
Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and

Anthropic Investment

Amazon’s Bold Anthropic Investment

On Monday, Amazon announced its plan to invest up to $4 billion in the AI firm Anthropic, acquiring a minority stake in the process. This

AI Experts Get Hired

Tech Industry Rehiring Wave: AI Experts Wanted

A few months ago, Big Tech companies were downsizing their workforce, but currently, many are considering rehiring some of these employees, especially in popular fields

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations with industry professionals. The gathering,

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and Dell Technologies Inc. more time

Semiconductor Stock Plummet

Dramatic Downturn in Semiconductor Stocks Looms

Recent events show that the S&P Semiconductors Select Industry Index seems to be experiencing a downturn, which could result in a decline in semiconductor stocks. Known as a key indicator

Anthropic Investment

Amazon’s Bold Anthropic Investment

On Monday, Amazon announced its plan to invest up to $4 billion in the AI firm Anthropic, acquiring a minority stake in the process. This decision demonstrates Amazon’s commitment to

AI Experts Get Hired

Tech Industry Rehiring Wave: AI Experts Wanted

A few months ago, Big Tech companies were downsizing their workforce, but currently, many are considering rehiring some of these employees, especially in popular fields such as artificial intelligence. The

Lagos Migration

Middle-Class Migration: Undermining Democracy?

As the middle class in Lagos, Nigeria, increasingly migrates to private communities, a PhD scholar from a leading technology institute has been investigating the impact of this development on democratic

AI Software Development

ChatGPT is Now Making Video Games

Pietro Schirano’s foray into using ChatGPT, an AI tool for programming, has opened up new vistas in game and software development. As design lead at business finance firm Brex, Schirano

Llama Codebot

Developers! Here’s Your Chatbot

Meta Platforms has recently unveiled Code Llama, a free chatbot designed to aid developers in crafting coding scripts. This large language model (LLM), developed using Meta’s Llama 2 model, serves

Tech Layoffs

Unraveling the Tech Sector’s Historic Job Losses

Throughout 2023, the tech sector has experienced a record-breaking number of job losses, impacting tens of thousands of workers across various companies, including well-established corporations and emerging startups in areas

Chinese 5G Limitation

Germany Considers Limiting Chinese 5G Tech

A recent report has put forth the possibility that Germany’s Federal Ministry of the Interior and Community may consider limiting the use of Chinese 5G technology by local network providers

Modern Warfare

The Barak Tank is Transforming Modern Warfare

The Barak tank is a groundbreaking addition to the Israeli Defense Forces’ arsenal, significantly enhancing their combat capabilities. This AI-powered military vehicle is expected to transform the way modern warfare

AI Cheating Growth

AI Plagiarism Challenges Shake Academic Integrity

As generative AI technologies like ChatGPT become increasingly prevalent among students and raise concerns about widespread cheating, prominent universities have halted their use of AI detection software, such as Turnitin’s

US Commitment

US Approves Sustainable Battery Research

The US Department of Energy has revealed a $325 million commitment in the research of innovative battery types, designed to enable solar and wind power as continuous, 24-hour energy sources.

Netanyahu Musk AI

Netanyahu and Musk Discuss AI Future

On September 22, 2023, Israeli Prime Minister Benjamin Netanyahu met with entrepreneur Elon Musk in San Francisco prior to attending the United Nations. In a live-streamed discussion, Netanyahu lauded Musk

Urban Gardening

Creating Thriving Cities Through Urban Gardening

The rising popularity of urban gardening is receiving increased recognition for its numerous advantages, as demonstrated in a recent study featured in the Environmental Research Letters journal. Carried out by

What You Need to Know About Cloud Security Strategies

What You Need to Know About Cloud Security Strategies

Today, many businesses are adopting cloud computing services. As a result, it’s important to recognize that security measures for data in the cloud are different from those in traditional on-premises

Romanian Energy Security

Eastern Europe is Achieving Energy Security

Canada and Romania have solidified their commitment to energy security and independence from Russian energy exports by signing a $3-billion export development agreement. The deal is centered on constructing two

Seamless Integration

Unlocking Seamless Smart Home Integration

The vision of an intelligently organized and interconnected smart home that conserves time, energy, and resources has long been desired by many homeowners. However, this aspiration has often been hindered

New Algorithm

MicroAlgo’s Groundbreaking Algorithm

MicroAlgo Inc. has revealed the creation of a knowledge-augmented backtracking search algorithm, developed through extensive research in evolutionary computational techniques. The algorithm is designed to boost problem-solving effectiveness, precision, and

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles