Differenze tra le versioni di "REP - Reporting"

Da itm wiki.
(→‎Data Set)
(→‎Add report on itmSUITE® menu)
 
(32 versioni intermedie di uno stesso utente non sono mostrate)
Riga 1: Riga 1:
 
REP is an optional itmSUITE® module that defines functionality to manage and excute reports created by Eclipse BIRT Designer tool.<br/>
 
REP is an optional itmSUITE® module that defines functionality to manage and excute reports created by Eclipse BIRT Designer tool.<br/>
 
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®.
 
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 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.<br/>
+
[http://www.eclipse.org/birt/ 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.<br/>
 
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 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.).
  
Riga 12: Riga 13:
 
# Report Engine: runtime component for report rendering
 
# 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).
+
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, parse and renders it as report in HTML,or other, format.
 +
 
 +
=== BIRT Report on itmSUITE® ===
 +
In itmSUITE®, the steps required to upload and execute BIRT report previously created through the BIRT Designer are following:
 +
#Data source configuration
 +
#Load report template (.rptdesign file)
 +
#Create a report instance
 +
#Add report on menu and execute it
 +
 
  
 +
==== Data Source Configuration ====
 +
In itmSUITE®, BIRT report data can be extracted only from data sources connected to database.
 +
Data Source settings are accessible on the '''''General > Company > Report Engine > Data Source''''' menu.
  
−
=== BIRT Designer ===
+
[[File:dataSource.png|centre|frame|Data Source list - more data source items can be created]]
−
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:
 
  
 +
Data source creation steps:
 +
# Click on '''Add New''' button
 +
# Fill following fields (* mandatory):
 
{| class="wikitable"
 
{| class="wikitable"
−
! View !! Meaning
+
! Field Name !! Description !! Example Value
 +
|-
 +
| <u>Name</u>* || Logic data source item name || "Sample DB"
 +
|-
 +
| <u>Description</u> || Data source item description || "Sample data source"
 +
|-
 +
| <u>Company</u>* || Company to which data source item is assigned || "Sample Company"
 +
|-
 +
| <u>Status</u>* || Configurable Active/Inactive statuses || "Active"
 
|-
 
|-
−
|<u>Data Explorer</u> [[File:DataExplorer.png|centre]]||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
+
| <u>Type</u>* || Driver type used to connect to data source || "JDBC"
 
|-
 
|-
−
|<u>Navigator</u> [[File:Navigator.png|centre]]||A projects management view. Every project contains one or more report template.
+
| <u>Data Driver</u>* || Driver used to connect to data source || "MSSQL"
 
|-
 
|-
−
|<u>Layout</u> [[File:Layout.png|centre]]||Main editor in which user can drag/drops and manages report items to define output report layout
+
| <u>DB Server</u>* || URL (or IP) of data source server || "192.168.10.5"
 
|-
 
|-
−
|<u>Palette</u> [[File:palette.png|centre]]||Contains all graphic report items (label, table, chart, etc...) to add to report layout
+
| <u>Port</u>* || Connection data source port || "1433"
 
|-
 
|-
−
|<u>Property Editor</u> [[File:PropertyEditor.png|centre]]||Menu from which user can configure report items properties
+
| <u>DB Name</u>* || Database name to which data source item refers || "PMSM_SAMPLE_DB"
 
|-
 
|-
−
|<u>Scripting Editor</u> [[File:ScriptEditor.png|centre]]||Editor available for each report items in which insert event drivern JavaScript/Java code
+
| <u>Username</u>* || Username used to connect to database || -
 +
|-
 +
| <u>Password</u>* || Password used to connect to database || -
 
|-
 
|-
−
|<u>Report Preview</u> [[File:Preview.PNG|centre]]||View on which launch a report preview with a limited extracted data
 
 
|}
 
|}
 +
<ol>
 +
<li value=3>Click on '''Check And Save''' button to check database connection and to save data source settings</li>
 +
</ol>
  
  
−
==== Birt Report Creation ====
+
==== Load report template ====
−
A report can be outlined in a bottom-up 4-levels structure:
+
Report template (.rptdesign) file defines the report structure in a well-formed XML document.
−
# <b>Data:</b> data extracted from data source (database, raw file, cvs, etc...)
+
In itmSUITE®, a report template can be associated to more report instances. Report template configuration is accessible on the '''''General > Company > Report Engine > Report template''''' menu.
−
# <b>User Data:</b> extracted data modified by user (e.g., data type changing)
 
−
# <b>Business Logic:</b> data elaborated according to event driven script (JavaScript/Java code)
 
−
# <b>Prensentation:</b> 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.
+
[[File:reportTemplateList.png|centre|frame]]
−
In this chapter it will be described all steps to create a sample table containing itmSUITE tickets info:
 
  
 +
The report template creation requires following steps:
 +
# Click on '''Add New''' button
 +
# Fill following fields on ''General'' tab:
 
{| class="wikitable"
 
{| class="wikitable"
−
! Id !! Ticket Type !! Ticket Status !! Owner !! Project !! Creation Date
+
! Field Name !! Description !! Example Value
 +
|-
 +
| <u>Name</u>* || Logic report template name || "Sample Report Template"
 
|-
 
|-
−
|1||Incident||Opened||Williams||Project 1||01/01/2017
+
| <u>Description</u> || Report template description || "A report tamplate as sample"
 
|-
 
|-
−
|2||Problem||Completed||Michael||Project 2||12/02/2017
+
| <u>Status</u>* || Configurable Active/Inactive statuses || "Active"
 
|-
 
|-
−
|3||Release||Closed||John||Project 3||23/05/2017
+
| <u>BIRT Report Template</u>* || Load .rptdesign file || "Sample_Report_Template"
 
|-
 
|-
 
|}
 
|}
−
 
+
<ol>
−
===== Report Template =====
+
<li value=3>Fill the following fields on ''Data source'' tab:</li>
−
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.
+
</ol>
−
 
 
 
{| class="wikitable"
 
{| class="wikitable"
−
! Step !! Description
+
! Field Name !! Description !! Example Value
 +
|-
 +
| <u>Data source</u>* || Data source imported from .rptdesign file || "PMSM_DB_TEST"
 
|-
 
|-
−
|<u>1. Project Creation</u>[[File:ProjectCreation.png|centre]]||style="vertical-align:top;"|
+
| <u>Mapped on</u> || itmSUITE® data source item to which report template can refers for data extraction. If it is empty, data source defined in .rptdesign file is used || "PMSM DB Test"
−
* 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
 
 
|-
 
|-
−
|<u>2. Report Template</u>[[File:ReportTemplateCreation.png|centre]]||style="vertical-align:top;"|
+
| <u>Type</u>* || Connection type to data source (always JDBC) || "JDBC"
−
* 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 =====
+
<ol>
−
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:
+
<li value=4>Click on '''Save''' button to save report template settings</li>
 +
</ol>
  
−
{|class="wikitable"
 
−
! Step !! Description
 
−
|-
 
−
|<u>3. Data Source Creation</u>[[File:DataSourceCreation.png|centre]]||style="vertical-align:top;"|
 
−
*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")
 
−
|-
 
−
|<u>4. Database Connection</u>[[File:DatabaseConnection.png|centre]]||style="vertical-align:top;"|
 
−
*Click on '''Manage Driver…''' and select a jdbc driver (e.g., "sqljdbc4.jar" file for [https://www.microsoft.com/en-us/download/details.aspx?id=11774, Microsoft SQL Server])
 
−
*Fill following fields, such as:
 
−
**<u>Driver Class</u>: "com.microsoft.sqlserver.jdbc.SQLServerDriver (v4.0)" (for MS SQL Server)
 
−
**<u>Database URL</u>: "jdbc:sqlserver://ip_address:db_port;DatabaseName=Nome_DB"
 
−
**<u>User Name</u>: sql username
 
−
**<u>Password</u>: sql password
 
−
*Click on '''Test Connection''' to check connection to database and then on '''Finish'''
 
−
|-
 
−
|}
 
  
 +
==== Create Report Instance ====
 +
Report instance defines a BIRT report that can refer to only a report template and can be launched directly from itmSUITE® menu.
 +
A report instance is configurable on the '''''General > Company > Report Engine > Report Management''''' menu
  
−
===== Data Set =====
+
[[File:reportManagementList.png|centre|frame]]
−
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.
 
  
 +
The report instance creation requires following steps:
 +
# Click on '''Add New''' button
 +
# Fill following fields on ''General'' tab:
 
{| class="wikitable"
 
{| class="wikitable"
−
! Step !! Description
+
! Field Name !! Description !! Example Value
 +
|-
 +
| <u>Name</u>* || Report name || "Sample Report"
 +
|-
 +
| <u>Description</u> || Report description || "A sample report"
 
|-
 
|-
−
|<u>5. Data Set Creation</u>[[File:DataSetCreation.png|centre]]||style="vertical-align:top;"|
+
| <u>Status</u>* || Configurable Active/Inactive statuses. If Inactive report cannot be shown on itmSUITE® menu || "Active"
−
* 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'''
 
 
|-
 
|-
−
|<u>6. Query Configuration</u>[[File:QueryConfiguration.png|centre]]||style="vertical-align:top;"|
+
| <u>Template</u>* || Report template name to which report instance refers || "Sample_Report_Template"
−
* 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
 
 
|-
 
|-
 
|}
 
|}
 +
<ol>
 +
<li ''value=3>Check that all report parameters are visible on ''Parameters'' tab:</li>
 +
</ol>
 +
 +
[[File:reportParameter.png|centre|frame]]
 +
 +
<ol>
 +
<li value=4>On ''Filter Designer'' tab, it is possible to customize a report launcher box (at default it contains the same parameters defined on report template):</li>
 +
*Hide/show report parameters
 +
*Add/delete parameters sections
 +
*Edit each report parameter item (read-only,label editing, mandatory, etc.)
 +
*Add (as drag&drop ) <u>Output</u> field to extract report in several format (html, word, excel, pdf, ppt)
 +
*Add (as drag&drop) <u>Open Report in New Window</u> check box to visualize report ouput on a new tab
 +
 +
[[File:reportDesigner.png|centre|frame]]
 +
 +
It is possible to see a report launcher preview by clicking on '''Preview''' button
 +
<li>On ''Assignments'' tab, set report visibility grants on itmSUITE® menu
  
−
In sample ticket info report four data set will be created:
+
[[File:reportAssignment.png|centre|frame]]
 +
 +
by following filter fields:
  
 
{| class="wikitable"
 
{| class="wikitable"
−
! Data Set !! SQL query !! Description
+
! Filter Name !! Description
 
|-
 
|-
−
|Owner
+
| <u>Role</u> || it limits visibility to users that belong to following roles: ''superadmin'', ''administrator'', ''manager'', ''pm'', ''resource'', ''requester''. It works also as prefilter for <u>Resource</u> filter
−
|<syntaxhighlight lang="sql">
 
−
select distinct owner
 
−
from TICKET
 
−
</syntaxhighlight>
 
−
|Extracts ''owner'' name from the TICKET table
 
 
|-
 
|-
−
|Project
+
| <u>Resource</u> || it limits visibility to selected ''resources''
−
|<syntaxhighlight lang="sql">
 
−
select name
 
−
from PRJ
 
−
</syntaxhighlight>
 
−
|Extracts ''project'' name from the PRJ table
 
 
|-
 
|-
−
|Ticket Type
+
| <u>Group</u> || it limits visibility to members of selected ''groups''
−
|<syntaxhighlight lang="sql">
 
−
select name
 
−
from TICKETTYPE
 
−
</syntaxhighlight>
 
−
|Extracts ''ticket type'' name from the TICKETTYPE table
 
 
|-
 
|-
−
|Ticket info
+
| <u>Organization unit</u> || it limits visibility to ''resources'' that belong to selected ''organization units''
−
|<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
 
 
|-
 
|-
 
|}
 
|}
 +
</li>
 +
<li>
 +
On Filter tab, it is possible to manage personal filters selectable by user directly from report
 +
 +
[[File:reportFilter.png|centre|frame]]
 +
</li>
 +
</ol>
 +
 +
==== Add report on itmSUITE® menu ====
 +
After report instance has been created on '''''Report Management''''', it needs to add a report entry in itmSUITE® menu.
  
−
<!--
+
[[File:reportMenu.png|centre|frame]]
−
===== Parameters =====
 
  
−
===== Report Layout =====
+
Report menu entry is configurable on the '''''General > Company > Main Navigation Menu''''', under the <u>''Not Used/Reporting''</u> section.<br/>
−
-->
+
'''Important:''' ''administrator'' re-login is required in order user can drag and drop new report entry from <u>''Not Used/Reporting''</u> section to any <u>''Main Navigation Menu''</u> section.<br/>
 +
Finally report can be run in new itmSUITE® menu entry by users that have visibility grants.

Versione attuale delle 15:18, 18 giu 2018

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, parse and renders it as report in HTML,or other, format.

BIRT Report on itmSUITE®

In itmSUITE®, the steps required to upload and execute BIRT report previously created through the BIRT Designer are following:

  1. Data source configuration
  2. Load report template (.rptdesign file)
  3. Create a report instance
  4. Add report on menu and execute it


Data Source Configuration

In itmSUITE®, BIRT report data can be extracted only from data sources connected to database. Data Source settings are accessible on the General > Company > Report Engine > Data Source menu.

Data Source list - more data source items can be created

Data source creation steps:

  1. Click on Add New button
  2. Fill following fields (* mandatory):
Field Name Description Example Value
Name* Logic data source item name "Sample DB"
Description Data source item description "Sample data source"
Company* Company to which data source item is assigned "Sample Company"
Status* Configurable Active/Inactive statuses "Active"
Type* Driver type used to connect to data source "JDBC"
Data Driver* Driver used to connect to data source "MSSQL"
DB Server* URL (or IP) of data source server "192.168.10.5"
Port* Connection data source port "1433"
DB Name* Database name to which data source item refers "PMSM_SAMPLE_DB"
Username* Username used to connect to database -
Password* Password used to connect to database -
  1. Click on Check And Save button to check database connection and to save data source settings


Load report template

Report template (.rptdesign) file defines the report structure in a well-formed XML document. In itmSUITE®, a report template can be associated to more report instances. Report template configuration is accessible on the General > Company > Report Engine > Report template menu.

ReportTemplateList.png

The report template creation requires following steps:

  1. Click on Add New button
  2. Fill following fields on General tab:
Field Name Description Example Value
Name* Logic report template name "Sample Report Template"
Description Report template description "A report tamplate as sample"
Status* Configurable Active/Inactive statuses "Active"
BIRT Report Template* Load .rptdesign file "Sample_Report_Template"
  1. Fill the following fields on Data source tab:
Field Name Description Example Value
Data source* Data source imported from .rptdesign file "PMSM_DB_TEST"
Mapped on itmSUITE® data source item to which report template can refers for data extraction. If it is empty, data source defined in .rptdesign file is used "PMSM DB Test"
Type* Connection type to data source (always JDBC) "JDBC"
  1. Click on Save button to save report template settings


Create Report Instance

Report instance defines a BIRT report that can refer to only a report template and can be launched directly from itmSUITE® menu. A report instance is configurable on the General > Company > Report Engine > Report Management menu

ReportManagementList.png

The report instance creation requires following steps:

  1. Click on Add New button
  2. Fill following fields on General tab:
Field Name Description Example Value
Name* Report name "Sample Report"
Description Report description "A sample report"
Status* Configurable Active/Inactive statuses. If Inactive report cannot be shown on itmSUITE® menu "Active"
Template* Report template name to which report instance refers "Sample_Report_Template"
  1. Check that all report parameters are visible on Parameters tab:
ReportParameter.png
  1. On Filter Designer tab, it is possible to customize a report launcher box (at default it contains the same parameters defined on report template):
    • Hide/show report parameters
    • Add/delete parameters sections
    • Edit each report parameter item (read-only,label editing, mandatory, etc.)
    • Add (as drag&drop ) Output field to extract report in several format (html, word, excel, pdf, ppt)
    • Add (as drag&drop) Open Report in New Window check box to visualize report ouput on a new tab
    ReportDesigner.png

    It is possible to see a report launcher preview by clicking on Preview button

  2. On Assignments tab, set report visibility grants on itmSUITE® menu
    ReportAssignment.png

    by following filter fields:

    Filter Name Description
    Role it limits visibility to users that belong to following roles: superadmin, administrator, manager, pm, resource, requester. It works also as prefilter for Resource filter
    Resource it limits visibility to selected resources
    Group it limits visibility to members of selected groups
    Organization unit it limits visibility to resources that belong to selected organization units
  3. On Filter tab, it is possible to manage personal filters selectable by user directly from report
    ReportFilter.png

Add report on itmSUITE® menu

After report instance has been created on Report Management, it needs to add a report entry in itmSUITE® menu.

ReportMenu.png

Report menu entry is configurable on the General > Company > Main Navigation Menu, under the Not Used/Reporting section.
Important: administrator re-login is required in order user can drag and drop new report entry from Not Used/Reporting section to any Main Navigation Menu section.
Finally report can be run in new itmSUITE® menu entry by users that have visibility grants.

Estratto da "http://wiki.itmsuite.eu/index.php?title=REP_-_Reporting&oldid=4001"