IDRS Tutorial

The aim of this tutorial is to explain how to setup and implement the IDRS using a sample setup. This tutorial will cover the steps necessary to add the IDRS to a servlet engine and how to connect it to a database. This tutorial DOES NOT cover setting up of a web server, jdk, rdbms or any other component of a web site infrastructure. These are what needs to be setup BEFORE you begin installing the IDRS:

For information as to which combinations of the above systems that have been certified to worth with the IDRS, please visit the IDRS Compatibility Page.

The IDRS is made up of these packages  

Number Class Name Description
1 net.sourceforge.idrs.core
Contains the core pieces of the IDRS including the RML parser and the servlet
2 net.sourceforge.idrs.utils
Contains the classes that include database access and calling object dynamicly
3 net.sourceforge.idrs.utils.pool
Used as the base for all pools in the IDRS
4 net.sourceforge.idrs.jdbc
Includes classes for a database pool
5
net.sourceforge.idrs.script
Classes for scripting the IDRS
6
net.sourceforge.idrs.embedable
Classes for embedding scripts into RML reports

Either the classes or idrs.jar now need to be inserted into a web application directory structure. The location of this structure is defined by the servlet executor, so consult your documentation. The web application tree is as follows

In this tree the {Web App Directory} is where the web application will be. This directory will include any static web pages or JSP pages that relate to the web application. The WEB-INF directory holds the web application's configuration file called web.xml, this document will be explained in the next section. The final directory is the classes directory, this is where the IDRS class files described above will go.

WEB.XML

The web.xml file is an xml file that tells the servlet runner where the web server can find the necessary classes for this web application and what initialization arguments are given to the servlet on startup and restart. The parameters are defined and explained by the IDRS Initialization Arguments page that is part of the IDRS Documentation Site. This document explains every piece of the IDRS' web.xml file and gives a sample web.xml file. A sample web.xml file is also included in the download of the IDRS.

IDRS DATA MODEL

The IDRS data model facilitates a dynamic model for report generation. The model is explained in the IDRS Data Model as part of the IDRS Documentation. to create the data model simply execute the statements in the IDRS Data Model. If you are running SQL Server you must change the type for the IsFile field in the IDRS' tblDoc table from boolean to int. Once the model is created the next step is to add users and groups to the model. Users may belong to multiple groups as explained in the IDRS Data Model, and reports may allow multiple groups to access the report.

CREATING A REPORT

Now we will create a sample report. The data for this report will just happen to sit in a PostgreSQL and all examples will pertain to PostgreSQL, but the type of database that is used doesn't matter. When finished our report will look like this :


This is my first IDRS Generated Report

John's Time Card

Hourly Rate : $25.00 / hour
 

Line Number Date Time In Time Out
1 22-May-2000 10:00 AM 3:00 PM
2 23-May-2000 11:30 AM 2:00 PM

Jack's Time Card

Hourly Rate : $10.00 / hour
 

Line Number Date Time In Time Out
3 22-May-2000 8:30 AM 5:00 PM
4 23-May-2000 8:00 AM 5:30 PM

Jen's Time Card

Hourly Rate: $8.50 / hour
 

Line Number Date Time In Time Out
5 22-May-2000 8:30 AM 4:30 PM
6 23-May-2000 8:22 AM 3:00 PM

John is the boss and you always listen to him/her.


The Data that supplies this report is in the employee database and contains the emp table :

CREATE TABLE emp (
ID int,
name varchar(10),
date date,
timein time,
timeout time,
payrate float);
 
 
 
 

id name date timein timeout payrate
1 John 22-May-2002 10:00 AM 3:00 PM 25.00
1 John 23-May-2002 11:30 AM 2:00 PM 25.00
2 Jack 22-May-2000 8:30 AM 5:00 PM 10.00
2 Jack 23-May-2000 8:00 AM 5:30 PM 10.00
3 Jen 22-May-2000 8:30 AM 4:30 PM 8.50
3 Jen 23-May-2000 8:22 AM 3:00 PM 8.50

This table is not a good example of a relational database, but for our purposes it will do just fine.

The final product has almost all of the pieces of the IDRS. It has tables that break when a data field changes, repeating colors in tables and it uses two datasets. While having two db's may seem like over kill, it will display how to use multiple DBs.

You can download the srource for this report by clicking here.


First we will look at how an RML template is structured.  There are two parts to an RML document, the head and the body.  They are defined by the <HEAD></HEAD>and <BODY></BODY>tags just as in HTML.  All of the information that defines the datasets to be used is placed in the head of the document and the placement of data is defined in the body of the document.  First, lets look at the head of the document:

<rnl>
<ishtml>true</ishtml>
<HEAD>
  <DB ID="emp">
    <DBDriver>dbType.Driver</DBDriver>
    <UserName>admin</UserName>
    <Password>asdf123</Password>
    <DBName>jdbc:dbType:employee</DBName>
    <SQL>
      <SRC>
        SELECT * FROM emp;
      </SRC>
    </SQL>
  </DB>

  <DB ID="boss">
    <useDB>emp</useDB>
    <SQL>
      <SRC>
        SELECT name FROM emp WHERE id = ?;
      </SRC>
      <varType>int</varType>
    </SQL>
  </DB>

</HEAD>

Preceding the <head>section there is an <rml>tag followed by an <ishtml>tag.  The <rml>tag tells the IDRS that there is RML in the page.  The <ishtml>tag tells the IDRS wether or not to print the <head>and <body>tags.

This head section defines two <DB>tags.  The frist DB has "emp" for an ID.  The ID attribute is used to identify each separate dataset.  This ID is used when referencing and inserting data into the body of the RML template.  The next line tells the IDRS what driver to use to connect to the database where the the data is stored.  Check your Database and JDBC Driver documentation for details on what this line should be.  The next two lines tells the IDRS what username and password should be used to connect to the DB.  These lines must be placed BEFORE the <DBName>tag, which appears next.  This line has the JDBC connection string that will be used to connect to the DB.  Again, check your documentation for correct syntax for this line.

The next line is the beginning of a SQL statement.  There are two parts to an SQL statement, the source and the declaration of variable types.  The first part of the SQL block is required, the second may not be.  The actual SQL statement to be used is placed here.  If there are no arguments to be used in the statement, then there is no need to declare what types are used.  The rest of the DB block is simply closing tags.

The next DB block is both simpler and more complicated.  The block starts out the same, but this time the ID is 'boss'.  The next line, <useDB>, tells the IDRS to share that database connection created by the first DB block, 'emp'.  This is preferable to creating an new connection because it is faster and saves on system recourses.

Next comes an SQL block.  First comes the <src>block which is used to extract the needed data from the database.  In this statement there is an argument to be used though.  In the WHERE clause of the SQL statement is a '?'.  This means that the IDRS must be supplied the criteria to be used for this statement.  This criteria will be supplied by the form that calls the IDRS and will be covered more later.   If an there are arguments in the SQL statement to be used, their types must be declared in the order which they occur in that statement.  The types are defined in the RML specification under the <varType>tag.  The type declared here is 'int'.  This means that the calling form must supply an integer for the WHERE clause to use in this statement.
 

<BODY>
This is my first IDRS Generated Report<p>

<repeat id="emp" color1="white" color2="#808080">
  <ifchange field="emp.id">
    </table>
    <field>emp.name</field>'s Time Card<p>
    Hourly Rate : <field format="number,currency">emp.payrate</field>/hour<p>
    <table>
    <tr>
      <td><b>Line Number</b></td>
      <td><b>Date</b></td>
      <td><b>Time In</b></td>
      <td><b>Time Out</b></td>
    </tr>
  </ifchange>
  <tr bgcolor="<field><backcolor></field>">
    <td><font color="<field><forecolor></field>"><field><linenum></field></td>
    <td><td><font color="<field><forecolor></field>"><field format="date,medium">emp.date</field></td>
    <td><font color="<field><forecolor></field>"><field format = "time,short">emp.timein</field></font></td>
    <td><font color="<field><forecolor></field>"><field format = "time,short">emp.timeout</field></font></td>
  </tr>
</repeat>
</table><p>
<field>boss.name</field>is the boss and you should always listen to him/her.
</BODY>
</rml>

Now lets take a look at the body of the RML document. The first line is standard HTML. Remember that RML documents output standard HTML. The next line is the beginning of a <repeat>block. <Repeat>tags require at least one parameter, id. This tells the IDRS which db it is iterating through. In this instance we are iterating through the 'emp' data set. The IDRS will process every line inside this <repeat>block for every record in the 'emp' dataset. The next two parameters are optional, but either must both be included or excluded. These colors are used to set the <forecolor>and <backcolor>fields of the IDRS. These special fields alternate between the two specified colors depending what record the IDRS is currently on. This allows for the alternating colors in the resulting tables tat makes the data easier to read.

The next line begins an <ifchange>block. An <ifchange>block is used when you need to only print lines when a certain field in a db changes. In this example we use the <ifchange>to break the last printed table, print employee information and begin a new table. The <ifchange>block has one parameter, field. This parameter is set to the db and field that will be tracked for changes. In this case we are checking the id field of the 'emp' dataset. This is done by setting field="emp.id". The next line is terminates the preceding table. Don't worry because there will be an extra </table>tag, the browser will ignore it. the next few lines print employee specific info that doesn't need to be printed in each line of the record and makes the final report much nicer looking. The key to inserting data into a final report is the <field>tag. This tag is described as part of the RML specification. Simply put the name of the dataset to be used, a period and then the name of the field to be used is placed between field tags. The next <field>tag contains a 'format' parameter. This parameter is used when formatting the output from the IDRS. This is further explained in the RML specification.

The following lines are all similar. These lines are processed ONLY when the emp.id field is changed.

The rest of the <repeat>block is processed for every record in the 'emp' dataset. Look closely at the line immediately following the end of the <ifchange>block. It has a special value inside the <field></field>block. The value is surrounded by a '<' &'>'. This means that the field to be used is an IDRS field. The one that is used is the <backcolor>field. This tells the IDRS to print the current back color as defined in the <repeat>tag. The next line shows the <linenum>field. This field prints the current record number the IDRS is processing. The rest of the <repeat>block is all very similar and straight forward.

After the <repeat>block the last table is closed off and a new paragraph is begun. There is one final <field>tag, this one uses the 'boss' dataset to retrieve data. This <field>tag is outside of a <repeat>tag. If a field is before a <repeat>tag that it's dataset is used in, the first line of the record is used for output. If it is after it's corresponding <repeat>block, the last record is used. There doesn't have to be a <repeat>block for any given dataset.

Now that the RML document is constructed, it must be deployed within the IDRS database. For our purposes the IDRS tblGroups and tblUsers tables will look like this:

tblGroups

GroupID GroupName
1 Boss
2 Employee

tblUsers

UserID UserName Password Groups
1 John asd12 1,2,
2 Jen def34 2,
3 Jack qwe87 2,

Lets look at this security model.  There are two groups, Boss and Employee.  For users, John is both a Boss and an Employee, this makes sense, while Jen and Jack are just Employees.

Now that we have our security model setup, it's tie to figure out what information is needed to deploy our RML document. This is the information needed to deploy our RML document:
 

DocID DocName DocSrc DocVarSrc DocParams DocGroups isFile DocConns
The number of our RML Document The name of our RML Document Either the filename of or the actuall source the RML Document The source of the input form needed for the RML Document The names of the input fields from our input form i the order they appear in the RML document The groups allowed to view this document wether or not the contents of DocSrc is a file the names of DB connections needed by the IDRS

We will go through each of these parts and show what data is needed for each field.

DocID: This is a unique number used to identify the RML document.

DocName: A unique name used to identify the RML document

DocSrc: This is either the source or the filename of the RML document.  The source should be used for maintenance reasons, in our example we will be using the source.

DocVarSrc:  This is the source for the input form that will suply our RML document with the data it needs.  If we look at our RML document's <HEAD>block, we see that our 'boss' <DB>block is the only one that requires a parameter.  To tell the IDRS what information it needs, we need to us a special name system.  That system is as follows DBName_inputName.  In this case we will name our only input box boss_BossID because have to use the DB's name followed by an underscore and a descriptive name.  we also need to include the submit button and reset buttons.  We don't include the <form>tags that define how to handle the data.  The source will look like this, you can download it by clicking here:



Enter The Boss's ID : <input type="text" name="boss_BossID"><br>
<input type="submit"><input type="reset">


Type this information into your favorite text editor and save it to the same directory as our sample RML document.

DocParams: This field is used to tell the IDRS the names of the input tags used in the DocVarSrc field.  You must name each input tag used in the DocVarSrc field in order which they are required in the RML document.  The submit and reset buttons are not included in this list.  The field looks like this:



boss_BossID:



We used only one parameter, boss_BossID.  This list is seperate and terminate by colons.

DocGroups:  This is a list of the groups that will have access to the RML document separated and terminated by commas.  In this case we wouldn't want all of the employees to see the other employee's data, so we'll only allow the boss to look at this report.  This is what the field will look like:



1,


isFile: This field tells the IDRS wether or not the contents of DocSrc is a file name.  The value for this will be false.

DocConns:  This field tells the IDRS what DB connections are used by the RML document.  This field contains the information in the <DBName>tag of each <DB>block.  If a connection is shared by two <DB>block, it is only mentioned once.  This field is separated and terminated by colons and will look like this:



jdbc:dbType:employee,


http://home.netscape.com/

Remember to consult your db and jdbc driver documentation for how to connect to the DB.

Now that we have decided what information we need, we have to enter it into the IDRS database.  We can do this with a utility called InsertToIDRS.  This utility allows us to insert all of the data we need into the tblDoc table of the IDRS.  It's a command line utility written in java.  The use of this utility is simple.  It first requires the driver, username, password, and name of the idrs db.  Next comes the option string.  This string starts with a '-' and continues with several characters that tell the utility what data to insert and where.  By typing 'java InsertToIDRS -h' you can see a full description of these arguments.  There is one argument character that might be confusing,  The 'u' argument option means that it will update a current record in the IDRS db, not including this character means that a new record will be created.  If this character is used then either the 'i' option or the 'n' option and it's corresponding values must be given to tell the utility what record should be updated.  We will use every argument except 'u' in this command.



java -insgvfpc jdbcdriver.Driver jdbc:db:idrs user pass 1 TestDoc /IDRSSrc/IDRSSample.rml 1, /IDRSSrc/IDRSSampleParams.htm boss_BosID:  false jdbc:dbType:employee,



You'll notice that all of these parameters were what we went over in the section prior to this.  This will insert an IDRS document into the tblDoc table.

There is just one more step until We can look at our sample report.  We need to change the web.xml file to reflect the new report we just created by adding a database to be pooled.  This is a simple and straight forward process, review the IDRS Initialization Arguments web page in the IDRS documentation.  change the <numdbs>to 1 and fill in all of the db information needed.

Once the servlet runner and the web server are started, you can go ahead and access the report we created.

To do this, fire up your web browser and point it to the IDRSParams servlet like this:



http://www.yoursever.com/IDRS/IDRSParams?docID=1



This will bring up a form that we give the username, password and the boss' ID.  If we enter john and asdf123 for the username and password and 1 for the ID, then you should see the same page as shown in the sample above.  If you type in the above web address again you will see a difference, there is no username or password.  For as long as the browser is open the IDRS will not ask or accept any other usernames and passwords.

Congratulations!  You have just created and deployed your first IDRS Report!  There is much more that can be done in RML, but this was a good beginning.