REP - Reporting

REP is an optional itmSUITE® module that defines functionality to manage and excute reports created by Eclipse BIRT Designer tool.
It allows to load one or more report templates (BIRT.rptdesign file) and to create and to execute report instances through the BIRT Viewer integrated on itmSUITE®.

BIRT Architecture

BIRT (Business Intelligence Reporting Tool)is an open source tool installed as Eclipse IDE plug-in for report developing. It has origin from collaboration between Actuate (along with IBM) and Eclipse.org open source community.
BIRT is a Java based tool that allows to extract data from many data source types for report generation in several output format (pdf, xlsx, docx, etc.).

Birt Architecture

BIRT architecture includes two main components:

1. Report Designer: a UI component to create report template (.rptdesign file)
2. Report Engine: runtime component for report rendering

The Design Engine generates a .rptdesign file(in XML format) structured according to configurations applied by user (by BIRT Designer) and the Report Engine, through BIRT Viewer, parses it and renders it as report in html (or other formats).

BIRT Designer

BIRT Designer is a tool (plug-in) integrated to the Eclipse IDE that defines a user friendly interface to create BIRT report. It can be enabled from toolbar on Windows > Open Perspective > Report Design. BIRT Designer is composed of several Eclipse views to create/configure a report:

View	Meaning
Data Explorer	Allows the data management extracted from a specific data source (i.e. database, xlsx file, cvs file, etc.) in order to define data sets and parameters report
Navigator	A projects management view. Every project contains one or more report template.
Layout	Main editor in which user can drag/drops and manages report items to define output report layout
Palette	Contains all graphic report items (label, table, chart, etc...) to add to report layout
Property Editor	Menu from which user can configure report items properties
Scripting Editor	Editor available for each report items in which insert event drivern JavaScript/Java code
Report Preview	View on which launch a report preview with a limited extracted data

Birt Report Creation

A report can be outlined in a bottom-up 4-levels structure:

1. Data: data extracted from data source (database, raw file, cvs, etc...)
2. User Data: extracted data modified by user (e.g., data type changing)
3. Business Logic: data elaborated according to event driven script (JavaScript/Java code)
4. Prensentation: data bound to report items (tables, charts, etc...) and shown on report layout

In BIRT Designer all reports can be rappresented as tables, charts, crosstab, textual document or as combination of these items. In this chapter it will be described all steps to create a sample table containing itmSUITE tickets info:

Id	Ticket Type	Ticket Status	Owner	Project	Creation Date
1	Incident	Opened	Williams	Project 1	01/01/2017
2	Problem	Completed	Michael	Project 2	12/02/2017
3	Release	Closed	John	Project 3	23/05/2017

Report Template

A report template is XML-like file that contains report items, datatsource, dataset and scripting references. It is a .rptdesign exstension file interpreted by Report Engine in order to generate report output.

Step	Description
1. Project Creation	Click on File > New > Project on toolbar Select Report Project on Business Intelligence and Reporting Tools section Tape "My Report" as Project name and click on Finish button
2. Report Template	Click on File > New > Report on toolbar Select "My Report" Project created on previous steps Tape "MyTickets.rpdesign" as report template name and click on Next button Create a blank report. Other default models: Simple Listing, Dual Column Listing, Grouped Listing, Chart Listing Click on Finish button. Report template file has been created in workspace

Data Source

In BIRT Designer, data can be extracted from various data sources such as database, flat file, csv file, etc. The detailed steps to create a data source bound to a database are explained below:

Step	Description
3. Data Source Creation	Select Data Source item from Data Explorer view Click on New Data Source from context menu Select JDBC Data Source and insert a data source name (e.g., "MyTicketSource")
4. Database Connection	Click on Manage Driver… and select a jdbc driver (e.g., "sqljdbc4.jar" file for Microsoft SQL Server) Fill following fields, such as: Driver Class: "com.microsoft.sqlserver.jdbc.SQLServerDriver (v4.0)" (for MS SQL Server) Database URL: "jdbc:sqlserver://ip_address:db_port;DatabaseName=Nome_DB" User Name: sql username Password: sql password Click on Test Connection to check connection to database and then on Finish

Data Set

In BIRT Designer data set are items that contain data extracted from data source (e.g., by means sql query) in order to populate report items (tables, charts, parameters, etc...) whom they are bound.

Step	Description
5. Data Set Creation	Select Data Set item from Data Explorer view Click on New Data Sets from context menu Select "SQL Select Query" and insert Data Set name. Click on Next
6. Query Configuration	Insert sql query statements in Query Text form and click on Finish In Data Set editor following options are available : Output Columns: shows column name defined in query Preview Results: shows preview query result Parameters: defines parameters for BIRT parametric query Filters: filters data to include in Data Set

In sample ticket info report four data set will be created:

Data Set	SQL query	Description
Owner	<syntaxhighlight lang="sql"> select distinct owner from TICKET </syntaxhighlight>	Extracts owner name from the TICKET table
Project	<syntaxhighlight lang="sql"> select name from PRJ </syntaxhighlight>	Extracts project name from the PRJ table
Ticket Type	<syntaxhighlight lang="sql"> select name from TICKETTYPE </syntaxhighlight>	Extracts ticket type name from the TICKETTYPE table
Ticket info	<syntaxhighlight lang="sql"> select t.ID ,tp.name as TICKET_TYPE ,w.name as TICKET_STATUS ,t.owner as OWNER ,p.name as PROJECT ,t.creationDate as CREATION_DATE from TICKET t inner join TICKETTYPE tp on (tp.id = t.TICKETTYPE_ID) inner join WORKFLOWTICKETOPSTATUS w on (w.id = t.WORKFLOW_TICKET_OPSTATUS_ID) inner join PRJ p on (p.id = t.PROJECT_ID) where t.creationDate >= ? and t.creationDate <= ? </syntaxhighlight>	Parametric query that extracts ticket info in order to populate report table