Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Concepts in Graphing

Concepts in Graphing

When I sit down to write this column I always ask myself, "What can I write that will be the most help to DataWindow programmers?" At the moment I'm also in the final production stages of my new book, The Definitive DataWindow. This morning, while glancing at my chapter on "Graphing," I remembered that this is an area not well understood by many people. In addition, an awful lot can be said about graphing, maybe enough for a series of articles.

My goal right now is to make sure you're an expert at creating graphs. I'm going to show you how to create them, how to use computed columns to make the data "fit" into your graph and how to make them interactive. I probably won't be able to fit it all into just one article - but I'll try.

We're going to develop an application that holds examples of graphs. Actually, this is my typical test application. I write one for every application I work on. In fact, I've done this since I developed my first one decades ago. I have an empty application I call "tester"; it's sitting in its own folder, empty except for the skeleton. Every time I start a new application, I copy tester.pbl to the appropriate folder and begin to build on it.

First we'll create our tester.pbl, which I'll explain how to use, then we'll get to our first simple graph.

For our test application we'll have two windows, one to log in and the other as a jumping-off point for our test windows. The idea is that we create a window for each individual test, then go back to the main window and add the test in there.

I've created the test application in my own folder under "personal apps" called, quite appropriately, tester.pbl. In fact, the only things in that folder are tester.pbl and connection.ini. That way, when I start an application, I just copy both of those files into my new application folder and I'm ready to go.

Figure 1 is the login window and Figure 2 is the main window, both from tester.pbl. When I create a new tester application I have to set some of the properties to customize them.

Let's take a look at the login window. I call mine w_login. There's one checkbox for the autoCommit, a series of single-line edits for the values, some static texts and a couple of buttons - one for canceling the login and one for executing the connect.

You can find the code for the open event of the window in Listing 1. It simply loads the connection parameters from the connection.ini file into the controls on the login window. The only other code required in this window is in the clicked event for the Connect button. You'll find it in Listing 2.

The next step is the main window. I call mine w_main. You can see from the figure that it just has a listbox, a couple of staticText objects and a button. In this case I've named the staticText objects st_help and st_window. The former holds text that will describe the test when the user clicks on the listbox; the latter holds the name of the window so I don't forget where to go to see the code.

The code for the open event is found in Listing 3.

The only other code in this window is found in the listbox control. There's code in the selectionChanged event, in which we'll change the values in the StaticTexts, and there's code in the DoubleClicked events, in which we fire off our example window. These are found in Listings 4 and 5, respectively.

That's all there is to the template test application. As I said before, when I start an application I copy the pbl and the connection.ini file into my new application folder, and whenever I need to test something I put it in there.

Our First Graph Exercise
Before we get started on graphs we need to understand a few terms. When you're using graphs in a DataWindow, you've moved away from the traditional row-and-column metaphor. You're now dealing with categories and values. What's worse is that these terms are so flexible, they can be just about anything. I think one of the hardest things about graphs, at least for me, is we're essentially dealing with the data in a different way. Now we talk about directions and dimensions rather than rows and columns. It gets even worse - we're often talking about multiple lines or planes. To put it frankly, once you're dealing with graphs you're no longer in Kansas, Dorothy.

Let's define our terms.

  • A category is normally the span of your graph. It's the opposite of a value. For instance, if you graph sales between a set of dates, the category would be the dates.
  • A value is the data that you're graphing. In our case of sales, it would be the sales amount or perhaps the number of sales.
  • A series is the collection of graphing lines. For example, you might want to compare the sales of one product with another and the series would be the product.
With these definitions in mind let's do our first little graph. In our example we've been asked to create a graph that allows a manager to view shipments that occurred between a user-supplied pair of dates. The manager doesn't care which product is shipped; his only concern is that there have been shipments.

First create the DataWindow, a graph with a SQL source. The SQL is shown in Figure 3, but just in case you prefer text, here's the SQL statement:

SELECT "sales_order_items".
"quantity",
"sales_order_items"."ship_date",
"product"."name",
"product"."description"
FROM "sales_order_items",
"product"
WHERE ( "sales_order_items"."prod_id" =
"product"."id" ) and
( ( "sales_order_items"."ship_date"
between :adate_start and
:adate_end ) )
ORDER BY "sales_order_items".
"ship_date" ASC
As you can see, we're selecting from two tables: thesales_order_items and the product. The reason we want the product is so we can display the name and description of the products.

We also have two retrieval arguments - the start and end dates. Thus we have quite a simple little DataWindow here.

Figure 4 shows the next dialog we get. In this case we aren't concerned with series. Our manager told us he didn't care about which products, just all of them grouped together. So the category (the span) is going to be the dates. The value then would be the sum of the shipments for that date.

The next dialog is shown in Figure 5. All we need here is a title for our graph and to specify which style it is. Since we're going to do a line graph, that's what we select.

This dialog is followed by a summary of the options we've chosen. Click past that one and you'll be in a DataWindow painter for your DataWindow.

It's time to put our test program to work. We need a window to hold this DataWindow and a couple of controls to provide us with the dates. Think about it - we're about to create a window. Should we have a superclass for this (an ancestor)? Do you suppose you'll ever need a similar window again? I think you might. In fact, since I know what I'll be writing in the coming months, I can tell you for certain that you will.

We should pause right here and create an ancestor window - a new one (or inherit from an appropriate existing one) - and put in the two edit masks for the start and end dates.

Wait a minute! Two edit masks for the start and end dates? Will we ever need that combination of edit masks again? Just how many times have you created them? Okay, just leave that window painter on your screen. We'll come back to it soon enough. For now, click on New, then the Object tabpage, and finally the Custom Visual (see Figure 6).

The next step is to put two static texts on the object for the From and To, and then two edit masks.

We need to give the programmer some way to get the values of those edit masks. This means one function for getting the From Date, another for getting the To Date. Those functions are found in Listing 6.

We're finished with this object. Close it and save it as u_dateRange and you'll be back at the new window you just left, ready to place your object on it.

Put your date range object on your window, a couple of buttons for running your report and another for closing the window, then put a DataWindow control on the window to run. You don't have to set the dataObject property of the DataWindow control yet; we're still in the ancestor. However, you should set the control name for your date range object to uo_dates (see Figure 7).

In the open event you have only one line of code:

dw_1.setTransObject(sqlca)
In the OK button you need to close only your parent (the parent of the button is the window).

The only real code in this window is in the clicked event of the Run button. You can find that in Listing 7. Note: I made the Run button default so the user can enter the dates, then hit Enter to run the report.

Close and save this window. I call mine w_super_report_daterange because I like to keep all my ancestor (superclass) windows together in my libraries.

Now that our infrastructure is set up, we need to use it, which is the point of this article. You might note that in the future, if you ever have to do another similar task, you can skip most of the preceding steps.

Inherit from your superclass window, set the title for the window (mine is "Simple Date Range Graph Test"), then click on the DataWindow and assign it the dataObject of the graph you created earlier. Now close and save your window. I called mine w_product_shipments. That's all there is to it. No code at all! In the future you can create any date range report you want by simply creating the DataWindow, inheriting from w_super_report_daterange, and setting the title and the dataObject.

The final step is to add this to our test application. Open w_main again and add "Product Shipments" to your listbox.

Now go to your DoubleClicked event for that listbox and add your case statement for this new item. You can find mine in Listing 8.

Finally, go to the selectionChanged event for the same listbox and add your case statement for the item again. You'll find my code for this in Listing 9.

This gives us a start, which we'll enhance in my next column. We'll clean up a few things in the graph to make it look better, then we'll make another similar, but more complex, graph.

More Stories By Rik Brooks

Richard (Rik) Brooks is the owner of Brooks & Young, a Sybase Code
Partner. He's been using PowerBuilder since 1990 and has worked as an
independent consultant for major corporations in the U.S. for the last five years. He has authored several books on PowerBuilder including PFC Programmer's Reference Manual and The Definitive
DataWindow.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.