Reports

From FiFormsWiki

Jump to: navigation, search

FiForms has an advanced, multi-stage report generation system. Report definitions, like form definitions, are stored as XML files in the reports directory under the application. (See the Schema Reference, and XML Schema for Report Definitions)

Contents

Report Generation Process

A FiForms report consists of one or more SQL queries, along with substitutable parameters and formatting information. FiForms uses a multi-stage report generation process, providing a very flexible and versatile interface for developers.

FiForms first reads the information contained in the definition and generates a page, requesting any necessary parameters from the user. At this stage, users are also able to select the output format of the report, which determines the filters that are applied during generation.

When the form is submitted (via HTTP GET Request), FiForms reads the report definition, substitutes parameters contained in the SQL with values submitted by the user, and executes the SQL script against the server. In the first phase of output, the data from MySQL is always formatted into a generalized XML. (See the Schema Reference, Schema) Then, that XML is passed through various processors (XSLT or custom PHP) which generate the final output.

Example

Suppose we have the following data in a table called "books":

+---------+-----------+--------------------------------+---------------+----------------+
| BOOK_ID | AUTHOR_ID | BOOK_TITLE                     | BOOK_ISBN     | BOOK_COPYRIGHT |
+---------+-----------+--------------------------------+---------------+----------------+
|       4 |         7 | Introduction to Unix and Linux | 0-07-222694-3 |           2003 |
|       3 |         9 | A+ Guide to Hardware           | 0-619-18624-0 |           2002 |
|       5 |         7 | Introduction to Physics        | 0-123-45678-0 |           2009 |
+---------+-----------+--------------------------------+---------------+----------------+


... and we create the following report definition, and save it as book_listing.xml in the reports directory of our application:

<reportdef xmlns="http://xml.fiforms.org/FiForms/" version="1.2"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://xml.fiforms.org/FiForms/ 
        http://xml.fiforms.org/schema/12/FiForm.xsd">
 <title>List Books by Category</title>
 <summary>A Demonstration of the Capabilities of FiForms</summary>
 <copyright>Copyright (C) 2008-2009 by FiForms Solutions</copyright>
 <connect db="fiforms_sample" type="MySQL" server="localhost" />
 <param name="title_filter" value="%" caption="Filter Titles" />
 <query resultname="Books">
   <sql>

     SELECT BOOK_TITLE, BOOK_ISBN, BOOK_COPYRIGHT 
     FROM books 
     WHERE BOOK_TITLE LIKE "%title_filter%"

   </sql>
 </query>
</reportdef>

We will add the following element to one of our menus, such as main.xml:

<rpt href="book_listing.xml"/>

When we open this report, it prompts for the title filter and format:

Books2-examplereport.png

Note the % character in the filter, which is interpreted by SQL as a wildcard character. We have changed the filter in the screenshot so that only books whose title starts with "Intro" are included. The parameter and caption are specified in the XML using the <param> element, and then substituted in the SQL at each occurance of %title_filter%. Note that the name attribute of the param element is the parameter name, which is enclosed in % symbols in the SQL code below. The % symbols in this case are merely delimeters--they are not passed to the database. If you wanted the % symbols to be passed to the database, you would have to double them, such as %%title_filter%%.

The server queries the database, finding two records that match the filter, and generates the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://xml.fiforms.org/FiReports/">
  <title>List Books by Category</title>
  <parameters>
    <param name="filename" value="book_listing.xml" />
    <param name="app" value="books" />
    <param name="_TYPE" value="_xml" />
    <param name="title_filter" value="Intro%" />
    <param name="EMAIL_REPORT" value="" />
  </parameters>
  <dataset name="Books" >
    <row id="1">
      <field name="BOOK_TITLE" t="string">Introduction to Unix and Linux</field>
      <field name="BOOK_ISBN" t="string">0-07-222694-3</field>
      <field name="BOOK_COPYRIGHT" t="int">2003</field>
    </row>
    <row id="2">
      <field name="BOOK_TITLE" t="string">Introduction to Physics</field>
      <field name="BOOK_ISBN" t="string">0-123-45678-0</field>
      <field name="BOOK_COPYRIGHT" t="int">2009</field>
    </row>
  </dataset>
  <messages>
  </messages>
</report>

You can view the raw XML, as above, by selecting the XML output type. Assuming you left the default output type selected, however, the report engine will process the XML using XSLT to produce an HTML report that looks like this:

Books3 report example.png

Simple Template

To get started quick-and-easy with your own report, here is a simple little template you can start with:

<reportdef xmlns="http://xml.fiforms.org/FiForms/" version="1.2"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://xml.fiforms.org/FiForms/ 
        http://xml.fiforms.org/schema/12/FiForm.xsd">
 <title></title>
 <summary></summary>
 <copyright></copyright>
 <connect db=""/>
 <query resultname="">
   <sql>

     SELECT
     FROM 

   </sql>
 </query>
</reportdef>

Query Syntax

Multiple Queries

You can place multiple queries in the same report definition, one after the other. For example, if we want to include a list of authors in the report, we can add the query in beside the book query.

We have another table, authors, with the following data in it:

+-----------+------------------+-----------------+------------------+------------+
| AUTHOR_ID | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME | AUTHOR_BIRTHDATE | AUTHOR_BIO |
+-----------+------------------+-----------------+------------------+------------+
|         9 | Jean             | Andrews         | NULL             | NULL       |
|         8 | Lee M.           | Cottrell        | NULL             | NULL       |
|         7 | Catherine        | Creary          | NULL             | NULL       |
+-----------+------------------+-----------------+------------------+------------+

So we will add a query to list all this information in our report.

<reportdef xmlns="http://xml.fiforms.org/FiForms/" version="1.2"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://xml.fiforms.org/FiForms/ 
        http://xml.fiforms.org/schema/12/FiForm.xsd">
 <title>List Books by Category</title>
 <summary>A Demonstration of the Capabilities of FiForms</summary>
 <copyright>Copyright (C) 2008-2010 by FiForms Solutions</copyright>
 <connect db="fiforms_sample" type="MySQL" server="localhost" />
 <param name="title_filter" value="%" caption="Filter Titles" />
 
 <query resultname="Authors">
   <sql>
     SELECT * FROM authors
   </sql>
 </query>
 
 <query resultname="Books">
   <sql>

     SELECT BOOK_TITLE, BOOK_ISBN, BOOK_COPYRIGHT 
     FROM books 
     WHERE BOOK_TITLE LIKE "%title_filter%"

   </sql>
 </query>
</reportdef>

Nested Queries

Running multiple queries works well, but often we want to arrange our data based on some type of parent-child relationship within the data. SQL is very good at flattening related tables into a single result set, so we will not demonstrate that here. FiForms Reports provides a query nesting feature which will generate a hierarchy of nested result sets in the output. When a query is nested inside another query, the outside query is run first. Then the inside query is run once for every row in the outside query. All fields from the outside query are available as parameters that can be substituted in the inside query, allowing one-to-many joins between tables to be mapped directly to nested XML output.

<reportdef xmlns="http://xml.fiforms.org/FiForms/" version="1.2"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://xml.fiforms.org/FiForms/ 
        http://xml.fiforms.org/schema/12/FiForm.xsd">
 <title>List Books by Category</title>
 <summary>A Demonstration of the Capabilities of FiForms</summary>
 <copyright>Copyright (C) 2008-2010 by FiForms Solutions</copyright>
 <connect db="fiforms_sample" type="MySQL" server="localhost" />
 <param name="title_filter" value="%" caption="Filter Titles" />
 
 <query resultname="Authors">
   <sql>
     SELECT AUTHOR_ID, AUTHOR_FIRSTNAME, AUTHOR_LASTNAME FROM authors
   </sql>

   <query resultname="Books">
     <sql>
 
       SELECT BOOK_TITLE, BOOK_ISBN, BOOK_COPYRIGHT 
       FROM books 
       WHERE BOOK_TITLE LIKE "%title_filter%"
       AND AUTHOR_ID="%AUTHOR_ID%"
 
     </sql>
   </query>
 </query>
</reportdef>

The XML output from this query would look something like this:

<?xml version="1.0"?>                                                                                 
<report>                                                                                               
 <title>List Books by Category</title>                                                                 
 <parameters>                                                                                          
   <param name="filename" value="book_nested.xml"/>                                                    
   <param name="app" value="books"/>                                                                   
   <param name="_TYPE" value="_xml"/>                                                                  
   <param name="title_filter" value="%"/>                                                              
   <param name="EMAIL_REPORT" value=""/>                                                               
 </parameters>                                                                                         
 <dataset name="Authors">                                                                              
   <row id="1">                                                                                        
     <field name="AUTHOR_ID" t="int">9</field>                                                         
     <field name="AUTHOR_FIRSTNAME" t="string">Jean</field>                                            
     <field name="AUTHOR_LASTNAME" t="string">Andrews</field>
     <dataset name="Books">
       <row id="1">
         <field name="BOOK_TITLE" t="string">A+ Guide to Hardware</field>
         <field name="BOOK_ISBN" t="string">0-619-18624-0</field>
         <field name="BOOK_COPYRIGHT" t="int">2002</field>
       </row>
     </dataset>
   </row>
   <row id="2">
     <field name="AUTHOR_ID" t="int">8</field>
     <field name="AUTHOR_FIRSTNAME" t="string">Lee M.</field>
     <field name="AUTHOR_LASTNAME" t="string">Cottrell</field>
     <dataset name="Books">
     </dataset>
   </row>
   <row id="3">
     <field name="AUTHOR_ID" t="int">7</field>
     <field name="AUTHOR_FIRSTNAME" t="string">Catherine</field>
     <field name="AUTHOR_LASTNAME" t="string">Creary</field>
     <dataset name="Books">
       <row id="1">
         <field name="BOOK_TITLE" t="string">Introduction to Unix and Linux</field>
         <field name="BOOK_ISBN" t="string">0-07-222694-3</field>
         <field name="BOOK_COPYRIGHT" t="int">2003</field>
       </row>
       <row id="2">
         <field name="BOOK_TITLE" t="string">Introduction to Physics</field>
         <field name="BOOK_ISBN" t="string">0-123-45678-0</field>
         <field name="BOOK_COPYRIGHT" t="int">2009</field>
       </row>
     </dataset>
   </row>
 </dataset>
 <messages/>
</report>

Formatting

Reports can be output using the default format, as in the examples above, or they can be formatted using a simplified HTML-style layout and CSS.

See the Report Formatting article for examples and documentation on report formatting.

Output Types

Raw XML

All reports are first generated to a special FiForms XML format, as demonstrated above. By selecting the Raw XML output, you can download this raw XML to the client. This is very useful for debugging output, and for developing new custom report types using XSLT.

Formatted Output

HTML

FiForms comes with a feature-rich XSLT stylesheet to output reports as HTML. Without any customization, data is formatted in HTML tables. With a few simple options, sophisticated reports can be designed using HTML and CSS.

PDF

FiForms contains experimental PDF report formatting (as of version 1.2). The same formatting information is used to lay out simple but high-quality reports ready for printing.

Unformatted and Custom Output

Spreadsheet (CSV)

Any report can be flattened into a spreadsheet in CSV format. Multiple queries are output one after the other in the same sheet, and nested queries are flattened similar to SQL joins (outer rows are duplicated for each inner row).


Custom XSLT

The Raw XML output of the report can be transformed using any custom XSLT stylesheet. A link to the stylesheet should be specified in the report definition, following the <param /> elements, like this:

<stylesheet href="name_of_stylesheet.xsl" caption="My Custom Stylesheet" type="application/xslt+xml" mode="transform" />

If mode="transform", then the transformation takes place on the server side. If mode="link" then the raw XML is served to the client, with a link to the stylesheet. The recommended method is mode="transform", as browser implementations of XSLT vary, and some browsers do not support it at all.

Personal tools