Welcome!

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

Related Topics: PowerBuilder

PowerBuilder: Article

DataWindow Reporting

DataWindow Reporting

In my early PowerBuilder days, I was very impressed with how easy it was to create and format DataWindows for either online or printed reports. At its easiest, a simple print method is required to send a "What you see is what you get" version of the DataWindow to the printer. Even when the printed version of a DataWindow is different from the visual one, a simple ShareData() function would be all that is required.

What took a while, however, was learning how to display and print reports that either had a master/detail relationship or no relationship at all. In short, what if I need to create or print a report that's based on different SQL statements? There are two solutions to this problem:
1.   Create a master (or base) DataWindow, then add additional nested DataWindows (nested reports).
2.   Create a report by using the DataWindow composite presentation style.

Nested Reports
The first way to include a report within a report is to add one to an existing DataWindow. Nesting reports is a common practice when dealing with a master/detail relationship. Adding a nested report is straightforward: within the master DataWindow select the report icon from the control palate, then drop it in the detail band.

Figure 1 contains a master DataWindow that displays the columns from the department table. Within the detail line on the left-hand side is a nested report that contains a list of all employees who work within the department displayed on each detail line. The nested DataWindow takes the department ID for a retrieval argument. A design time view is displayed in Figure 1.

When it was created, the nested DataWindow d_chap_emps was defined to accept a retrieval argument - the department ID - and then display employee information for that department. To establish the link between the master DataWindow and the nested one, supply the arguments property of the nested DataWindow with an appropriate value, in this case the value of the dept_id column. Now when the master/detail window is retrieved, the department ID will be passed as a retrieval argument to the nested DataWindow, in turn displaying all employees for that department.

Nested reports don't always have to exist in the detail band. Figure 2 displays a master DataWindow grouped by the state column. The group band contains a nested report that has been set up to accept state as an argument. If you place a nested report in any band except the detail band, the autoheight property of the nested report becomes meaningless as the detail band is the only DataWindow band that will adjust its size to fit its contents. Also, note that when adjusting the height or width of a nested report or band, it may be necessary to save changes, exit the DataWindow painter, and then reenter to see the formatted changes.

The previous examples demonstrate how to use a nested report when a master/detail relationship exists. Actually, no relationship needs to exist at all. A nested report can exist in any band within the DataWindow. Where a nested report exists and what it displays is up to you. The DataWindow in Figure 3 contains a nested report in the header band. As long as the nested report does not require a retrieval argument, this is perfectly valid.

When placing nested reports in bands other than the detail band there are times when you may want to repress the display of the nested report. For example, rather than displaying the nested report at the top of every page, only show it on the first page. This will be discussed later in this column series when I cover DataWindow bands.

Nested Reports -
How Does the Data Get Retrieved?

When running or previewing a DataWindow containing a nested report, the actual number of database retrievals is determined by which band the nested report is in. First, the data is retrieved for the base DataWindow. If the detail band contains a nested report, each row of the detail band will retrieve the nested report that is associated with it. If the detail band contains 100 lines, 100 retrieves will be performed for the nested report. Nested reports not in the detail band are retrieved once for however many times that band exists.

Nested Reports - Accessing Objects
Since a nested report is not a DataWindow child, objects within the nested report cannot be accessed with a getchild() method. To set object properties (including expressions), dot notation must be used. The syntax is:

dwcontrol.Object.nestedreportlname { [row ] }
.Object.dwcontrolname.property { .property } { = value }

For example, the following code will set the expression for the salary column within the nested report. The syntax below will set the expression for all instances of the nested report dw_emps:

dw_1.object.dw_emps.Object.salary.color =
'255 ~t If(salary > 50000, 255, 0)'

It's possible to access and change properties in a nested report object if they are dependent on a value in the base DataWindow. The following code example loops through all rows in the base DataWindow; if the department ID is equal to 100, the nested report for that specific row is accessed. An expression is then applied to the salary column. Notice the syntax below the row of the nested report is being referenced.

integer li_row
FOR li_row = 1 to dw_1.RowCount()
IF String(dw_1.Object.dept_id.Primary[li_row]) = "100" THEN
dw_1.Object.dw_nested_rpt[li_row].Object.salary.Color = &
'255 ~t If(salary > 60000, 255, 0)'
END IF
NEXT

If the report does not have a row associated with it, no row specification is needed when accessing the nested report. The following code is valid if accessing a nested report in the header, footer, or summary band within the base DataWindow. If the report is in a header of a trailer band, the row number for the band must be specified.

dw_1.object.dw_nested_report.Object.state.color = &
'255 ~t If(state <> ~'IL~', 255, 0)'

Nested Reports and the getchild() Method
Earlier, I mentioned that report objects nested within a DataWindow are not DataWindow children, thus any call to getchild() will fail. There is a way around this. In the source editor, open the base DataWindow and change "processing=0" (or whatever it is) to "processing=5". This is the actual trick to change the base DataWindow's presentation style to "composite". The getchild function can now be used to access the nested report.

This is a cool trick because it opens up all methods that can be called for DataWindow children. A drawback of this method is that since the base DataWindow's presentation style has been changed to composite and composite windows have no data source, the base DataWindow's data source cannot be changed unless processing is changed back from "5". Also, it should be pointed out that the above technique is not supported by Sybase and should be used with extreme caution.

Composite Reports
An alternative to using nested reports is the use of the composite DataWindow presentation style. A composite DataWindow is really a collection of unrelated DataWindows (see Figure 4). Composite DataWindows by nature do not have a data source; the data comes from the data source of the composite DataWindow objects.

Retrieval Considerations
Even though a composite DataWindow has no data source, if all enclosed DataWindows have no retrieval arguments, all may be retrieved via one Retrieve() function. The following code will retrieve all DataWindows within a composite report.

dw_1.SetTransObject(SQLCA)
dw_1.Retrieve()

This approach works well when all nested DataWindows within the composite report have no retrieval arguments. If retrieval arguments are needed, there are two ways they can be specified. First, they can be supplied to the DataWindow child. For example, let's say the composite DataWindow in Figure 4 takes two retrieval arguments:

 

Nested DataWindow: Retrieval Argument:
d_cust_list State (String)
d_product_graph Region (String)

The code to retrieve them would be:

DataWindowChild ldwc_cust
DataWindowChild ldwc_product
dw_1.GetChild("dw_cust",ldwc_cust)
dw_1.GetChild("dw_product",ldwc_product)
ldwc_cust.SetTransObject(SQLCA)
ldwc_product.SetTransObject(SQLCA)
ldwc_cust.Retrieve("IL")
ldwc_product.Retrieve("Central")

Notice when obtaining a handle to the DataWindow child, the actual name of the nested report is specified, not the DataWindow object associated with it. Furthermore, for the sake of brevity and readability, return codes were not checked in the this code example. Because the nested DataWindows are truly DataWindow children, a GetChild() method can be called to obtain their handle. Any method that is valid for a DataWindow child may now be used.

If you don't want or need to obtain the nested reports as DataWindow children, there's a way in which they may be retrieved directly - without obtaining their child handle. Retrieval arguments can be specified within the DataWindow Painter as a general property of the composite DataWindow (see Figure 5).

Now that the retrieval arguments are a general property, they need to be plugged into the nested DataWindows. From within the DataWindow Painter, right-click on the nested report and supply its retrieval argument under the General Properties tab (see Figure 6).

This figure ties in the "state" retrieval argument for the nested report to the "state" retrieval argument that was supplied in Figure 5 as a general property of the composite DataWindow. The same steps should be taken to supply the "region" argument to the other nested report, but the steps are redundant and so won't be repeated here.

The final step in this method of composite DataWindow retrieval is the actual code:

dw_1.SetTransObject(SQLCA)
dw_1.Retrieve("IL","Central")

Notice that when retrieving nested reports (for composite DataWindows) in this fashion, you don't have to programmatically obtain the DataWindow child.

Dynamic Nested Reports
The DataWindow object of the nested report does not have to be defined at design time - it can be dynamic. The following PowerScript determines the DataWindow object as well as any arguments.

dw_1.SetTransObject(SQLCA)
If gs_userid = "MANAGER" Then
dw_1.Modify("dw_product.DataObject = ~"d_product_graph~"")
Else
dw_1.Modify("dw_product.DataObject = ~"d_product_by_region~"")
dw_1.Modify("dw_product.Nest_Arguments = ((~"'Central'~"))")
End If
dw_1.Retrieve()

In this code, the nested report named dw_product will contain the DataWindow Object d_product_graph if the user logged on is MANAGER. All other users will see the DataWindow object d_product_by_region, which takes a string argument in which we are supplying the value "Central".

Notice how the tilde character was used to denote the double quote character within a string. The DataObject property for the nested report looks and acts the same way as it would for a DataWindow. What is noteworthy is the Nest_Arguments property for the nested report. This property is used to supply either a literal value, expression, or a column value as a retrieval argument(s) for a nested report. The number of values provided in the list has to match the number of retrieval arguments defined for the nested report. Even though this example is in the composite report section, it will work for nested reports as well - that is, when a DataWindow is not of the composite report type but does include nested reports. The syntax for supplying dynamic retrieval arguments via the Nest_Arguments property follows:

( ("arg1") {,("arg2") {,("arg3") {,... } } } )

Notice that this list is not a quoted string. The whole list is surrounded by an open and a close parenthesis, respectively. Each argument is also surrounded by an open and a close parenthesis. Finally, the argument is enclosed by quotes. Also, the way the argument is treated by PowerBuilder differs depending on how it's presented. Sound confusing? It is. The following examples should help simplify this.

Using Expressions or Column Values
When the argument is equal to a database column (in a nested report that is not a composite DataWindow), its value is supplied in double quotes. The following code example passes the current value of the cust_id column as a retrieval argument.

dw_1.Modify("dw_product.Nest_Arguments = ~"cust_id~"")

Using String Values
String values must be entered in single quotes within the double quotes:

dw_1.Modify("dw_product.Nest_Arguments = ((~"'Central'~"))")

Using Numeric Values
When using numbers, omit the single quotes. Notice that the literal value 100 is still surrounded by double quotes. Remember the double quotes denote the value for the retrieval argument for the nested report; they do not denote a "string".

dw_1.Modify("dw_product.Nest_Arguments = ((~"100~"))")

Prompting the User for Retrieval Arguments
When more than one argument is needed, they are separated by commas. If not enough (or too many) arguments are defined, a runtime error will occur. If you want to prompt the user to enter the retrieval arguments at runtime, specify and empty parentheses like:

dw_1.Modify("dw_product.Nest_Arguments = ()")

How Dynamic Can We Get?
When using nested reports in a composite DataWindow or one of a different presentation style, the nested report does not need to exist at design time; it can be created dynamically. The following code example dynamically creates a nested report:

String ls_modstring
String ls_rc

ls_modstring = 'create report(band=summary dataobject="d_composite_tab" ' + &
'x="1083" y="12" height="796" width="1001" border="0" ' + &
'height.autosize=yes criteria=""
trail_footer = yes name=dw_nested_2 ' + &
'visible="1" slideup=directlyabove)'
dw_1.Modify(ls_modstring)

The nested report named dw_nested_2 uses d_composite_tab as its DataWindow object. It's important to point out that even though nested reports can be created dynamically, the DataWindow objects that they are associated with cannot. In this example, d_composite_tab must already exist. It can't be created dynamically via a SyntaxFromSQL() function. Although SyntaxFromSQL can be used to dynamically create a DataWindow object to be used as the source of a DataWindow or DataStore, it's no help to us when using nested reports.

Sharing Data Between Reports
Because nested reports within a composite DataWindow are truly DataWindow children, the same result set can be shared among them with a ShareData() method. Of course, the column sets must match in all of the nested DataWindows that share the result set. Sharing a result set is much faster than retrieving all nested reports individually as only one database retrieve needs to be performed. The following code retrieves a result set into a DataStore, then shares it with two nested reports:

DataStore lds_prod_info
DataWindowChild ldwc_nest1
DataWindowChild ldwc_nest2
lds_prod_info = CREATE DataStore
lds_prod_info.DataObject = "d_composite_tab"
lds_prod_info.SetTransObject(SQLCA)
lds_prod_info.Retrieve()
dw_1.GetChild("dw_nested_1",ldwc_nest1)
dw_1.GetChild("dw_nested_2",ldwc_nest2)
lds_prod_info.ShareData(ldwc_nest1)
lds_prod_info.ShareData(ldwc_nest2)

Notice the composite DataWindow does not require a transaction object or a database retrieve. The DataStore handles database transactions. Remember that each time data is retrieved into the composite DataWindow, all references to DataWindow children are lost. Always make sure to reestablish the child handle with a new call to the GetChild() function before trying to share data.

Limitations on Sharing Data
Using the ShareData() function produces some unexpected results on nested reports. When you use ShareData() with nested DataWindows that are part of a composite DataWindow, there are some instances where the ShareData will not work. Use the following rules of thumb:

  • Sharing data from a DataWindow or a DataStore with a nested report - okay
  • Sharing data from a nested report with a DataWindow or DataStore - will not work
  • Sharing data from a nested report with another nested report - will not work

    These three rules apply to all nested reports, regardless of whether or not they are being used in a composite DataWindow presentation style. A good way to get around this problem is to use RowsCopy when the ShareData() function will not work. An example of the RowsCopy() function is:

    dw_1.RowsCopy (1, 1, primary!, dw_2, 1, primary! )

    Of course, whenever you call a ShareData() or RowsCopy() function, both the source and destination column sets must match.

    Limitations Nesting/Composite
    Usually nested reports tend to be of the more common DataWindow presentation styles such as FreeForm, Tabular, or Grid. For the most part you're allowed to nest the various types of DataWindow presentation styles. However, significant limitations apply to two of them.

  • Crosstab: You can't place a crosstab with retrieval arguments within another report as a related nested report. However, you can include a crosstab in a composite report.
  • RichText: RichText reports cannot be nested in any way. You are not allowed to place a RichText report in another report and you cannot place a RichText report within a composite DataWindow.

    What's the Difference?
    Composite vs Nested

    Nested reports are not a DataWindow style, but DataWindow objects used within DataWindows. Nested reports are usually hierarchical - they have a detail relationship with the base DataWindow. To create a nested report, open the base DataWindow, then place a report object in the desired DataWindow band. The nested report can be linked to the base DataWindow by setting its retrieval argument property. The criteria for nested report retrieval can be a column value (in the base DataWindow) or a constant.

    When retrieving a base DataWindow containing a nested report, the base DataWindow is retrieved first, then each of the nested reports. In most circumstances, nested re-reports are not considered DataWindow children, therefore they cannot be accessed via a GetChild() method. There is an unsupported workaround that will allow the programmer to treat the nested report as a true DataWindow child.

    Composite reports are a DataWindow presentation style that provides the programmer with a place to put unrelated mutually independent DataWindow objects. When run, each DataWindow is retrieved independently; no relationship exists with either the base DataWindow or any other DataWindow within the composite report presentation style. When supplying retrieval arguments, they must be supplied in the code.

    Unlike using nested reports, the composite report presentation style treats the contained DataWindow objects as DataWindow children so the GetChild() method can be used to access the report and any objected contained within it. Just remember to name the report objects in the composite DataWindow if you want to obtain its handle via a GetChild() method. Similar to using nested reports, the composite report DataWindow presentation style is used when the data is read only. When the user runs a DataWindow that either contains nested reports or was built with the composite report presentation style, the base DataWindow is rendered in Print Preview mode. Both composite and nested reports are true reports and are, therefore, never updateable.

  • More Stories By Bob Hendry

    Bob Hendry is a PowerBuilder instructor for Envision Software Systems and a frequent speaker at national and international PowerBuilder conferences. He specializes in PFC development and has written two books on the subject, including Programming with the PFC 6.0.

    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.