Tutorial 5: Graphs Application

Note: We recommend that you print this tutorial for easy reference. To print, click on the right-hand frame before accessing your HTML Browser's File/Print command.

In the previous tutorials you have created interfaces that presented information in the form of labelled data fields, enumerated widgets, and tables. One of the most exciting features of DBPak is that other presentation styles are available. In this tutorial you will learn how to present sales data graphically using exactly the same techniques as before.

When creating graphs, you will usually want to group the data by some common value. If you think about graphs you've seen, they seldom display actual data. They usually display sums or totals compared to each other. This grouping is accomplished with the XmNgroupBy resource on queries and widgets with query attributes, like the plotter in this example.

Suppose that you want to find out which items in your company's inventory are selling and which items are just taking up shelf space. One way of presenting the data is through a column of numbers, but it would be hard to find the "interesting" data. By creating a graph, however, those interesting values would become very obvious.

You will create a bar graph of sales for each item in your inventory with one bar per item. Individual item sales can be found in the InvoiceDetail table, while the actual descriptions of the items are in the Inventory table.

Creating the Plotter Widget

• Start by creating a new application by selecting New from the Browser File menu.
• Select the OrderEntry database in the Schema Browser.
• Select the Inventory table in the Tables list in the Schema Browser.
• Select Graph Types... Bar Plot in the Object Type option menu in the Schema Browser.
• Select the Description field from the Columns list.
• Drag the Description field and drop it onto the Browser.

Notice that Database Xcessory created a XiDBPlotter widget along with the other familiar basic widgets, such as the Control Panel.

The Plotter widget is a container that holds various plots and axis widgets.

If you select Show Compound Children from the Browser View menu, you can see the axis widgets (xaxis and yaxis) that were automatically created, as shown below:

Compound Children of dbPlotter Widget

Database Xcessory sets the plotter's xColExpression resource to Inventory.Description. Those values will label the x axis when you switch to Play Mode to test the interface.

dbPlotter Display on Your Interface Before Entering Play Mode

Creating the Bar Plot

• Select the InvoiceDetail table from the Tables list in the Schema Browser. The InvoiceDetail table contains the detail records for the Invoices in the database. The detail records represent the actual line items in the order forms. There is one detail record for each line that appears on an invoice. The InvoiceDetail table contains the number of each item ordered on that invoice and the price per item.
• Select Price from the Columns list.
• Drag the Price field onto the dbPlotter object in the Browser hierarchy.

Alternatively, you may drag the Price field onto the Plotter widget in the interface. XiDBBarPlot is now created as a child of the plotter widget, shown below:

Bar Plot (invoiceDetail_Price) is Child of Plotter Widget (dbPlotter)

The Plotter is a container widget that has x-axis and y-axis compound children. When you add a plot widget (such as a Bar Plot) to the Plotter, you are adding just the plot and a set of y values.

You may be wondering why a Plotter widget was created the first time, and a Bar Plot widget is created this time. Database Xcessory notices that you are dragging the field into an existing Plotter widget and it created a BarPlot. Each new plot dragged onto the Plotter widget will be added as a child and another set of y values. You can mix plot types in one plotter container.

How Interface Appears Before Entering Play Mode

Setting Resources for Sum

• Select invoiceDetail_Price (the XiDBBarPlot) in the Browser if it's not already selected.
• Press the Update button in the Resource Editor window to update the resources for the BarPlot widget.
• Change the expression resource from "InvoiceDetail.Price" to "sum(InvoiceDetail.Price * InvoiceDetail.Quantity)".

We want the value of all like items on all invoices, and not just the price for a single unit. We have to take into account any lines in which a person ordered more than one item. This expression is added to the SQL select statement that is generated and sent to the server. The server performs the calculation, and returns a single value to be used by the bar plot.

• Select the dbPlotter widget in the Browser.
• Press Update in the Resource Editor window.
• Set the where resource to:
  "Inventory.Part_Number = InvoiceDetail.Part_Number".

This joins the Inventory table to the InvoiceDetail table. By setting the where resource, you can then use values from both tables with a single query. When the data is fetched from the database, both tables will be consulted.

Creating the Groups

• Edit the groupBy resource to "Inventory.Description." In this case we need to group the data by inventory item. That is, we are asking the database to give us the sum of the money spent on all orders, grouped by inventory part.

All resources from the Plotter and the BarPlot are pulled together to create an SQL select statement to gather data for the graph. The statement sent looks like the following:

SELECT Inventory.Description,
sum(InvoiceDetail.Price * InvoiceDetail.Quantity)
FROM Inventory, InvoiceDetail
WHERE Inventory.Part_Number = InvoiceDetail.Part_Number
GROUP BY Inventory.Description

When reviewing these steps you can see how this statement was constructed: the select expressions came from the dbPlotter and dbBarPlot expression resources, the where clause came from the where resource on the dbPlotter, and the group by clause came from the groupBy resource on the dbPlotter.

• Go into Play Mode to test the interface.

When you press the Search button, the data is fetched and the graph is displayed, as shown.

Interface In Play Mode

You must make the window very wide so that the inventory descriptions aren't displayed on top of each other.

Next Stop on the Tour