Write your own report

In this section we will describe how to create new Twproject reports using Jasper Reports.

Your project data are carefully collected and maintained by Twproject and this represents one of the most valuable aspect of Twproject adoption.
How can you extract your data and present it to your users? Actually Twproject has several pages with dynamic filtering capabilities that extract meaningful data, but a customizable reporting systems was missing until version 5.5.
In this release we have integrated Jasper Reports, one of the most powerful reporting engine. Jasper reports adds the capability to create your own reports in minutes using an intuitive graphical editor.
Reports are integrated with the flexible Twproject security structure in an easy and practical way, without forcing users to write complex query and rules.


JasperReports
is a reporting tool by JasperSoft (recently acquired by Tibco) widely used by the enterprise oriented Java community.

Reports can be easily designed with the friendly, powerful, free and open-source iReport Designer or with Jaspersoft Studio.

Jasper Studio is the main product and it’s based on Eclipse. iReport is simpler to configure, but unfortunately will be discontinued soon. If you already know Eclipse interface, use Jasper Studio, otherwise use iReport. Actually the configuration steps are very similar.

For our purposes iReport works perfectly.

Drivers and classpath

Once iReport  is installed the first step is to add database library to the editor.

1)  Click on tools->options and then on “classpath” tab.

image

Database drivers are included on Twproject distribution, you can find them here:

  • SQL server: [twproject root]\WEB-INF\lib\jtds-1.2.5.jar
  • My SQL: [twproject root]\WEB-INF\lib\mysql-connector-java-5.1.6-bin.jar
  • Oracle: [twproject root]\WEB-INF\lib\ojdbc5.jar
  • PostgreSQL: [twproject root]\WEB-INF\lib\postgresql-9.0-801.jdbc3.jar
  • HSQLDB: [twproject root]\WEB-INF\lib\hsqldb.jar

Click on “Add jar” button and select the driver for the database you are using.

Then you should also add Twproject classes, in case we need to use some formatting functions.

Click “Add Folder” and select [twproject root]\WEB-INF\lib\classes folder

Once done we are ready to connect to Twproject database

1) click on “Step 1: Create database connection”

image

3) choose Database JDBC Connection

image

4) write the name of this connection, “TW5 test” for instance

5) select the right database driver:

insert your server connection data, database name, user and password:

image

6) test the connection

image

If the connection is fine, we can proceed writing our first report.

Create your first report

Click on “Step 2 : create a new report“

image

The report wizard will pop-up. There are many templates:

image

Select the one you like the most and “Launch Report Wizard”.

image

Insert the report name and location carefully: report name and report location are crucial information for integrating Twproject and iReport. Read the dedicated section below.

Click “next”

image

select your just created data source “TW5 test”; it’s now time to think about the query.

In this example we would like to report time spent by each resource on their projects.

In order to write a query, the knowledge of Twproject data structure is mandatory. Even if table names are quite immediate I suggest to have a look to this section:

https://twproject.com/support/twproject-advanced-usage/connections/database-structure/

If you are a SQL wizard you can write your query directly, but if you prefer something more “visual” click on “Design query” button:

image

Drag the tables you need for your query.

In our case work hours are on twk_worklog table. Worklog records are always linked to an assignment. Assignments create relations between resources and task weighted with a role. So we need to add “twk_assignment”, “tsk_task”, “twk_resource” and “olpl_role”. The query editor tool will use sql foreign keys for create joins.

Save the query and click “next”.

image

You can select fields you want to see on your report. In our case we will add them manually. Click “next”

Here you will define some groups, but this is not mandatory, you can eventually add groups after.

image

Click “next” and then “finish”.

Your report “skeleton” is almost ready:

image

Notice that a report has many sections like “Title”, “page header,” two group header, details, and then the footer part.

If you click on “preview” you will see something ugly like this:

image

Go back on the designer and let ‘s do some changes:

Change title, use task and resource name instead of ids

Drag  worklog insertion date, action, and time spent from “fields” area to our report on “details” section:

image

With these simple change it will look like:

image

A little bit better, but:

  1. dates are in a strange format: to fix them, right click on the field and click on “field pattern”. Choose “Date” and then the format
  2. there are “null” in case of no description inserted: select the field and from properties box select “Blank when null”
  3. time spent are in milliseconds:  in this case you have to use one on Twproject utility function. Right click on the field and choose “Edit expression” then write:
    org.jblooming.utilities.DateUtilities.getMillisInHoursMinutes($F{twk_worklog_duration}). This function will transform milliseconds in a Hours:Minutes

Here we are:

image

Now you may want to have partial sums on group header/footers. This can be done introducing variables.

In this case we will need a variable for summing worklog on a task group say ”sumGroup1”:

image

Drag the just created variable on “group footer 1” and format the result using “getMillisInHoursMinutes” function as above:

image

You can play with the designer to optimize your report. When you are ready save it!

iReport and Twproject integration

Once you have your report you can run it inside iReport, directly to the database, but if you want to integrate it in Twproject in the right way (e.g.: respecting security) you have to know some additional detail.

The report we have just created is already available in your Twproject (if you created it in the right place with an unique name Smile).

To see installed reports go to Admin –> Customizations –> Forms, plugins and reports.

In the “Additional iReports” section your report should be listed. If not click on “reload plugins” button (if “not” again there is a problem on the report, see logs).

screen977

You should already have a special widget (“Report list” on wp_genericReports.jsp file) installed on your Twproject. Add it to your dashboard if you want to access you reports directly from Twproject.
See https://twproject.com/support/twproject-advanced-usage/customizations/dashboard-customization/ for create a new portlet/widget

image

The widget is already filled with your reports just because you are administrator otherwise it will be empty until you assign the correct permission as described below.

But who can run these reports? How reports are secured in Twproject?

Report names are used for generating a permission you must select to grant access to a report, this is why they must have unique meaningful names.

Every report extends the standard Twproject role based implementation adding a special permission, one for each report. These permissions can be assigned to a role exactly like the standard ones, both at global level or at project level.

Here the role editor extended with report permissions:

screen978

For a deep discussion on Twproject security see: https://twproject.com/support/introduction-security/

Once you added a report permission to a role, that report will be visible to the users with that role.

That said, in order to integrate JasperReports with Twproject we have to take care of security and data visibility.

Accessing the database directly from the report engine can be acceptable for some “high level” reports, but as you know, Twproject security model is very flexible, allowing to see only an allowed set of data for each user. How can be possible to create a report that respect Twproject security model without writing complex queries?

Report location and security

We worked hard to solve this problem and we found an easy and practical solution.

First of all we divide reports in “global” and “entity related”.

Global reports are the most flexible and easy to build (like the one just created); they can use any kind of data from Twproject: task, worklog, resources or issue, without restriction.

Global reports will test security at global role level only: in the worst case who can access the report can read its data (e.g project and costs), even if she/he doesn’t have access to “projects” the data comes from.
If your reports show sensible data, grant access to them wisely.

As you (should Smile ) know, Twproject security uses at top level the concept of “security area” (see here for details: https://twproject.com/support/areas/). Having the permission to see “report x” on “area 1” doesn’t mean you can see the same report on “area 2”. To solve this problem, Twproject passes to the report the list of areas where you have that permission. You should use that list to filter report data showing, for instance, only project/resources/issues/worklog from “area 1” and not from “area 2”. We will explain how to use that parameter in the following.

Entity related reports, instead, are related to a main Twproject entity such as task, resource, issue and the access is tested object by object, singularly.

Twproject cannot inspect the report and “guess” if it is “global”, related to tasks, to issues and so on; you have to instruct Twproject about report contents by putting it on a folder structured as follows:

image

where “ACME” is your company name.

So the reports on the “report” root are considered “global” while the ones in “issue”, “resource”, “task” or “worklog” folders are considered related to the corresponding Twproject entity.

Entity report are shown on entity list pages such as task list, resource list, worklog analysis and so on.

Entity related reports will be accessible using the print menu on each section (task, resource, issues, worklog):

Entity reports will receive from Twproject the list of entity’s ids currently visible on the page on those you have permission to see that report.

A clarifier example: If you are today involved on two project, one as “project manager” and another one as “worker”, the task list page will show you both projects .

Consider that by default, as PM, you can see project costs, but as worker you cannot. If you’ve created a report for cost analysis, you will give the access to that report to PMs only.

Building the set of ids for the report, Twproject will check entity by entity if you are allowed to se it, and in this case, project where you are assigned as worker, will be filtered out.

Summing up the report will apply to entities that match filter (today tasks) and security constraint.

The list of allowed entities is passed to your report, but you have to use it!

Reports will receive the following parameters:

  • global reports: AREA_IDS
  • task reports: TASK_IDS e TASK_DESCENDANTS
  • resource reports: RESOURCE_IDS e RESOURCE_DESCENDANTS
  • issue reports: ISSUE_IDS
  • worklog reports: WORKLOG_IDS

Use these parameters in your report as follow.

For a global report you should add to the query something like:

$X{ IN ,twk_task.area,AREA_IDS} 

eventually in “AND” with other clauses.

For entity related reports add something like:

$X{ IN ,twk_worklog.id,WORKLOG_IDS}

or

$X{ IN ,twk_task.id,TASK_IDS}

Twproject comes with several reports that you can use as starting point. (If you copy an existing report remember that you have to change both file name and “report name” property in iReport)