Reports


Chapter 7 . Calculating Report Data

At runtime, Panther can automatically capture data as it is output and use it to calculate totals. It can also copy output to other locations in the report, or use an array to collect a series of output values. You can also write your own functions to process output—for example, to calculate summary data such as averages, maximums, and minimums, or to control report execution by changing node properties at runtime.


Outputting Derived Values

A dynamic output widget's Value property controls how it gets its output value. You can set this property so that a widget gets its value from previous report output—for example, totals of column values within a given group. You can reset the widget's Value property to one of these options:

Unless set to Page Number, the Value property has one or more subproperties:

Value Source
Specifies the dynamic output widget whose values you want to total, copy, or collect. The property's combo box lists all named dynamic output widgets in the current layout window. You can choose or type in one of these; or you can enter the name of a widget to be created later.

Initialize In
Available only when the widget's Value property is set to History or Total, this subproperty is set to the name of a group or detail node. Panther initializes the widget's contents each time the specified node executes. The property's combo box lists all named group and detail nodes in the structure window. You can choose or type in one of these, or you can enter the name of a node to be created later.

Usually, you should leave this property blank. Panther sets it to the same group node that outputs the widget's layout area.

Set this property only in three cases:

Update In
Available only when the widget's Value property is set to History or Total, this subproperty is set to the name of a group or detail node. Panther updates the widget's contents each time the specified node executes. The property's combo box lists all named group and detail nodes in the structure window. You can choose or type in one of these, or you can enter the name of a node to be created later.

Usually, you should leave this property blank. Panther updates the widget as follows:

Context
If Context remains set to Default, Panther uses the value that is consistent with the context of the widget's output—that is, the page, group, or detail in which it appears. For example, a total widget that is output in a group footer gets the totals for that group, while a total widget that is in a page footer gets the totals for the page.

Reset this property in these cases:

Totaling

You can create widgets that automatically calculate and display totals for a column's values. These totals can be for a given group, for a specific page, or for the entire report.

Group and Grand Totals

Reports typically generate subtotals for one or more columns within each group, and a grand total for all values. Figure 7-1 shows a report whose output is grouped on distrib_name and order_num. Each group ends with a subtotal of qty values; the end of the report also contains a grand total for qty:

Figure 7-1 Report that generates group subtotals and grand total.

To generate this output, the report relies on three components:

Figure 7-2 shows how to define a report that generates the output shown earlier:

Figure 7-2 Layout areas and report structure for generating group and grand totals.

Page Totals

You can also generate totals at the page level by creating a layout that includes a total widget and outputting this layout from the page format node's footer section.

Copying

Reports sometimes need to reproduce data that has already been output. For example, it can be helpful to identify a group's totals by reproducing the output of the widget on which the group broke. Figure 7-3 shows a report fragment where subtotals for distributors and orders are each accompanied by a dynamic output widget; these widgets each repeat the output from their respective break fields:

Figure 7-3 Use copy widgets to repeat output from other widgets.

To reproduce output from one widget in another, create a copy widget—that is, a dynamic output widget whose Value property is set to Copy. The Value Source subproperty must be set to the name of the widget whose values you wish to reproduce. For example, the layout areas for the group footers shown earlier contain copy widgets whose Value Source is set to the groups' break fields, distrib_name and order_num:

The previous example copies group-level output. You can also copy data into page headers and footers. For example, you might want the page header to show the first value that is output by a group's break field, or the footer to show the last value that is output by that field.

Collecting

You can set a dynamic output widget to collect the values that are output in another widget. A widget that collects data from another—or history widget—can be used, for example, to collect totals for a given group and present these in a graph.

How to Create a History Widget

  1. Create a dynamic output widget in the unnamed area of the layout window.
  2. Under Geometry, make the widget a scrolling array by setting these properties:
  3. Under Composition, set these properties:

Using Historical Data in Graphs

Figure 7-4 shows a report that calculates and displays totals for each distributor in the vbizplus database. It then displays these totals in a graph:

Figure 7-4 Outputting report values in a bar graph.

The data required to draw this graph is derived from two history widgets:

Two graph properties of the graph widget are set to the values of the two history widgets: the Value Source property for Data Series #1, which populates the bar data, is set from Tdistrib_qty_hist; and the Label Source property for X Tick Marks, which sets the labels under each bar along the X axis, is set by distrib_name_copy_hist.

Viewing Property Links

To review dependencies between a dynamic output widget that processes another widget's data, and other widgets and nodes:

  1. Select the widget.
  2. Choose ReportShow Property Links.

Panther displays the Property Links dialog:

The Property Links dialog can contain two types of entries:

Going to a Property Link

When you display the Property Links dialog, you can give focus to the widget or node specified in the selected entry by choosing Go To, or by double-clicking on the entry. If the widget is in the structure window, Panther brings this window forward; if the structure window is closed, Panther opens it.


Outputting Database Calculations

You can take advantage of your database's aggregate or mathematical functions to calculate column values. The transaction manager fetches these values as part of the report data. To do this, create a dynamic output widget and set its properties as described below. You can create this widget either in the report itself or in a repository entry. If you add the widget to your repository, it is available for selection when you create reports through the report wizard. For more information on adding widgets to a repository, refer to page 4-19, "How to Add an Item to a Repository Entry."

Set these widget properties:

After you set the widget's properties, update the members of the table view to include the new widget.

Note: Add the widget to a repository entry in order to make it available for selection when you create other reports, specifically those created with the report wizard.


Using Call Nodes

By using call nodes to call your own functions, there is no limit to the types of operations that you can perform on report data. You can insert a call node anywhere in the report structure, giving you access to data at every level of report execution. The functions that you write can be written in either C or JPL; you can store them in the report file itself or on disk in a file or library.

Calculating Widget Output

You can call functions that compute typical summary data such as average, minimum, or maximum values. Summaries can be calculated for any given group, for each page, and for the entire report. The following report shows the largest video order from each distributor:

Figure 7-5 Maximum order quantities for each distributor.

A report can generate and display calculated data through three components:

A report that generates the kind of output shown earlier might use the following layout area and report structure:

Figure 7-6 Report that calculates maximum output.

Creating a Calculation Widget

A calculation widget acquires and optionally outputs calculated data. To show calculated data, place the calculation widget in a layout area that is output. To hide calculation widget data, place the widget in the layout window's unnamed area (below all layout areas), or set its Hidden property to Yes or Always.

The report defined in Figure 7-6 defines a single group whose break field is distrib_name; the layout area for the group's footer contains calculation widget Mxdistrib_qty. During detail processing, this widget gets the maximum qty value that is calculated for the group; when the group breaks and its footer area is output, Mxdistrib_qty contains this value.

Initializing Calculated Data

Before any detail data is output, the function called from the group node's header section initializes Mxdistrib_qty to 0:

proc init_max_distrib_qty()
Mxdistrib_qty = 0
return

This initialization function is called each time a break in distrib_name generates a new group.

Updating Calculated Data

After fetching and outputting each row of data, Panther executes the call node that is attached to the detail node. This call node's function compares the fetched data in qty and compares it to Mxdistrib_qty; if the data in qty is greater, the function puts this value into Mxdistrib_qty. It also updates the value in the group footer widget Mxdistrib_film:

proc update_max_distrib_qty()
if Mxdistrib_qty < qty
{
Mxdistrib_qty = qty
/* if updating max, also get film name */
Mxdistrib_film = name
}
return

Controlling Report Execution

You can programmatically control report execution through JPL return codes that affect subsequent processing; you can also modify execution by setting report node properties at runtime—for example, a print node's Area property.

Using JPL Return Codes

The JPL procedures that you call during report execution can return with values that influence subsequent processing. Table 7-1 lists these return codes and how they affect report execution.

Table 7-1 JPL return codes for reports

Return code Action

SM_RW_OK

Continue normal report processing.

SM_RW_SKIP

Skip over all remaining action nodes that are attached to the current structure node. If the call node is attached to a detail node, begin the next fetch.

SM_RW_ENDDETAIL

Stop fetching data and skip over all nodes attached to the current detail node.

SM_RW_ENDREPORT

Silently terminate current report. If this is the main report, finish outputting the current page before quitting.

SM_RW_ENDRUN

Silently terminate main report. Finish outputting the current page if necessary.

SM_RW_ERROR

Immediately stop execution of all reports and post an error message.

For example, you can conditionally execute a report's title page, generated by a print node and end page node, if both are preceded by a call node that calls this procedure:

proc set_area

if draft_arg == 1 /* draft distribution */
@widget("title_pg_print")-> area = "draft_title"

else if draft_arg == 0 /* final distribution title */
@widget("title_pg_print")-> area = "final_title"

else /* no distribution, no title page */
return SM_RW_SKIP /* skip print/end page nodes */

return SM_RW_OK /* process print node */

Setting Node Properties at Runtime

The previous example shows JPL code that sets a print node's Area property at runtime. A number of node properties, listed in Table 7-2 are accessible at runtime. You can read and change these properties using JPL or equivalent calls to Panther library functions. For example, this JPL statement gets the name of the report invoked by subreport node qtr_summary:

vars sub_rpt
sub_rpt = @widget("qtr_summary")->report_invocation

Only named nodes are accessible at runtime; their object class is @widget. For more information about accessing widget properties, refer to "Properties" in Application Development Guide.

Table 7-2 Report properties that are accessible at runtime

JPL property mnemonic
C constant
Values Constraints

Detail node:

root

PR_RW_DETAIL_ROOT

str—root table view

Data Source = TM

sql_statement

PR_RW_SQL_STATEMENT

str—SELECT statement

Data Source = SQL Query

cursor_and_using

PR_RW_CURSOR_AND_USING

str—cursor name and arguments

Data Source = Predefined Cursor

Group node:

break_on

PR_RW_GROUP_FIELD

str—break field name

Call node:

function_call

PR_RW_ACTION_STRING

str—call string

Print node:

area

PR_RW_ACTION_STRING

str—layout area name

Subreport node:

report_invocation

PR_RW_ACTION_STRING

str—invocation string

Transferring Data from a Report

You can use Panther's send command to send data back to its caller. If the caller is a Panther application, it can retrieve this data through a corresponding receive command; otherwise, it can use Panther library functions such as sm_receive or sm_get_bundle_data. The send command can send data from report widgets and global variables, or any valid expression.

The call node whose function contains the send command can be placed anywhere in the report structure, so you can send data back to the caller at any stage of report execution. Use send and receive as vehicles for return values when the report returns to its caller, or for sending data during report execution—for example, to save status or error messages.


Grouping Data on Computed Break Fields

In previous examples, groups are defined through break fields that obtain their values from the report's data source—typically, a database. You can also set a group node's Break Field property to a widget whose value is computed by a function written in JPL or C.

Computed breaks can be used to group data in a number of ways. For example:

In each of these cases, the group's break field is a widget or global variable whose value is computed. The function that sets the break field's value must be set in the report detail node's Pre-Break Call property. Panther performs this function after it fetches the detail data but before it checks for break field changes and begins break group processing.

In the following example, the break field's value is derived from fetched data. The report fetches tape rental data and groups it in ranges of times rented —over 80, between 61 and 80, and 41 and 60. The report must order the fetched rows by the times_rented field in descending order, and contains these components:

When you run this report, Panther executes set_rental_group each time a row is fetched:

proc set_rental_group

/* check the fetched value in widget times_rented
* and determine which group this record belongs
* in, set widgets rental_group and rental_range_label
* accordingly.
*
* the ranges are: 1: 41 - 60
* 2: 61 - 80
* 3: over 80
*
* the variable rental_group is a non-output widget
* in the layout window's unnamed area
*/

if times_rented >= 81
{
rental_group = 3
rental_range_label = "Times rented: over 80"
}
else if times_rented >= 61
{
rental_group = 2
rental_range_label = "Times rented: 61 - 80"
}
else if times_rented >= 41
{
rental_group = 1
rental_range_label = "Times rented: 41 - 60"
}
else if times_rented < 41
/* don't bother to check any further */
{
return SM_RW_ENDDETAIL
}

This procedure sets the break field rental_group before break group checking occurs, so Panther can correctly determine whether to start a new group. When you run this report, it yields this output:

Figure 7-7 Report with groups generated by computed break field.