OpenReports: Users Guide

(1)

OpenReports: Users Guide

Author:Erik Swenson

Company:Open Source Software Solutions Revision: Revision: 1.3

(2)

4. Charts ... 12 4.1. Chart Definitions ... 13 4.2. Report Definitions ... 15 5. Scheduled Reports ... 16 5.1. Configuration... 17 5.2. Scheduling ... 18 6. FAQ ... 19 7. Change History ... 21 7.1. Version 0.71... 22 7.2. Version 0.7... 23 7.3. Version 0.6... 24 7.4. Version 0.5.1... 25 7.5. Version 0.5... 26 8. Upgrading ... 27 8.1. Version 0.7... 28 8.2. Version 0.6... 29 8.3. Version 0.5.1... 30

9. Open Source Software Solutions ... 31

10. License ... 32

11. Credits ... 33

OpenReports: Users Guide

(3)

1. Introduction

OpenReports is a web based reporting application that allow users to generate dynamic reports in a browser and view them as PDF, HTML, XLS, or CSV files. The goal of OpenReports is to provide a web based reporting solution built upon open source technologies that will compete against complex and expensive reporting tools such as Crystal Reports and Cognos.

OpenReports uses JasperReports, an excellent full featured open source reporting engine, and was developed using leading open source components including WebWork, Velocity, and Hibernate.

Features include

• The ability to create groups of reports, and grant users access to reports by group.

• The ability to generate reports as PDF, XLS, HTML, and CSV files.

• The ability to generate bar, pie and xy charts for inclusion in reports.

• The ability to schedule and email PDF, XLS, and CSV reports.

• The ability to define reusable report parameters. Available parameter types include Date, Text, and Query Parameters.

• The ability to create multiple DataSources for use in generating reports. Support for JNDI DataSources and internal connection pooling via Commons-DBCP is included.

• The ability to upload and hot deploy new reports.

• Web based administration of users, groups, reports, parameters, and datasources.

• Cross platform database support via Hibernate based persistence layer.

• Available in a preconfigured bundle with Apache Tomcat.

Note: This document contains instructions to install and run OpenReports. If you are using OpenReports for

the first time, it is recommended that you download the OpenReportsTomcat bundle. OpenReportsTomcat is preconfigured and contains a sample database that should give new users a better understanding of how OpenReports works.

(4)

2. Installation

Note: If you have downloaded the preconfigured OpenReportsTomcat bundle, and would like to get started

immediately,unzip OpenReportsTomcat to root directory of your hard drive, change to the

'openreports-tomcat/bin' directory, start Tomcat via 'catalina run', open 'http://localhost:8080/openreports' in your browser, and login using a username of 'admin', and a password of 'admin'

In order to get OpenReports up and running, you must first perform the following steps: 1. Create OpenReports tables in your database.

2. Create OpenReports Admin User.

3. Configure Hibernate to point to your database. 4. Configure upload directory.

OpenReports stores information about reports, users, groups, etc. in database tables. These tables must be created before first using OpenReports. The SQL scripts for some common databases are in thedocs/database directory.

Once you have created the OpenReports tables in your database, you must insert a record into the REPORT_USER table in order to create an Admin user that you can use to log into and administer the application.

The following example creates a user with the name 'admin', a password of 'password' and Administrative rights:

INSERT INTO REPORT_USER (NAME, PASSWORD, ADMIN, EXTERNAL_ID, EMAIL_ADDRESS, PDF_EXPORT_TYPE)

VALUES ('admin','password',1,'','',0)

Note: The value of the ADMIN field in the INSERT statement may be 1 or 'true' depending on how your

database handles boolean fields.

The last step in the configuration process is to configure Hibernate so that OpenReports can connect your database.

In order to configure Hibernate you must set the SQL Dialect, and connection properties in thehibernate.propertiesfile that is located in theWEB-INF/classesdirectory. Here are some common examples:

HSQL

hibernate.dialect=net.sf.hibernate.dialect.HSQLDialect

MySql

hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect

SQL Server

(5)

hibernate.dialect=net.sf.hibernate.dialect.SybaseDialect

PostgreSQL

hibernate.dialect=net.sf.hibernate.dialect.PostgreSQLDialect

The next step is to set the connection properties. If you are going to use a JNDI connection, the only property you need to set is"hibernate.connection.datasource". For example:

hibernate.connection.datasource=java:comp/env/jdbc/OrDb

If you are not using JNDI, you must supply all the properties needed to create a connection such as the URL and Driver. Examples of these properties are included in the

hibernate.propertiesfile.

The final configuration step is to set thewebwork.multipart.saveDirproperty in the

WEB-INF/classes/webwork.propertiesfile. This is the directory where you will upload your compiled JasperReport files to. This property must be set to the full path of the

openreports/reportsdirectory, for example:

webwork.multipart.saveDir=

(6)

3. Getting Started

In order to use OpenReports, you must add DataSources, Reports, Users, Groups, Etc. to the application through the Adminstration pages. The Adminstration pages are available though the Admin link on the top right section of the page.

3.1.DataSources 3.2.Reports

3.3.Report Parameters 3.4.Groups and Users

(7)

3.1. DataSources

DataSources are used by reports to gather data. You can define multiple datasources, either as JNDI datasources, or internal connection pools. You must create at least one datasource before you can run a report.

(8)

3.2. Reports

In order to deploy a report in OpenReports, you must first upload the report using the

upload link. After uploading your report files, you define each report by giving it a name and description, and specifying the datasource and JasperReport file used to generate the report.

Optionally, you can add any Report Parameters you have defined to the report.

Query Reports A new feature of OpenReports is the ability to create reports from queries

without the need to create a JasperReport. Query reports are displayed as a HTML table and support exporting to XLS, CSV, and XML.

A query report is added to OpenReports in the same manner as other reports. The only difference is that you must enter a valid query in the query field instead of choosing a report file.

Note: OpenReports uses JasperReports as the reporting engine, and can only run compiled JasperReports

files. The version of JasperReports used to compile your reports must be in sync with OpenReports. If you are having problems running reports, verify that your reports were compiled with the same JasperReports jar file that is in the openreports/WEB-INF/lib directory.

(9)

3.3. Report Parameters

In order to pass parameters to your reports, you must first define your report parameters and then add the parameters to the report through the report admin page.

In order for JasperReports to recognize the parameters, the name of the Report Parameter must match the name of the parameter defined in your JasperReport file.

Parameter steps, sort order, and compound parameters.

A powerful feature of OpenReports is the ability to divide reports parameters into multiple steps and sort the parameters within each step.

Compound parameters are parameters that are built from the values of previously choosen parameters. For example, compound parameters can be used to present the user with a list of Countries and then a list of cities based on the selected Country.

Multi-Select parameters

A Multi-Select parameter will display a list of possible values and allow the user to select more then one value. The selected values will be passed to the report as a String. For example:

'Austria','Canada','France'

This value can be used in the report query, for example:

select orderid, employeeid, freight, shipcountry from orders

where shipcountry in ($P!{CountryList}) order by shipcountry

The following examples illustrate the use of the four parameter types in OpenReports.

Query - creates a combobox containing the values returned from the query.

Type:Query

Class:java.lang.String

Data:select distinct shipcountry from orders order by shipcountry

Datasource:your datasource...

Note: If your query contains two columns, OpenReports will display the value of the second column as the

parameter on the web page, but send the value of the first column to the report. For example, the query 'select statecode, statename from states' will show the list of state names as available parameters, but send the state code of the choosen state to the report.

(10)

List - creates a combobox containing the values from the pipe delimited data String.

Type:List

Class:java.lang.Integer

Data:10000|11000|12000|13000|14000|15000

Text - creates a textfield for String parameters

Type:Text

Class:java.lang.String

Date - creates a datepicker component

Type:Date

Class:java.util.Date

(11)

3.4. Report Groups and Users

The last two things that need to be created are Report Groups and Users. Report groups are groups of reports that you can assign to your users. Users can be given multiple reports groups. If you check the Administrator box, the user will be given access to the Admin screens.

(12)

4. Charts

Note: Chart are new to this version of OpenReports. The charting interface and capibilites are subject to

change.

OpenReports uses JFreeCharts, the leading open source charting package, to provide the ability to include dynamically generated charts in your reports without the need to write any code.

To include a chart in a report, you must follow three steps 1. Define the chart.

2. Add the chart and chart query parameters to your report.

3. Include a ChartImage parameter in your JasperReport definition. 4.1.Chart Definitions

4.2.Report Definitions

(13)

4.1. Chart Definitions

OpenReports charts are defined via the Chart link on the Administration page. You can create any number of charts, and the charts you define can be used in multiple reports. To create a chart you must enter a name, title, and a number of other properties. The most important properties are chart type, query, and datasource. These properties are used to generate the chart dynamically at runtime via a query. Each chart type requires a specific query format to generate a valid dataset for the chart. The chart queries can include parameters to be selected by the user at runtime.

OpenReports currently supports three types of charts, Bar, Pie, and XY charts. The following examples illustrate the query format required for each chart.

Bar Chart

The query format for Bar Chart reports is

SELECT value, series, category FROM ... WHERE... GROUP BY... ORDER BY...

For example:

The following query produces are bar chart of the number of orders by city and country:

select count(*), shipcity, shipcountry from orders

where shipcountry like 'A%' or shipcountry like 'B%'

group by shipcity order by shipcountry, shipcity

This query produces a bar chart of the number orders by city for a given country. To use a parameter with a chart query, the matching report parameter must be added to the report via the Report admin page.

select count(*), shipcity from orders

where shipcountry = $P{Country} group by shipcity

Pie Chart

The query format for Pie Chart reports is

SELECT value, key FROM ... WHERE... GROUP BY... ORDER BY...

(14)

This query produces a pie chart displaying the allocation of address for each city in the address table

select count(*), city from address group by city

XY Chart

The query format for XY Chart reports is

SELECT series, value1, value2 FROM ... WHERE... GROUP BY... ORDER BY...

For example:

This query produces a XY chart comparing the position vs quantity for two different products.

select name, position, quantity from position, product where productid in (1,2) and productid = product.id order by productid

Time Chart

The query format for Time Chart reports is

SELECT series, time, value FROM ... WHERE... GROUP BY... ORDER BY...

For example:

This query produces a time chart that plots the amount of orders over time.

select 'Orders', count(*), orderdate from orders group by orderdate

(15)

4.2. Chart Reports

Report Definition

Once you have defined your chart, you must modify your report definition in OpenReports to include your chart. This is done by selecting the desired report from the chart dropdown box on the Edit Report page. Also, if your chart query included any parameters, you must selected the matching report parameters on the Edit Report page in order for the user to be prompted to enter the parameter.

JasperReport Definition

Charts generated by OpenReports are passed to your reports as a parameter named 'ChartImage'. In order for the chart to appear in your report, you must modify the JasperReport definition to include the following parameter:

Here is an example of an image tag that can be used in a JasperReports definition to display the ChartImage:

<reportElement x="0" y="40" width="545" height="752"/> <imageExpression

class="java.awt.Image">$P{ChartImage}</imageExpression> </image>

Note: The OpenReports Tomcat distrubiton includes a number of example charts, and an example chart

report to help you get started. Also, visit the JFreeChart website at http://www.jfree.org/jfreechart/index.html for more information about the chart types used in OpenReports.

(16)

5. Scheduled Reports

OpenReports uses Quartz, an open source enterprise-class Job Scheduler, to provide the ability to schedule PDF reports to be sent via email.

Note: The scheduling feature is new to this version of OpenReports. The scheduling interface and capibilites

are subject to change.

5.1.Configuration 5.2.Scheduling

(17)

5.1. Configuration

The scheduling feature of OpenReports can be enable or disabled by uncommenting or commenting out the following lines in the components.xml file.

<scope>application</scope>

<class>org.efs.openreports.providers.impl.SchedulerProviderImpl</class> <enabler>org.efs.openreports.providers.SchedulerProviderAware</enabler> </component>

Themail.smtp.hostandbaseDirectory properties in the

openreports.propertiesfile must also be set to a valid mail host, and the full path of the OpenReports web application directory in order to generate and email scheduled reports.

OpenReports is currently configured to use a Quartz RAMJobStore which stores all jobs in memory. All scheduled jobs will be lost if OpenReports is reloaded or shutdown. Quartz can be configured, in the quartz.properties file, to persist jobs to a database via a

JDBCJobStore. For more information on configuring Quartz, visit the Quartz project site at http://sourceforge.net/projects/quartz

(18)

5.2. Scheduling

To scheduling a report, click on the schedule report link after you have choosen the report and entered any parameters. The Schedule Report page allows you to select schedule type, start date, and start time. Enter the email addresses of the report recipients in the recipients field. The 'scheduler' link at the top of the page allows you to review your scheduled reports and remove any scheduled reports that are no longer needed.

(19)

6. FAQ

Common Problems

1) Error loading object from file (or reports not being generated):

JasperReports requires that you compile your reports using the same version of the JasperReports jar file as the application you are using to run the reports (in this case OpenReports) is using.

To fix this problem, recompile your JasperReport files using the same version of the jar file that is in theopenreports/WEB-INF/libdirectory.

Another possible solution is to replace the JasperReports jar file in

openreports/WEB-INF/libwith the jar file you are using to compile your reports. In most cases they should be compatible, but backwards compatiblity is not guaranteed. 2) java.lang.NoClassDefFoundError on Linux:

The JasperReports library will generatejava.lang.NoClassDefFoundError

exceptions when running on a Linux machine without an X-Server running. The easiest solution is to run in headless mode, which is available in JDK 1.4+. For example, if you are using Tomcat, add the following to your catalina.sh file:

CATALINA_OPTS="-Djava.awt.headless=true"

For more information, search the JasperReports forums on SourceForge for 'headless' or 'NoClassDefFoundError'.

Database Schema Help

OpenReports uses Hibernate to provide cross platform database support and the

OpenReports distribution includes the DDL for a number of databases. OpenReports also includes two utility classes that can be used to create or update the OpenReports schema. These two classes can be run by ANT using build.xml file included with OpenReports. 1)ant schemaExporter- this target will create the entire OpenReports schema in the database indicated in the hibernate.properties file.

2)ant schemaUpdater- this target can be run to update the OpenReports schema in the database indicated in the hibernate.properties file to the latest version.

If you are having problems with the DDL included in the OpenReports distribution, it may be easier to export or update the schema directly using the ANT targets.

Also, make sure that thehibernate.dialectin thehibernate.propertiesfile is set to the proper dialect for your database.

(20)

http://www.hibernate.org

(21)

7. Change History

7.1.Version 0.71 7.2.Version 0.7 7.3.Version 0.6 7.4.Version 0.5.1 7.5.Version 0.5

(22)

7.1. Version 0.71

NEW FEATURES

1. Report Parameter Enhancements

- Made standard parameters (user id, name, and external id) available to parameter queries.

2. JasperReports

- Upgraded to JasperReports 0.53

3. Misc

- Changed Report.REPORT_QUERY, ReportChart.CHART_QUERY, and

ReportParameter.DATA to text types in order to support queries longer then 255 characters.

- Replaced 'select date' with calendar icon on report schedule page.

CHANGE LOG

1. Bug Fixes

- Changed ReportUser.ADMIN and ReportUser.PDF_EXPORT_TYPE fields NOT NULL

- Moved Velocity pages up a level to fix compatibility problems with some app servers (Tomcat 5...)

- Added jta.jar.

2. Updated installation documentation

(23)

7.2. Version 0.7

NEW FEATURES

1. Report Parameter Enhancements

- Multi-select parameters (Based on code contributed by David Glick) - Parameter Steps and Sort Order

- Compound Parameters 2. Charting

- Time Series Chart (Latie) 3. Scheduled Reports

- Added support for scheduling XLS and CSV reports 4. Report Administration

- Added simple query based reports 5. JasperReports

- Upgraded to JasperReports 0.52

CHANGE LOG

1. Bug Fixes

- Fixed UserPersistenceProvider (Latie) - Fixed DeleteChart bug (Steve Peterson)

(24)

7.3. Version 0.6

NEW FEATURES

1. New User Interface

- Single CSS file for easy customization - Administration Navigation Panel - All tables are now sortable 2. Charting

- Create bar, pie, and xy charts

- Chart data created via SQL statements that can include parameters - No coding required to create dynamic database driven charts

- Uses JFreeCharts 3. Scheduled Reports

- Schedule PDF reports to run on a one time, daily, or weekly basis - Email scheduled reports to multiple users

- Uses Quartz Scheduler 4. User Admin Page

- Users can now changed their own username, password, and email address 5. Export types for report

- Valid export types are now set at the report level 6. Delete confirmation pages

- added delete confirmation pages for all OpenReports objects

CHANGE LOG

1. Security

- Fixed admin security problem

- Report groups and reports are now validated 2. All Velocity page footers are now parsed. 3. PDF Export

- Added content-disposition header to PDF export

- Allow users to choose between default and single request PDF export types

(25)

7.4. Version 0.5.1

NEW FEATURES

1. Added MaxWait to Edit DataSource Page. 2. Added Description to Report Parameters. 3. Added Required to Report Parameters

- Required Parameters must be filled in before running reports. 4. Added ReportParameterValue object

- For parameters with Id and description. See Report Parameter Help Page... 5. Added ExternalId and Email fields to ReportUser object.

6. Automatically passing the following parameters to all reports. - OPENREPORTS_USER_ID

- OPENREPORTS_USER_EXTERNALID - OPENREPORTS_USER_NAME

- OPENREPORTS_IMAGE_DIR

7. Added report logging that stores report, start and end time, and status of each report run.

8. Added support for empty datasource reports

CHANGE LOG

1. Fixed issues that allowed users direct access to some pages without being logged in. 2. Fixed problem with application not throwing exception when testing datasources that

contain invalid settings by upgrading connection pooling to use commons-dbcp-1.1 and commons-pool-1.1.

3. Removed NOT NULL contraints from fields (DRIVER, USERNAME, PASSWORD...) in REPORT_DATASOURCE table to support both Commons-DBCP and JNDI

datasources.

4. Fix groupId bug in ReportListAction

(26)

7.5. Version 0.5

NEW FEATURES

1. Cross platform database persistence via Hibernate 2. Added JNDI lookups of datasources

3. Added Long, BigDecimal and Timestamp parameter types 4. Added ValidationQuery to datasources

5. Added Help on Param page

CHANGE LOG

1. Upgraded to JasperReports 0.5

2. Fixed URLs in actions.xml and various pages

3. Fixed Login.vm so username/password do not show in URL

(27)

8. Upgrading

This section contains a list of the database changes that must be made in order to upgrade to the latest version of OpenReports from previous versions.

Note: For more information about table definitions, refer to the Hibernate mapping files and to the DDL scripts

in the docs/database directory. Also, the schemaUpdater ANT task can be used to upgraded an existing OpenReports Schema to the latest version.

8.1.Version 0.7 8.2.Version 0.6 8.3.Version 0.5.1

(28)

8.1. Upgrading to OpenReports 0.7

1) Add the following fields to the OpenReports tables:

Table Name Java Type

REPORT REPORT_QUERY String

REPOR_PARAMETER MULTI_SELECT boolean

REPORT_PARAMETER_MAP REQUIRED boolean

REPORT_PARAMETER_MAP SORT_ORDER int

REPORT_PARAMETER_MAP STEP int

(29)

8.2. Upgrading to OpenReports 0.6

REPORT PDF_EXPORT boolean

REPORT CSV_EXPORT boolean

REPORT XLS_EXPORT boolean

REPORT HTML_EXPORT boolean

REPORT CHART_ID Integer

REPORT_USER PDF_EXPORT_TYPE Integer

(30)

8.3. Upgrading to OpenReports 0.5.1

REPORT_PARAMETER DESCRIPTION String

REPORT_PARAMETER REQUIRED boolean

REPORT_USER EXTERNAL_ID String

REPORT_USER EMAIL_ADDRESS String

2) Remove NOT NULL contraint from the following fields in the REPORT_DATASOURCE table in order to support both Commons-DBCP and JNDI datasources:

DRIVER, USERNAME, PASSWORD, MAXIDLE, MAXACTIVE

3) Create new REPORT_LOG table.

(31)

9. Open Source Software Solutions

OpenReports is sponsored by Open Source Software Solutions. We specialize in Java development using open-source software. If you are interested in support or customization of OpenReports please visit us at:http://www.opensourcesoft.netor email

(32)

10. License

OpenReports is distributed under theGPL License

For alternative licensing, [email protected]

(33)

11. Credits

OpenReports uses the following open-source projects: JasperReports - Java report-generating library

http://jasperreports.sourceforge.net

WebWork - MVC web application framework http://sourceforge.net/projects/opensymphony

Hibernate - a powerful, ultra-high performance object/relational persistence and query service for Java

http://sourceforge.net/projects/hibernate

Quartz - a Java open source enterprise-class Job Scheduler, http://sourceforge.net/projects/quartz

JFreeCharts - a Java charting library http://www.jfree.org/jfreechart/index.html

OpenReports also uses a variety of other open-source software including software developed by the Apache Software Foundation (http://www.apache.org)

(34)

OpenReports: Users Guide