It will come as a surprise to no one that PowerBuilder's native graphing capabilities are somewhat lacking. Even the Define Graph Style dialog for the DataWindow graph style seems to have been left out of the GUI update in PowerBuilder 10.5 (see Figure 1). Forget Windows 95, this looks like something out of the version of Excel that came with Windows 3.1.

So what would we want from updated graphing capabilities? I can think of a few important features:

1. Something that would work equally well in a Web application as a client/server one.
2. Transitions, to allow some animation in the graphic during its initial display.
3. Click-through, making it possible to respond to actions on the chart to drill down into additional information, whether it be additional charts or just access to the underlying data.
4. More control over the generation of the chart. For example, for 3D pie charts we should be able to explode segments and have control over the amount each segment is exploded.
5. An updated look-and-feel. That's a little hard to put into words, but what we want to do is make Figure 2 look like Figure 3.

Well, that's all possible today. Figure 3 was generated from data from a couple of PowerBuilder DataWindows and meets the other feature requirements indicated above. We're going to examine how it was possible to do that.

When you look at the list of desired features presented above, what thoughts pop into your mind? Well, what popped into my mind was Adobe Flash. Flash is capable of generating charts that meet all of those requirements. The problem is how do we use PowerBuilder to generate Flash charts, particularly if don't have and/or don't know how to use the Adobe development tools?

Fortunately, that problem is easily solved. There are a number of articles (including code examples) on the Internet demonstrating how to dynamically create Flash charts from XML files. For example, "Build a Better Charting Engine Using Flash and XML" by Pallav Nadhani (www.devx.com/webdev/Article/20901/1763) or "Using an XML File To Load Chart Components" by Sas Jacobs www.flashkit.com/tutorials/Actionscripting/Using_an-Sas_Jaco-822/index.php.

Those articles are largely written for Adobe developers and demonstrate a proof-of-concept. However, at least one person took it the next step and created a Flash library available for reasonable price that not only generates the charts from XML, but also accepts information in the XML file that determines a wide number of the charting options. See "XML/SWF Charts" (www.maani.us/xml_charts).

If we're generating a Web application from PowerBuilder all we need to do is include a reference to the Flash file in our HTML page and tell it where to find the XML file we're generating. For a client/server application, we simply embed the ShockWave player ActiveX in a window and, similarly, tell it where to find the XML file we're generating.

So the only thing we have to do now is generate the XML file that provides the data and the charting options we want. That turned out to be a bit trickier that it seemed at first. While PowerBuilder DataWindows renders XML very easily, the library expects to see the data in a somewhat non-standard format. It also takes the data and the charting options in a single file, whereas we probably want to use two separate DataWindows to generate that information and then combine them.

With regard to the charting data, let's say we want to generate our chart using the following data (see Table 1), which was actually the basis for Figures 2 and 3.

If you did a typical XML export from a DataWindow with that data, you'd end up with something like Listing 1.

However, the library looks at the data differently. It expects you to pass the data as if it were formatted as in Table 2 and it expects you to pass in the labels for the series as part of the data. What it expects as far as the tag is the data type. So the XML it's expecting is shown in Listing 2.

Generating the XML in that unusual format actually turned out to be simpler to do than it seemed at first. I created two separate XML templates (see Figure 4), one for exporting the categories and another for exporting the values. I then did two XML exports, one for each template. Actually, there were four templates and four exports required, because I'm generating the color for the series and the explosion factor from the chart data DataWindow as well.

Because the library is expecting the label series as the first record in the data, I'm copying the data into a data store and inserting a blank row as the first record before exporting the XML.

Figure 5 shows the DataWindow I used for generating the chart options, and Figure 6 shows the XML export template used to generate the XML. Obviously, you wouldn't show the chart options DataWindow to the end user, you would simply use it with a data store in script. There are also many more charting options available. I've only included those that were necessary to reproduce one of the library vendor's sample charts. In particular, the "link" data associated with the user clicking on different areas of the chart isn't included.

You'll note as well elements in the chart options export (chart_data, series_color, series_explode) that aren't associated with any data. Those are placeholders for the XML that I'm generating from the DataWindow that has the actual chart data.

Listing 3 demonstrates how the XML is assembled into one file to pass to the library, and Listing 4 shows how the information about the resulting file is passed to the ActiveX control for display.

The sample code for this article (excluding the library) is available on Sybase's CodeXchange site. The library is available for free evaluation so you can use it to see the results yourself.

Conclusion
Next-generation charting is possible with PowerBuilder today with the assistance of a relatively inexpensive Flash library. I highly recommend reviewing the vendor's Web site to see all the different graph styles supported (as well as gauges that can be dynamically rendered in a similar way). Then take a look at the sample code and extend it for your own purposes.

--This article was originally published on PBDJ.