April Meeting – PHPReports

This month we will about a little class for generating reports using php. If you are really interested in new and exciting ways to separate your data layer from your presentation layer…read on.

I was a bit pressed for time, so most of this content was taken either from the README or the phpreports site. The readme really is an excellent tour of the system, so if you are interested in how to use PHPReports, I would highly recommend you read it. Now, with that out of the way…

Requirements

You will need PHP compiled with XML/XSLT support. The PHPReports author uses the Sablotron libs in his work and examples.

Hello World

The PHPReports version of the classic Hello World, would be a simple table of database information. Lets take a look at the PHPReports Simple Sample before we get to far.

Document Structure

First off, we need to talk terminology. The author defines three key divisions or sections of a report:

  • The Document Layer
  • The Page Layer
  • The Group Layer

The author describes these best:

    You just have one document layer, one page layer (you can have a lot of pages, but just one page layer to configure) and some group layers.

    All these layers collect information about the data on your report, like the number of lines, statistics about the fields and so on.
    The document layer stores ALL these statistics, and stores it till the report end.

    The page layer stores it till the page end, and reset it there.

    The group layer stores it till the end of group, (where group is a set of data defined by a break expression, which could be any kind of field contained on your data set.)

    So, to visualize it in ascii art, we might use something like this:

    
        +----------------------------------+
        | DOCUMENT_LAYER                   |
        +----------------------------------+
        | HEADER                           |
        |                                  |
        | +------------------------------+ |
        | | PAGE_LAYER                   | |
        | +------------------------------+ |
        | | HEADER                       | |
        | |                              | |
        | | +--------------------------+ | |
        | | | GROUP LAYER              | | |
        | | | break expression A       | | |
        | | +--------------------------+ | |
        | | | HEADER                   | | |
        | | | FIELDS                   | | |
        | | |                          | | |
        | | | +----------------------+ | | |
        | | | | GROUP LAYER          | | | |
        | | | | break expression A,B | | | |
        | | | +----------------------+ | | |
        | | | | HEADER               | | | |
        | | | | FIELDS               | | | |
        | | | | FOOTER               | | | |
        | | | +----------------------+ | | |
        | | | FOOTER                   | | |
        | | |                          | | |
        | | +--------------------------+ | |
        | |                              | |
        | | FOOTER                       | |
        | +------------------------------+ |
        |                                  |
        | FOOTER                           |
        +----------------------------------+
    
    

    Or, if you prefer something a bit less abstract:

    
        +---------------------------------------+----+
        | John Doe Enterprises                  |////+--- document layer
        | Sales Report                          |////|
        +------+--------------------------------+----+
        | city | <city goes here>               |\\\\|
        +------+-+------+-------------+---------+\\\\|
        | id   | name   | product     | $       |\\\\|
        +------+--------+-------------+---------+\\\\+--- group layer
        | <id> | =name= | <product>   | <value> |\\\\|
        +------+--------+-------------+---------+\\\\|
        |                       total | <total> |\\\\|
        +-----------------------------+---------+----+
        |                  page total | <total> |////+--- page layer
        +-----------------------------+---------+----+
        |                report total | <total> |\\\\+--- document layer (again)
        +-----------------------------+---------+----+
    
    

    So what does my php script look like? It’s Massive right??

    
        include 'PHPReportMaker.php';
        $sql  = "select ID,NAME,CITY,PRODUCT,VALUE from SalesLog order by ID";
        $parm = Array();
        makeReport( "sales.xml", "PHPReport.xsl", "user", "mypass", 
                "mydb", "mysql", $sql, $parm );
    

    Or…If you prefer the “cleaner” OO form:

    
    <?php
        include 'PHPReportMaker.php';
        $sql  = "select ID,NAME,CITY,PRODUCT,VALUE from SalesLog order by ID";
        $oRpt = new PHPReportMaker();
    
        $oRpt->setXML("sales.xml");
        $oRpt->setUser("user");
        $oRpt->setPassword("mypass");
        $oRpt->setConnection("mydb");
        $oRpt->setDatabaseInterface("mysql");
        $oRpt->setSQL($sql);
        $oRpt->run();
    ?>
    
    

    sales.xml – The format spec for our report

    You know that that would just be too easy, right. Do you see the “sales.xml” string above? That points to an xml file that contains the format spec for our report. Think of it as a template for our data.

    
    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <REPORT>
       <TITLE>Sales eport</TITLE>
      <BACKGROUND_COLOR>#FFFFFF</BACKGROUND_COLOR>
       <PAGE BORDER="1" SIZE="25" CELLSPACING="0" CELLPADDING="5" WIDTH="500">
          <HEADER>
             <ROW>
                <COL COLSPAN="5">John Doe Enterprises</COL>
             </ROW>
             <ROW>
                <COL COLSPAN="5">Sales Report</COL>
             </ROW>
          </HEADER>
          <FOOTER>
             <ROW>
                <COL ALIGN="RIGHT" COLSPAN="4">page total</COL>
                <COL ALIGN="LEFT" NUMBERFORMATEX="2" TYPE="EXPRESSION">$this->getSum("value")</COL>
             </ROW>
          </FOOTER>
       </PAGE>
       <GROUPS>
          <GROUP NAME="maingroup">
             <FIELDS>
                <ROW>
                   <COL>id</COL>
                   <COL>name</COL>
                   <COL>city</COL>
                   <COL>product</COL>
                   <COL>value</COL>
                </ROW>
             </FIELDS>
          </GROUP>
       </GROUPS>
    </REPORT>
    

    This xml might show us a report that looks a lot like the following:

    Where’s the REAL code?? Some more complete examples

    There is a how-to example on the phpreports website that illustrates this topic. It also includes the complete XML file for generating the report.

    There are a host of other Samples available on their site.
    Be sure to check out their Super Swanky Report as well as the Debugging section. Yes, it has it’s own debugging helper.