Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


Breck Carter
Last modified: December 23, 1996
mail to: bcarter@bcarter.com
[Home]



Tip 57: Moving The Unmovable

How can I move a field to the bottom of the last page in a DataWindow? That's where I need to put a signature line in a printed report.

The summary band is a good place to start because it appears only once at the end of the report. Unfortunately, the summary band normally appears immediately after the detail data. If the last page is half full the summary band will appear in the middle of the page.

One approach is to expand the summary band to fill the rest of the page and push the signature line down to the bottom of the band, as shown in Figure 1.

Figure 1: Signature Line At The Bottom


Here's how it can be done, step by step:
  • Turn on print preview so the size of the DataWindow page will agree with the final printed copy.

  • Scroll to the last page so that expressions involving FirstRowOnPage and LastRowOnPage will return values that apply to the last page.

  • Calculate the total height of the header band plus all the detail bands on the last page. Call describe() to get values for the DataWindow.header.height, detail.height, FirstRowOnPage and LastRowOnPage, and use these to do the calculation.

  • Call modify() to expand the summary band to fill the last page by changing the value of DataWindow.summary.height.

  • Call modify() to move the signature field to the bottom of the expanded summary band by changing the value of signature.y.

Figure 2 shows the corresponding steps in PowerScript.

Figure 2: Moving The Summary Signature Field
 integer li_RC
 long    ll_row_count
 string  ls_error
 long    ll_first_row
 long    ll_last_row
 long    ll_header_height
 long    ll_detail_height
 long    ll_page_height
 long    ll_summary_height
 long    ll_signature_y

 // Fill the DataWindow.

 li_RC = this.dw_page.SetTransObject ( SQLCA )
 if li_RC <> 1 then
    MessageBox ( "Error", "SetTransObject failed" )
    halt close
 end if

 ll_row_count = this.dw_page.retrieve()
 if ll_row_count < 0 then
    MessageBox ( "Error", "Retrieve failed" )
    halt close
 end if

 // Turn on print preview so the size of the DataWindow
 // page will agree with the final printed copy.

 ls_error = this.dw_page.modify &
    ( "DataWindow.print.preview=yes" )
 if ls_error <> "" then
    MessageBox ( "Error", "Modify print.preview failed" )
    halt close
 end if

 // Make the last page the visible one so that expressions
 // involving FirstRowOnPage and LastRowOnPage will
 // return values that apply to the last page.

 li_RC = this.dw_page.ScrollToRow &
    ( this.dw_page.RowCount() )
 if li_RC <> 1 then
    MessageBox ( "Error", "ScrollToRow failed" )
    halt close
 end if

 // Get the total height of the header band plus all the detail
 // bands on the last page.

 ll_header_height = long ( this.dw_page.describe &
    ( "DataWindow.header.height" ) )
 if ll_header_height <= 0 then
    MessageBox ( "Error", "Describe header.height failed" )
    halt close
 end if

 ll_first_row = long ( this.dw_page.describe &
    ( "DataWindow.FirstRowOnPage" ) )
 if ll_first_row <= 0 then
    MessageBox ( "Error", "Describe FirstRowOnPage failed" )
    halt close
 end if

 ll_last_row = long ( this.dw_page.describe &
    ( "DataWindow.LastRowOnPage" ) )
 if ll_last_row <= 0 then
    MessageBox ( "Error", "Describe LastRowOnPage failed" )
    halt close
 end if

 ll_detail_height = long ( this.dw_page.describe &
    ( "DataWindow.detail.height" ) )
 if ll_detail_height <= 0 then
    MessageBox ( "Error", "Describe detail.height failed" )
    halt close
 end if

 ll_page_height &
    = ll_header_height &
    + ( ll_detail_height &
     * ( ll_last_row - ll_first_row + 1 ) )

 // Expand the summary band to fill the last page.
 // The magic number 4050 is a few units smaller than the
 // full page size to avoid problems printing the last page.
 // This number may require adjustment by trial and error.

 ll_summary_height = 4050 - ll_page_height

 ls_error = this.dw_page.modify &
    ( "DataWindow.summary.height=" &
       + string ( ll_summary_height ) )
 if ls_error <> "" then
    MessageBox ( "Error", "Modify summary.height failed" )
    halt close
 end if

 // Move the signature field to the bottom of the
 // expanded summary band.
 // The magic number 60 was chosen to position the signature
 // field above the bottom edge of the summary band by a
 // sufficient margin to avoid problems printing the last page.
 // This number may require adjustment by trial and error.

 ll_signature_y = ll_summary_height - 60

 ls_error = this.dw_page.modify &
    ( "signature.y=" &
       + string ( ll_signature_y ) )
 if ls_error <> "" then
    MessageBox ( "Error", "Modify signature.y failed" )
    halt close
 end if

This example assumes a simple DataWindow with a header band and a few detail rows on each page plus a summary band at the end. The page height calculation will more complicated if group headers and trailers are being used.

If groups are involved an alternative approach might be more attractive: Put the signature field in the DataWindow footer band. Unlike the summary band, the footer automatically appears at the bottom of the page (that's good) but it also appears on every page (not so good). Figure 3 shows the expression to use in Properties - Expressions - Visible to make the signature field invisible on every page except the last.

Figure 3: Footer Signature Visible Only On Last Page

 if ( page() = pageCount(), 1, 0 )

The footer band may not be the ideal solution. Even though the signature line can be made invisible, the empty space is not available for printing detail rows. If that's unacceptable then the complexity of the summary band solution might not be so bad after all.


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.