[#XCF-2286] Integrate Jasper Reports - SIPfoundry Issue Tracker

sipXconfig

Integrate Jasper Reports

Created: 2008-02-21 18:41 Updated: Yesterday 21:46

Component/s:

usability

Affects Version/s:

None

Fix Version/s:

3.11.8

Original Estimate:

3 days

Remaining Estimate:

4 hours

Time Spent:

2 days, 4 hours

File Attachments:

0001-XCF-2286-Added-files-to-build-Jasper-Reports-depende.patch (8 kb)
2.

0001-XCF-2286-CDR-reports-based-generated-by-jasper-repor.patch (130 kb)
3.

0001-XCF-2286-prototype.patch (40 kb)
4.

0001.bmp (1.14 Mb)
5.

0002-XCF-2286-Removed-the-report-for-SIPXCONFIG-database.patch (4 kb)
6.

0002-XCF-2286.patch (80 kb)
7.

0003-XCF-2286.patch (113 kb)
8.

0004-XCF-2286.patch (126 kb)
9.

cdr-extension-report.jrxml (6 kb)
10.

cdr-table-report.jrxml (5 kb)

Image Attachments:

1. 0002.jpg (146 kb)	2. 0003.jpg (77 kb)	3. 0004.JPG (28 kb)
4. 0005.JPG (51 kb)	5. 0006.JPG (45 kb)	6. 0006.JPG (52 kb)
7. 0007.JPG (24 kb)

Issue Links:

Related

This issue related to:
	XECS-1084 CDR subsystem should allow the identi...
	XECS-1091 CDR Report: Minutes per day of week, ...
	XECS-1092 CDR Report: Call volume (# of calls) ...
	XECS-1093 CDR Report: Top n extensions with the...
	XECS-1094 CDR Report: Top n callers with the hi...
	XECS-1102 CDR: Call legs that belong to the sam...

This issue related to:
	~~XCF-2500~~ A link is needed to see what DDI rece...

Description

« Hide

This issue proposes the integration of Jasper Reports into sipXconfig to provide flexibility for all reporting needs of sipXecs.

What is Jasper Reports?
----------------------------
Jasper Reports is an open source reporting application written in Java. It is similar to Crystal Reports in functionality in that it allows the creation of report templates that then are used to generate reports in a variety of formats on demand. Jasper Reports is licensed under the LGPL license.

Objective
-----------
Integrate Jasper Reports into sipXconfig and apply it to generating CDR reports as a first step.

Integration Framework
---------------------------
Jasper reports are generated based on report templates stored in .jrxml files. These report template are preferrably generated with a GUI tool like iReport. iReport is a Java app that can run on many platforms including Windows.

The integration framework shall provide a set of precanned reports for the customer to use. It shall be possible to download the .jrxml files of these reports so that the user can load them into iReports and change them (i.e. change the logo or other parts of the layout).

It shall further be possible for the user to upload modified or new .jrxml files in order to define custom reports. sipXconfig shall allow the generation of both precanned and custom reports on demand by the admin.

Reports shall be provided in different formats, including .pdf. It would be desirable to have the report display inside sipXconfig as well offering a nice and professional layout or graphical representation of the data. It shall be possible to download and safe the reports in .pdf format.

CDR Report Definition for precanned reports:
------------------------------------------------------
1) CDR table. This report provides identical information as compared to the currently existing CDR table. It shall be possible to define a date range. Total call duration and talk time shall be distinguished.

2) CDR report grouped by extension. This report shall group all the calls by extension. Outgoing calls shall be separated from incoming calls.

3) CDR Graph 10 most active extensions. This report provides a stacked bar chart of the 10 extensions with the highest number of calls. Outgoing calls, incoming calls, and internal calls shall be colored differently as a stacked bar.

4) CDR Graph 10 most active callers: This report provides a bar chart of the 10 external numbers from which most of the calls are received.

5) CDR Graph call volume. This graph shall provide call volume (number of calls) per day of week, week, month as a stacked bar chart. Outgoing calls, incoming calls and internal calls shall be colored differently in the bar.

6) CDR Graph number of minutes. This graph shall provide number of minutes per day of week, week, month as a stacked bar chart. Outgoing calls, incoming calls and internal calls shall be colored differently in the bar.

7) CDR Graph number of minutes per extension. This graph shall provide a stacked bar chart of the 10 extensions with the highest number of minutes. Incoming, outgoing and internal calls shall be colored differently.

8) CDR Graph pie chart of failed vs. successful calls. A pie chart provides overall statistics about failed vs. successful calls.

For all the graph charts a table shall be provided that prints the data used to generate the graph.

Reports shall provide a nice title, formt the table with some colors, use small font so that the report does not get too big. There shall be space to put a logo on the report. For all reports it shall be possible to specify a date range. An error shall be printed if no data is available in the database for the specified date range.

More info:
------------
http://www.jasperforge.org/
Books on Jasper Reports and iReport on Amazon
http://www.jasperforge.org/jaspersoft/opensource/business_intelligence/ireport/

Description

This issue proposes the integration of Jasper Reports into sipXconfig to provide flexibility for all reporting needs of sipXecs. What is Jasper Reports? ---------------------------- Jasper Reports is an open source reporting application written in Java. It is similar to Crystal Reports in functionality in that it allows the creation of report templates that then are used to generate reports in a variety of formats on demand. Jasper Reports is licensed under the LGPL license. Objective ----------- Integrate Jasper Reports into sipXconfig and apply it to generating CDR reports as a first step. Integration Framework --------------------------- Jasper reports are generated based on report templates stored in .jrxml files. These report template are preferrably generated with a GUI tool like iReport. iReport is a Java app that can run on many platforms including Windows. The integration framework shall provide a set of precanned reports for the customer to use. It shall be possible to download the .jrxml files of these reports so that the user can load them into iReports and change them (i.e. change the logo or other parts of the layout). It shall further be possible for the user to upload modified or new .jrxml files in order to define custom reports. sipXconfig shall allow the generation of both precanned and custom reports on demand by the admin. Reports shall be provided in different formats, including .pdf. It would be desirable to have the report display inside sipXconfig as well offering a nice and professional layout or graphical representation of the data. It shall be possible to download and safe the reports in .pdf format. CDR Report Definition for precanned reports: ------------------------------------------------------ 1) CDR table. This report provides identical information as compared to the currently existing CDR table. It shall be possible to define a date range. Total call duration and talk time shall be distinguished. 2) CDR report grouped by extension. This report shall group all the calls by extension. Outgoing calls shall be separated from incoming calls. 3) CDR Graph 10 most active extensions. This report provides a stacked bar chart of the 10 extensions with the highest number of calls. Outgoing calls, incoming calls, and internal calls shall be colored differently as a stacked bar. 4) CDR Graph 10 most active callers: This report provides a bar chart of the 10 external numbers from which most of the calls are received. 5) CDR Graph call volume. This graph shall provide call volume (number of calls) per day of week, week, month as a stacked bar chart. Outgoing calls, incoming calls and internal calls shall be colored differently in the bar. 6) CDR Graph number of minutes. This graph shall provide number of minutes per day of week, week, month as a stacked bar chart. Outgoing calls, incoming calls and internal calls shall be colored differently in the bar. 7) CDR Graph number of minutes per extension. This graph shall provide a stacked bar chart of the 10 extensions with the highest number of minutes. Incoming, outgoing and internal calls shall be colored differently. 8) CDR Graph pie chart of failed vs. successful calls. A pie chart provides overall statistics about failed vs. successful calls. For all the graph charts a table shall be provided that prints the data used to generate the graph. Reports shall provide a nice title, formt the table with some colors, use small font so that the report does not get too big. There shall be space to put a logo on the report. For all reports it shall be possible to specify a date range. An error shall be printed if no data is available in the database for the specified date range. More info: ------------ http://www.jasperforge.org/ Books on Jasper Reports and iReport on Amazon http://www.jasperforge.org/jaspersoft/opensource/business_intelligence/ireport/

Show »

All

Comments

Work Log

Change History

Sort Order:

[ Permlink | « Hide ]

Martin Steinmann - 2008-02-21 18:44

Call type is currently not available in the CDR database (see XECS-1084). Therefore, as a phase 1 the proposal is to only provide reports that do not require call state. Call state can then be added to the DB later.

[ Permlink | « Hide ]

Michael Haag - 2008-02-22 00:49

pls include a table-type report which lists all call failures (including failure reason and status, as per XCF-2134). Also, incude a graph-type report which shows errors (by type) as a percentage of all calls.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-04-18 11:05

The prototype patch for integration with jasper reports.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-04-18 11:11

I attached also jars which need to be included in sipXconfig.

[ Permlink | « Hide ]

Martin Steinmann - 2008-04-18 15:16

Would it be possible to attach some more screenshots that show how a user would invoke the new reporting capability and also some sample reports?

What we would like to end up with is a set of pre-canned reports that go with the product. The xml files that define these reports should be downloadable from the admin UI so that the admin can import them into iReports and customize. The admin should then be able to upload such customized or new report definition files that are used for report generation.

Reports should be able to use all the data available in the DB (e.g. CDR, ACD, etc.). The admin should be able to create all kinds of reports using whatever dataset fits the purpose. In particular reports should be able to include table and graph representations of the data. For the pre-canned reports we need some illustrative examples that show the capabilities of this. Hope this makes sense.

[ Permlink | « Hide ]

Martin Steinmann - 2008-04-18 15:17

Also, please indicate the source and license for the jar files included above that come from a third party.

[ Permlink | « Hide ]

Bogdan Brezoi - 2008-04-25 09:09

Additional screenshots uploaded (PDF example) . This screenshots shows the download/open dialog that appears when user click on PDF link and the resulting PDF file (representing the cdr table for the selected period). These reports and screenshots are informative.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-06-09 07:33

I made a new patch that contains a new custom component (ReportComponent) with following functionalities:
- downloads the jasper design file (.jrxml file) of the report;
- generates reports (html, pdf, csv and xls formats) based on the input data and the jasper design file;
- displays the html reports in a new web browser page;
- downloads reports in pdf, csv and xls formats;
- uploads a new jasper design file (if the new uploaded design file can't be compiled or filled with data then it will be rolled back to the previous report design file).
For a new report type a new instance of this component will be used.

The /var/sipxdata/reports directory contains the following files:
- precanned report design files;
- the new uploaded report design files (that overides the coresponding precanned report design files);
- the backups for the previously report design files in order to be able to replace the bad uploaded report design file;
The reports (in html, pdf, csv and xls formats) are generated under /var/sipxdata/tmp directory.

The patch contains only two types of reports (for CDR table and grouped by extension). I still investigate how to generate graph charts reports.

Please keep in mind that after compiling report designs, a jasper print must be filled with data (jasper data source object). Due to this, for now, isn't possible to create reports using whatever dataset fits the purpose. The data set used contains CDRs registered between user input parameters (start/end date).
Still investigate how to offer support for report design files that contains SQL Queries (to use all the data available in the DB).

New dependencies:
1 - JasperReports
Home page: http://sourceforge.net/projects/jasperreports/
Version: 3.0.0
License: GNU Library or Lesser General Public License (LGPL)
Download: http://sourceforge.net/project/showfiles.php?group_id=36382

2 - iText, a JAVA-PDF library (internally used by jasper for rendering PDF format)
Home page: http://sourceforge.net/projects/itext
Version: 2.1.2
License: License: GNU Library or Lesser General Public License (LGPL), Mozilla Public License 1.1 (MPL 1.1)
Download: http://sourceforge.net/project/showfiles.php?group_id=15255

3 - Apache POI - Java API To Access Microsoft Format Files (internally used by jasper for rendering XLS reports)
Home page: http://poi.apache.org/
Version: 3.0.2
License: Apache License, Version 2.0 (http://apache.org/licenses/LICENSE-2.0)
Download: http://www.apache.org/dyn/closer.cgi/poi/release/

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-06-09 07:36

Please find attached some screenshots: 0004.JPG, 0005.JPG and 0006.JPG.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-06-09 07:39

Please find attached precanned jasper report design files for CDR table (cdr-table-report.jrxml) and
CDR report grouped by extension (cdr-extension-report.jrxml).

[ Permlink | « Hide ]

Martin Steinmann - 2008-06-09 18:47

I am moving this into the current sprint, but we can discuss further if needed. Achieving a first step is required so that we have a basis for further exploration, feedback and improvement.

Being able to generate reports based on an SQL query would be very desirable. This would mean that report uploading only takes place in one place and not per report. The desired solution is a capability where we have a set of pre-canned standard reports plus the ability for the admin to upload new report templates using the full dataset available in the DB.

An example of a graphics report is also highly desirable.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-06-27 07:19

I made a another patch (0003-~~XCF-2286~~.patch) with contains the previous patch and graphics report and I added new screen shots: 0006.JPG and 0007.JPG

For that, I introduced another two dependencies:
- JFreeChart is a Java chart library
Home page: http://www.jfree.org/jfreechart/
Version: 1.0.10
License: GNU Lesser General Public Licence (LGPL)
Download: http://sourceforge.net/project/showfiles.php?group_id=15494

- JCommon is a collection of useful classes used by JFreeChart
Home page:http://www.jfree.org/jcommon/index.php
Version: 1.0.13
License: GNU Lesser General Public Licence (LGPL)
Download: http://sourceforge.net/project/showfiles.php?group_id=15494

Also I changed iText version library from 2.1.2 to 1.5.4 because I noticed some errors with it.
Download from: http://sourceforge.net/project/showfiles.php?group_id=15255&package_id=12596

Please ignore previously patch (0002-~~XCF-2286~~.patch).

[ Permlink | « Hide ]

Martin Steinmann - 2008-06-27 08:19

Very nice graphs!

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-07-07 04:00

Finally, I made a new patch that contains the previous patch (simple and graph reports) plus reports based on an SQL query:
SIPXCDR Query Report (for SIPXCDR database) and SIPXCONFIG Query Report (for SIPXCONFIG database). Either report have a report-design file attached: cdr-query-repor.jrxml and, respective, cdr-query-report.jrxml. For new reports generation based on these report design files, you must change the "queryString", "parameter" and "textFieldExpression" tags. Parameter tag must match a field from the queried database table(s).

[ Permlink | « Hide ]

Damian Krzeminski - 2008-10-15 15:43

integrated

[ Permlink | « Hide ]

Eric Long - 2008-10-17 10:06

I would also love to see a report showing a timeline of calls. I would be able to see bar graphs for call durations on each of the extensions. It would look similar to a Gannt chart or when you schedule appointments and invite others using Outlook or other program... Extensions on the left-most column, Date and times across the top-most row. This would be useful for seeing when we are exceeding the number of SIP trunks we are using, among other things.

You'd be able to see graphically the timeline of calls. Something like this:

EXT 09:00|09:10|09:20|09:30|09:40|09:50|10:00|10:10|10:20|10:30|10:40|10:50|11:00
1 <-----------------> <---------------> <-> <------------------------------------>
2 <-----------------------------------------------------------> <------> <---------------
3 <-------------> <-------------> <----------------->
4 --------> <-------------------------------------
5 <-> <--> <------> <------------------------------->

ideally, you could control the granularity (show last hour, last 3 hours, last 8 hours, 24....)

[ Permlink | « Hide ]

Kevin Thorley - 2008-10-30 07:59 - edited

Attached a patch for building an RPM with the dependencies for Jasper Reports. Added a new sipx-jasperreports-lib dir to the sipXecs/lib dir. Dependency jars are assumed to be in LIBSRC. Also modified sipXconfig to look for the jasper report dependencies using CHECK_JAR rather than hardcoding them in lib.properties. The previous patch with the jasper reports functionality will need to be modified to remove the dependencies from lib.properties

[ Permlink | « Hide ]

Damian Krzeminski - 2008-11-07 07:24

Several issues fixed in this new patch:
- fix jasper jar references in build.xml files
- fix Eclipse project files
- fix sipXconfig RPM - report directory - not registered
- some Eclipse warnings in the code fixed

[ Permlink | « Hide ]

Damian Krzeminski - 2008-11-07 07:34

Please hold off on checking in the latest patch until dependency jar is built and published for all supported platforms.

[ Permlink | « Hide ]

Laurentiu Ceausescu - 2008-11-11 08:26

I removed the report for SIPXCONFIG database

[ Permlink | « Hide ]

Kevin Thorley - 2008-12-01 21:45

Committed changes in r14134. Changed code to precompile reports at build time, eliminating the need to use javac at runtime. The upload fields need to be removed from UI as a result.

Modified build to remove log4j jar from build path to allow jasper precompilation

Patch from Laurentiu Ceuasescu - Thanks Laurentiu!