University of Houston–Clear Lake



[pic] [pic]

Ad Hoc Graphical Reports

Final Report

Version

Faculty Advisor

Dr. Kwok-Bun Yue

Mentor

Scott Hetherington

Abbasi Dhilawa

[

Team Members

Abdul Hafeez Khan

Augustine Patil Chirra

Nikhila Reddy Chilkamarri

Prathyusha Patinjaryil

ACKNOWLEDGEMENTS

We take the opportunity to thank our instructor Dr. Kwok-Bun Yue and the mentors, Mr. Scott Hetherington and Mr. Abbasi Dhilawa for the support and guidance they have provided us throughout the capstone without which the task assigned to us would not have been accomplished rightly. We would also like to thank Mr. David Webb for providing the Team with a dedicated computer in the capstone lab.

As a team, it was a wonderful experience working together. We would also like to thank all the people who helped us in this project and whom we might not have mentioned here.

ABSTRACT

Tietronix Inc. has a TieFlow utility called “Ad Hoc Report Generator” that allows users to define their own reports. The current utility is a J2EE-based web application, deployed in the JBoss Application Server. It allows users to select an area of interest (AOI) and specify the report name and description, the output columns, the filter columns with values, and the desired sorting, grouping, and calculations. It also offers the user to save the report definition for later execution. The output result is a tabular textual form. The current capstone project aims at extending the Ad Hoc Reporting Tool with graphical reporting ability. The objective is to tightly integrate JasperReports, an open source graphical reporting tool, with the Ad Hoc Report Generator. This involves writing Java code that generates the Report Definition XML (jrxml) as the input for the run time JasperReports Engine to generate the desired graphs. Six important business charts are well implemented in this Capstone. The generated output charts can be saved, and executed later just like the tabular reports.

TABLE OF CONTENTS

Acknowledgement…………………………………………………………………………i

Abstract……………………………………………………………………………………ii

Table of Contents…………………………………………………………………………iii

List of Figures……………………………………………………………………………..v

1. Introduction………………………………………………………………………………..1

2. Design and Implementation of the Solution………………………………………………2

2.1 Block Diagram ……………………………………………………………….…………2

2.2 Use Case Diagram ……………………………………………………………………...4

2.3 Sequence Diagram ……………………………………………………………………..5

3. Main functions of the solution……………………………………………………………6

4. Technologies Selected…………………………………………………………………….6

5. Implementation Issues…………………………………………………………………….7

6. Comparision with other alternative solutions………………………………………………..9

7. Evaluation of Solution……………………………………………………………………...10

7.1 Solution to the problem………………………….……………………………….……..10

7.2 Design of the solution…………..……...……………………………………….………11

7.3 Functionality of the solution………...…………………..…………………….………..11

7.4 Performance of the solution prototype……………………...…………………………..12

7.5 Lessons Learnt. ………………………………………...…………..…………………..12

7.6 Future Enhancements……………………………...…..………………………………..13

8. Project Management and Team Information………………….……………………………14

8.1 Tasks……………………………………………………………………………..……..14

8.2 Task Assignments………………………………………………………….…….……..14

8.3 Schedule………………………………………………………………………..……….14

9. Known Issues……………………………………………………………………………….15

10. Conclusions…………………………………………………………………………………15

11. References…………………………………………………………………………………..16

12. Appendix A (Important Class Diagrams for Graphical Reports)……...………….…..……17

13. Appendix B (Screen Shots of the User Interface)……..….………………………………..19

14. Appendix C (How to Install and Run Ad Hoc Graphical Reports)………..……………….34

List of Figures

Figure 1: Block Diagram………………………………………………………………………….2

Figure 2: Use Case Diagram………………………………………………………………………4

Figure 3: Sequence Diagram………………………………………………………………………5

Figure 4: Bypass the XML and directly format Data……………………………………………..8

Figure 5: Showing tight integration with JasperReports…………………………………………..9

Figure 6: Project Time Line……………………………………………………………………...14

1. Introduction

Tietronix Inc. currently has an Ad Hoc Reporting Tool that allows users to define their own reports. The tool is a J2EE-based web application currently deployed in the JBoss Application Server. The tool currently allows users to select an area of interest (AOI) and specify the report name and description, the output columns, the filter columns with values, and the desired sorting, grouping, and calculations. The report templates can also be saved and subsequently executed at any time. The current report output is a tabular display on the web page with options to export the results to a comma-separated file, a PDF file, an RTF file, raw XML, or a Java applet. While the Ad Hoc Reporting Tool has the basic functionality for textual reporting, it currently does not have the ability to generate charts, graphs, or any other graphical reporting format. The Ad Hoc Reporting Tool requires the ability to output the report results in various graphical formats in addition to its current textual output. The objective of this project is to tightly integrate an open-source graphical reporting tool with the TieFlow Ad Hoc Reporting Tool. The target graphical reporting tool is JasperReports. JasperReports is an open-source, Java reporting tool available as a Source Forge project.

2. Design and Implementation of the Solution

2.1 Block diagram

[pic] Figure 1: Block Diagram

The Orange blocks indicate the work done by our team in this capstone.

The Green blocks indicate the available resources.

The current Ad Hoc Reporting that was given to us has a graphical users interface (GUI) that generates tabular output. The goals of our capstone team include adding another GUI very similar to the tabular reports GUI, which shares a common database with the tabular reports and a write a Java XML API that generates a jrxml which is understood by the Jasper Run time engine, to produce the graphical output.

The important blocks here are:

Current Capstone: This is the current TieReports Package of the Ad Hoc Report Generator which contains all the important classes like the Area of Interest Generator, Report Definition, which are required for the Graphical Reports Interface.

Graphical Report Generator: The java code is written to generate the jrxml for a particular chart. Six different charts have different java classes that generate the specific jrxml according to the input parameters it takes for the output.

Jasper Reports: This is the runtime JasperReports Engine that accepts the jrxml generated and converts it to the final output chart for the report seeker. The Jasper Reports run time engine also comes to play when the graphical report is saved and later when executed, the jrxml is retrieved and complied again to produce the chart.

2.2 Use Case diagram

[pic] Figure 2: Use Case Diagram

The system here is the Ad Hoc Graphical Reports and the actor is the report seeker.

Use Case1: Report Seeker selects an Area of Interest

Use Case2: The generated Area of Interest is given back to the report seeker

Use Case3: Actor selects the Tabular or Graphical reports

Use Case4: Actor is presented with the options of the report generation

Use Case5: If Graphical charts are selected, system presents available parameters

Use Case6: System accepts input parameters and generates the chart

Use Case7: System presents the output chart to actor which can be saved, edited, executed.

2.3 Sequence Diagram

[pic]

Figure 3: Sequence Diagram

The Sequence diagram focuses on identifying the behavior within the system. It elaborates the use case diagram cases in a more detailed manner, explaining each case. The process right from selecting the Area of Interest to the generation of the report is expressed above.

3. Main functions of the Solution

The basic functionality of the system will be to present the user with the options to generate tabular reports or graphical charts. The graphical charts should be just a click away. The six important charts are presented to the user and when he selects a particular chart i.e. Bar Chart and specifies, its input parameters, the system should generate the equivalent jrxml, which is then complied by the Jasper Runtime engine to produce the output chart, which can be viewed in the same browser. The user also is given the chance to save the generated output report so that it can be executed later on.

4. Technologies Selected

• JasperReports 1.3.0: JasperReports is the market leading open source business intelligence and reporting engine. The code is written entirely in Java, and the XML report templates are used to generate ready to print documents using data from customizable data sources, including JDBC. The output can be delivered to the screen, printer, or stored in PDF, HTML, XLS, RTF, CSV, and XML format

• Java JDK 1.5

• JBoss Application Server

• Microsoft SQL Server 2005

5. Implementation Issues

• To use XML Translator that uses the Java JDK version 1.4.2 for any required Java code.

• XML Translator shall use the XML parser currently used by the Ad Hoc Report tool for any required XML processing.

• The capstone shall implement an XML translator for translating the Ad Hoc report definition to BIRT report definition and Jasper Report Definition.

As seen above, the requirements mainly focused on using an XML translator and integrating the current Ad Hoc reporting tool with JasperReports and BIRT. Dr. Yue suggested that we bypass the XML translator part, and instead write a java code that generates the xml report definition that is understood by JasperReports and BIRT. This explanation is clearly shown in the Figure 4.

Figure 4: Bypass the XML and directly format the data

The initial requirement also included implementing BIRT (Business Intelligence Reporting Tool) along with Jasper Reports. BIRT is an open-source Eclipse-based reporting system that can be integrated with a Java/J2EE application. We initially started working on both JasperReports and BIRT 2.2, but over the course of the project, we are not implementing BIRT, but only JasperReports due to the following reasons:

• BIRT is a promising tool with excellent graphical reporting capabilities, but BIRT is a very young tool from Eclipse. It is still not mature as JasperReports.

• It is very difficult to build BIRT outside the Eclipse environment as it is very tightly integrated with Eclipse

• BIRT lacks proper documentation and contains bugs that need to be worked on.

• WYSIWYG ability with BIRT is not too good.

• BIRT is harder to integrate with other open reports than other engines.

• Time constraint

• Number of important Business Charts available with JasperReports and BIRT is almost the same. Hence we were asked to implement the six important business charts present in JasperReports and leave BIRT.

Some of the other difficulties encountered by us are listed below,

• As we were required to modify and add more features to the existing Ad Hoc Report Generator, understanding its working was initially the biggest challenge.

• We faced few problems while deploying the existing tool, for example, the Java SDK version to be used, SQL Server 2005 Network configuration and security settings (as SQL Server was used for the first time by the team members) and the memory out error in JBoss Server.

• Understanding the format of the jrxml was difficult.

• Saving the report definition xml back to the database and retrieving it and executing it using the execute feature of the Tabular reports was a big challenge in the final stages of the capstone.

Now, we can proudly say that we have succeeded in overcoming all the challenges and are able to generate the Graphical charts as per the requirements.

6. Comparison with other alternative solutions

The project can basically be divided into two major parts.

1. Generating the Report definition XML(jrxml)

2. Generating the Graphical charts

Figure 5: Tight integration of JasperReports

The Report Definition XML is the input for the JasperReports and it follows a specific format. It has its own tags understood by the JasperReports engine. The tags are called bands. This xml is then compiled by the JasperCompileManager to generate the required charts. The solution can be well implemented using iReports, an open source java based IDE for generating charts using JasperReports. The user needs to select a particular chart, give the required input parameters (which are generated as fields when the user reports a query and establishes an active database connection). The compilation creates a jrxml of the chart which can be saved and execution is done by the JasperReports engine built within the iReport.

This above process is implemented in our capstone avoiding many of the unnecessary steps of iReports. The user selects the type of chart he desires and the category and value expression as parameters. By single click the jrxml is created by our application and compiled by the JasperReports integrated within it to produce a Graphical Report. The evaluation of our solution is described in the next section.

7. Evaluation of the Solution

7.1 Solution to the Problem: The Ad Hoc Reporting Tool provided to us initially had the basic functionality for textual reporting. It lacked the ability to generate charts, graphs, or any other graphical reporting format. The team was required to enhance the Ad Hoc Reporting Tool to provide it with the ability to output the report results in various graphical formats in addition to its current textual output. The objective of the project was to tightly integrate the open-source graphical reporting tool called JasperReports with the TieFlow Ad Hoc Reporting Tool.

7.2 Design of the Solution: After carefully understanding the requirements, the team members were able to broadly divide the project into three major tasks.

1. Produce the Report Definition XML, which serves as an input to the JasperReports engine: This XML was first generated for a simple bar chart and then five more charts were added.

2. Integrate the JasperReports with the current application: The Integration of JasperReports was a tough job to perform, as the tool was completely new to us. Still, being an open source and lot of documentation available made JasperReports the best choice for a developer for the Integration.

3. Modifying the Graphical User Interface: We tried to keep the user interface as simple as possible. The idea was not to modify the look and feel so much that the existing users of Ad Hoc Reporting tool might find it difficult to understand the new interface. The existing left pane was modified to add the hyperlinks for the graphical chart. When a chart is selected the user is asked for the chart parameters. Upon selection of the parameters the graphical report is generated in a single click. The save report, edit and execute options are implemented same way as there are for the textual reports.

7.3 Functionality of the Solution: The basic functionality of the project is to generate graphical reports apart from the already implemented textual report. The user should be able to generate the graphical reports in several formats (bar, area, pie etc) in a single click. Also the XML for the generated report should be saved back to the database, and can be retrieved back from the database whenever needed.

7.4 Performance of the Solution prototype: The Ad Hoc Graphical Reporting tool developed by us has been tested with the sample database provided and is successful in generating the graphical reports as a Bar, Area, Stacked Bar, Pie, Scatter and Line Charts. It meets all the basic requirements set forth by the mentors. The chart image which is in PNG format is exported to an Html page, through an image servlet.

7.5 Lessons Learnt

• Team Work

Working as a team for a given task can prove to be very challenging. All the members agreeing on an idea can be sometimes just not possible. But still, working as a team has solved many major tasks for us in a very short time. In our team, every person had vast imagination and abundant ideas, which we used for the betterment of the team. We had several team meeting to discuss the issues and have documented everything we discussed in those meetings.

• Time Management

Finishing the task assigned on time has been a very important factor. Meeting the various deadlines set by the instructor was tough and gave us a valuable experience of how to effectively manage the time.

• Research

As the technologies JasperReports and JBoss were new to us, getting to know them required a lot of research. We learnt that a good understanding of the topic can be achieved only after a thorough research.

• Experience

We have gained valuable knowledge and experience working on this project. We learnt about JasperReports, SQL Server 2005, and XML etc. We feel that this knowledge will be very helpful for us in our careers.

7.6 Future Enhancements

As per the requirements the JasperReport has been tightly integrated with the existing Ad Hoc Report Generator. In future, the team believes that the following enhancements can be made to the capstone,

• Other complicated charts may be implemented.

• The generated Graphical Reports may be exported to Excel, PDF and rtf files.

• Generating a Graphical Report for a complex query (example using order by or group by clause or sorting, merging of fields in query) can be implemented.

• The project can be extended to various other Open Source Graphical Reporting Tools.

8. Project Management and Team Information

Team Lead: Prathyusha Patinjaryil

Research and Design: All

Webmaster: Augustine Patil and Abdul Hafeez Khan

Database Developer: Nikhila Reddy Chilkamarri

Testing and Debugging: All

Developers: All

Documentation: All

8.1 Tasks

The major tasks of the project include

• Understanding the requirements and studying the current tool

• Developing Additional Graphical User Interface for the current tool

• Creating Database in SQL Server 2005

• Retrieving the Report Definition query for Graphical reports

• Generating the jrxml for every chart

• Saving reports and retrieving them

• Report and other Documentation which include UML Diagrams

8.2 Task Assignments

Due to the nature of our project, all the team members had to devote equal time in learning all the new technologies that were used and have contributed equally to all aspects of the project.

8.3 Schedule

We have split our tasks in the following schedule. Show below are the various tasks performed in respective time limits. Finally it can be seen that the project deadline has been attained successfully.

[pic]

Figure 6: Schedule of Events (unit = week)

9. Known Issues

The scope of our prototype deals with developing graphical reporting ability to the Ad Hoc Report Generator. However, we felt that our project had the following limitations which could not successfully be implemented in the time duration given for the project

• Some of the output charts are displayed three times which is a property of iReports.

• Graphical Reports can be built from the same report definition as tabular reports except for the grouping option.

10. Conclusions

• The Ad Hoc Reporting tool now has the ability to produce graphical reports.

• Both Tabular and Graphical reports can be generated from same database.

• The report viewer can produce reports, save and execute them.

11. References

The following references have been used in order to understand the underlying technologies used in developing this prototype. These have not been used for our documentation purposes.

• Jasper Reports:

• Java XML API:

• Java:

• XML:

• JBoss:

• Documentation:

Appendix A:

Important classes for generating the Graphical Reports:

The Tabular and Graphical Reports are very tightly integrated. There are many classes that have been used from the Ad Hoc report generator. Hence, we are explaining some of the important classes that play a major role in the graphical reports.

1. The ReportsManager class compiles and executes the report definition.

[pic]

2. The GraphicalReportComplier uses the JasperReport Engine to compile the Reports.

[pic]

3. The GraphicalReportExecution class calls the Run time Jasper Engine to execute the charts

[pic]

4. The ReportHTTPManager class contains all the methods for the generation and execution of the reports.

[pic]

Appendix B:

Screen Shots of User Interface:

Screenshots of the running Application:

Follow the setup instructions to copy the war folder to the right location and setting up the Database.

1. Start the JBoss Server by double clicking, D:\Program Files\jboss-4.0.5.GA\bin\run.bat

2. Open a new browser window and type

in the address bar.

Create entry for TieJasperReports:

Label: TieReportsTestData

JNDI: TieReportsTestData

Database Server: [db_server_machine]

Database Port: 1433

Database Owner: dbo

Database Name: TieReportsTestData

3. Click 'Area Of Interest Generator' at top.

Select 'TieReportsTestData' Database.

Enter any name for 'Area of Interest Category Name' (name is 'RunNow' here)

Select 'Go' button.

4.

Select 'Reports' button at top.

Select Category previously created (RunNow).

Select '*_tiereportstestdata_dbo_table2' by 'Create New Report from Area Of Interest'

5. Select the Graphical Chart desired from bottom left of the menu:

6. Select Bar Chart and the Fields on X and Y Axis for the Bar chart and click View Report

7. A Bar chart for the selected input can be viewed below,

8. The other charts implemented can be shown in the following screenshots,

An Area Chart

9. Generating a Pie Chart

A Pie Chart

10. Generating a Line Chart

A Line Chart

11. Generating a Stacked Bar Chart

A Stacked bar Chart

12. Scatter Chart

A Scatter Chart

13. The following screenshot displays the saving of the Bar Chart previously created,

14. The saved chart can be viewed from the Reports tab at the top. The XML for the chart can also be viewed.

[pic]

Appendix C

How to install and run Ad Hoc Graphical Reports

Contents

1. Downloading the necessary software

a. JDK 1.5

b. Net Beans 5.5

c. JBoss Application Server 4.0.5

d. SQL Server 2005

2. Installing the necessary components

a. Installing SQL Server 2005

b. Installing Net Beans 5.5

c. Installing JDK 1.5

d. Installing JBoss 4.0.5

3. Setting up the environment

a. Import the TieJasperReports war file

b. Importing and setting up the Database.

c. Changing JNDI properties file accordingly.

4. Running the program

a. Running the TieJasperReports Program

1. Downloading the necessary software

a. JDK 1.5:

b. Net Beans 5.5:

c. JBoss 4.0.5:

d. SQL SERVER 2005

2. Installing the necessary Components

a. SQL Server 2005:

• In the SQL Server Management Studio,

• Server type: Database Engine

• Authentication: SQL Server Authentication (give the username and password)

• From Programs, select Microsoft SQL Server 2005, select Configuration tools, in SQL Server Configuration Manager, in SQL Server 2005, Network Configuration, in Protocols for MSSQL Server, ENABLE TCP/IP status.

• Make sure the port number is 1433

b. Net Beans 5.5:

• In tools, select Java Platform Manager.

• Set Platform folder to directory which contains JDK 1.5.0 i.e., for example, Platform Folder: C:\ProgramFiles\Java\jdk1.5.0_05

• Select in Tools, Server Manager, Add the server, JBoss Application Server The Server type and name will be JBoss Application Server 4. Select the Java Platform to JDK 1.5( Default)

c. Installing JDK 1.5

• Run the jdk-1_5_0_05-windows-i586-i.exe. This is the JDK installer for Windows. Follow the instructions, the installer provides.

• Update the path variable to C :> “\Program Files\Java\jdk1.5.0_05\bin\”

d. Installing JBoss Application Server 4.0.5

• Install JBoss. Be sure that you have 1.5.SDK (Software Development Kit) installed and not just that the JRE (Java Runtime Environment).

• JBoss uses the complier in the SDK to compile the JSP pages on the fly.

• To start JBoss, change to the JBOSS_HOME/bin/directory and type run. This should start a string similar to:

[pic]

3. Setting up the environment

Create TieReports Database

1. Create a Database named 'TieReports'

2. Create tables by running the SQL script './config/TieReports_Tables.sql'

3. Create default data by running the SQL script './config/ConKeywords.sql'

Create TieReports Sample Database

1. Create a Database named 'TieReportsTestData'

2. Create tables and data by running the SQL script './config/TieReportsTestData.sql'

Deploy TieJasperReports App

1. Copy dir './TieJasperReports.war' to [jboss-install-dir]\server\default\deploy

2. Copy file './config/jtds-0.9.jar' to [jboss-install-dir]\server\default\lib

3. Copy file './config/mssql-TieReports-ds.xml' to [jboss-install-dir]\server\default\deploy

o Edit this file and update connection-url, user-name, and password

4. Copy file './config/mssql-TieReportsTestData-ds.xml' to [jboss-install-dir]\server\default\deploy

o Edit this file and update connection-url, user-name, and password

4. Running the Program:

Running TieReports Web App

1. Start JBoss.

2. Go to

3. Create entry for TieReportsTestData:

Label: TieReportsTestData

JNDI: TieReportsTestData

Database Server: [db_server_machine]

Database Port: 1433

Database Owner: dbo

Database Name: TieReportsTestData

4. Click 'Area of Interest Generator' at top.

5. Select 'TieReportsTestData' Database.

6. Enter any name for 'Area of Interest Category Name'

7. Select 'Go' button.

8. Select 'Reports' button at top.

9. Select Category previously created.

10. Select '*_tiereportstestdata_dbo_table2' by 'Create New Report from Area Of Interest'

11. This gives you the option of viewing the Tabular or Graphical reports

12. For viewing Tabular reports, select the options of grouping, sorting, filtering, Calculations etc and view the tabular report.

13. Select View Report to view tabular report output.

14. The Tabular report can be saved by Selecting Save Textual Report.

15. For viewing the graphical report, create a new report definition or use the same report definition as the tabular reports.

16. Click on a particular chart to view a sample chart and select from the options, and give the input parameters for the chart.

17. Select View Report to view graphical report output

18. The report can be saved by selecting the Save Graphical Report

19. To view the saved reports, click on the Reports tab.

20. The report can be executed again.

-----------------------

jrxml

Graphical Report

Runtime

Jasper

Engine

Graphical Report

Generator

Formatted data

Report

Data Formatting in Report XML

TieFlow Data

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download