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.

Tip 57: Moving The Unmovable

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.