PowerBuilder: Article

Dynamic Report Module Basics

Dynamic Report Module Basics

Now that the holidays are over, it's back to work, back to school...and back to the basics. In my December column, "PowerBuilder and ODBC 101" (PBDJ, Vol. 9, issue 12), we discussed hooking up to a database via ODBC.

This month, let's look at the most important item to hook up to a database, a DataWindow. Our apps are all about viewing data, and the DataWindow is the nucleus of all "data viewing" in PowerBuilder apps. (Source code for this article can be downloaded from www.sys-con.com/pbdj/sourcec.cfm.)

Since most DataWindows function as "reports," we'll use that as our model. Let's pretend our users want a window to view "reports." We can put this together using one window, one DataWindow control, one listbox, one object-level function, a few DataWindow objects, about a dozen lines of code, and some elbow grease. Only a dozen lines of code? We only need one DataWindow control? Yes, that's all we need if we use the dataobject property of the DW control dynamically at runtime.

Most folks starting out with PB get into a habit of using one DW control with one DW object defined at design time. Good for starting out, but not very flexible. With a little creativity, we can have so much more, for so much less. Fire up the new PC you got in December from your rich relatives and follow along with me:

  • Create a new workspace and target or just use ones you already have.
  • Create a main! window called "w_reports". Drop a DataWindow control and a listbox onto it. Leave the DW control named dw_1 and change the name of the listbox to lb_reports. Save the window.
  • Create three DataWindow objects using the sample database (EAS Demo DB V4).
    1.   dw_employee: A simple report showing all employees in alphabetical order
    2.   dw_customer: A simple report showing all customers in alphabetical order
    3.   dw_phonebook: A join between employees and department, grouped by department

    At this point, we have all the basic pieces to put together a report module. Let's review the steps required to get a DW "talking" to the database.

    1.   Initialize a transaction object (your own) or use SQLCA.
    2.   Populate the transaction object properties needed for your database.
    3.   Try connecting to the database.
    4.   Check the transaction object's SqlCode property to see if the connection was successful.
    5.   Set the dataobject property of the DataWindow control at design time or programmatically (dw_1.dataobject = '<dw object name>').
    6.   Connect the DW control to your transaction object (dw_1.SetTransObject (<tranx object name>). Be sure to check the return code for a successful connection.
    7.   Perform a Retrieve() or Update() on the DW control.

    I'll assume you already have SQLCA successfully connected to the database (if you don't, review my December article). We must use the <dw_control>. SetTransObject ( <tranx object> ) function. However, we must remember the following rule: if we change the dataobject property, the connection is broken and we must reset the DW control to the transaction object. We're going to do just that: change the dataobject property "on the fly" in our app, giving us a very flexible report tool. Let's start coding:

  • Open w_reports in the window painter.
  • Declare an instance variable of type DataWindow:
    datawindow idw_report
    - Make sure you save the window before leaving the instance variable area.
  • Create an object-level function called "of_runreport". Make it with one input parameter of type DataWindow. Code it as shown in Figure 1.
  • Select the listbox lb_reports. On the Properties panel, select the Items tab. We'll make our report "pick list". Create the items as follows:
    - Customer List
    - Employee List
    - Employee Phonebook
  • Code the SelectionChanged event of lb_reports to determine which report was selected by the user and retrieve it as follows:

    CHOOSE CASE index
    CASE 1 // Customer List of_runreport( 'dw_customer' )
    CASE 2 // Employee List of_runreport( 'dw_employee' )
    CASE 3 // Employee Phonebook of_runreport( 'dw_phonebook' )

    Run the application. The DW control will be empty initially. Just click on one of the reports. Each time you select a report, it will change the DW control dataobject property, reset the transaction object connection, and retrieve the data. Figure 2 shows the completed window. By using one DW control, the print button logic is simple: dw_1.Print ( ). Now you have a base report module. You may want to add a few "bells and whistles," such as radio buttons, to re-sort the data from ascending to descending, a button for filtering, and a button that turns on the "prompt for criteria" functionality.

    Changing properties "on the fly" (at runtime) is a key concept to master to take your PB apps to the next level. Happy coding!

  • More Stories By David Hart

    David Hart is a former Sybase Tools and Database Instructor. He is a certified PB developer and certified ASE DBA from Rancho Cucamonga, CA. He is currently on contract assignments in Southern California.

    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.