Application Techniques

Building a Matrix Style Report Using an AgoVectorRowCursor

The code shown on this page is outdated and should not be used. Do not use AgoVectorRowCursor to associate a vector with a row cursor as described on this page.

Instead, use AgoDataTreeManager, as shown in the live example for pgVectorMatrix.html. Click on pgVectorMatric.html in the table below to view the correct implementation.

About this technique



HTML Client Techniques> HTML-based views

You can run this technique code from:

NOTE   First make sure that database is running on your localhost SilverStream Server

Related reading

See the chapter on programming pages in the Programmer's Guide

This example shows how to associate a vector with a row cursor. The row cursor is used as the data provider for an HTML view on a page. The resulting report is a matrix that shows revenue data organized by category and year. The page provides a choice box that lets the user select a beginning year.

Declaring variables   Top of page

The page has a member variable for the AgoVectorRowCursor object as well as a member variable that defines the default beginning year. The page also declares constants that define the names of the columns in the matrix and the maximum number of years that the report can display:

  AgoVectorRowCursor m_rowCursor; 
     // vector row cursor to build the view 
  int m_iMinYear = 96; 
     // for this example, hard code the beginning year to 1996 
     // Our matrix column names. 
     // Note, the number of columns here need to match the  
        number of columns/labels in the view on the page 
  final static String COLUMN_NAMES[] = {"Category", // our column 
                                                    name list 
  final static int m_iMaxYearCount = COLUMN_NAMES.length - 2; 
                   // the number of column labels in the view 

Creating the AgoVectorRowCursor   Top of page

The pageLoaded event for the page creates an AgoVectorRowCursor object and sets its row description to the column name list for the view displayed on the page. In addition, it binds the labels in the view to the AgoVectorRowCursor columns.

  protected void pageLoaded(AgiHttpServletRequest req, AgiHttpServletResponse res) throws Exception 
    // Create an AgoVectorRowCursor and set it's row  
       descriptor to the column name list 
    m_rowCursor = new AgoVectorRowCursor(); 
    // Bind our labels in our view to the AgoVectorRowCursor  
       column names. 
    // First, the category name 
    agDataMgr.bind(lblCategoryName, "Text", "getText",  
       "setText", m_rowCursor, "Category"); 
    agDataMgr.bind(lblTotalForCategory, "Text", "getText",  
       "setText", m_rowCursor, "Total"); 
    // Now the yearly totals 
    for (int i = 0; i < m_iMaxYearCount; i++) 
       AgpLabel lblYear = (AgpLabel) getControl("lblTotalYear"  
         + (i + 1));  
       if (lblYear != null) 
         agDataMgr.bind(lblYear, "Text", "getText",  
       "setText", m_rowCursor, "Year" + (i + 1) ); 

Retrieving the data   Top of page

The page has a method called getTheData() that retrieves data from the categories, orders, products, and orderdetails tables, and generates revenue totals organized by category and year. The pageRequestBegin event for the page and the valueChanged event for the cbYear choice control both call the getTheData() method.

The getTheData() method uses a vector called vctRecords to keep track of the data in the matrix. Each element in the matrix is an object array called objRecord that represents the data for a particular product category. Each objRecord array contains up to five cells of data, one for the category name, one for the category total, and up to three other cells containing the revenue data for each year.

After all of the data has been processed, the getTheData() method sets the result set for the AgoVectorRowCursor object to the vctRecords vector and sets the data provider for the HTML view to the AgoVectorRowCursor object:

  public void getTheData() 
       * getTheData:  This method retrieves and totals revenue 
       * by category by year. 
       * / 
     // Create the Vector that will hold the records 
     Vector vctRecords = new Vector(); 
       // Loop through the records getting totals by year. 
       boolean bCategoryResults = dataCategories.gotoFirst(); 
       while (bCategoryResults)  
        // Each row will consist of a column for the category name, 
          and one for each year reported 
        Object objRecord[] = new Object[m_iMaxYearCount + 2]; 
        // get the quantity,price, and date, then calculate the 
           total revenue for the order 
        Integer intCategoryID = (Integer)  
           dataCategories.getProperty ("categoryid"); 
        if (DEBUG_ON) 
         System.out.println("CategoryID = " + 
        objRecord[0] = (String) 
           // get the data by Category by year and update the 
              graph buffer 
        BigDecimal bdCategoryRevenue[] =  
           iMinYear, iMaxYear); 
        BigDecimal bdTotalRevenue = new BigDecimal(0); 
        for (int i = 0; i < m_iMaxYearCount; i++) 
         objRecord[i + 1] = bdCategoryRevenue[i].toString(); 
         bdTotalRevenue = bdTotalRevenue.add(bdCategoryRevenue[i]); 
         bdYearlyTotals[i] =  
        if (DEBUG_ON) 
         System.out.println("====> Setting total Revenue"); 
        // Set the total revenue for this category for all  
           reporting years 
        objRecord[m_iMaxYearCount + 1] = bdTotalRevenue.toString(); 
        bCategoryResults = dataCategories.gotoNext(); 
       // Now we need to put out the yearly totals 
       if (DEBUG_ON) 
         System.out.println("====>setting Grand totals"); 
       BigDecimal bdGrandTotal = new BigDecimal(0); 
       Object objRecord[] = new Object[m_iMaxYearCount + 2]; 
       objRecord[0] = "Yearly Totals:"; 
       for (int i = 0; i < m_iMaxYearCount; i++) 
        if (DEBUG_ON) 
         System.out.println("Year " + i + " total = " + 
        objRecord[i + 1] = bdYearlyTotals[i].toString(); 
        bdGrandTotal = bdGrandTotal.add(bdYearlyTotals[i]); 
       objRecord[m_iMaxYearCount + 1] = bdGrandTotal.toString(); 
       // for debugging purposes only, display the row data 
       if (DEBUG_ON) 
        int iSize = vctRecords.size(); 
        for (int i = 0; i < iSize; i++) 
         System.out.println("====> Row: " + i); 
         Object objArray[] = ( Object[]) vctRecords.elementAt(i); 
         int iObjSize = objArray.length; 
         for (int j = 0; j < iObjSize; j++) 
          System.out.println("========> Column " + j + ": "  
            + objArray[j]); 
       // Set the resultset for the row cursor 
       // Tell the view where to get the data 
     catch (Exception e) 
      System.out.println("====> Get the Data failed.  Exception:  " 
       + e); 
      lblErrorMsg.setText("Get the Data failed.  Exception:  "  
       + e.toString()); 

Copyright © 2000, SilverStream Software, Inc. All rights reserved.