We have to track progress by summarizing resolved vs unresolved issues.
Fetching more data from JIRA and binding them to the Table item
We need data to make any report those data obviously, therefore we need to fetch some data from JIRA in order to know whether an issue is resolved or not.
Select the Data Explorer view and double click on the Reported Issues data set to open the edit dialog. Then click on the Query element on the left panel and add the following column to the current SQL for JIRA query:
i.resolutionid is not NULL as "Resolved"
So the new query is:
select i.key as"Issue", i.summary as "Summary", t.name as "Type", t.url as "TypeUrl", s.name as "Status", c.colorname as "Status Color", i.resolutionid is not NULL as "Resolved" from ISSUES i join ISSUETYPEDEFINITIONS t on t.id = i.typeid join ISSUESTATUSDEFINITIONS s on s.id = i.statusid join ISSUESTATUSCATEGORYDEFINITIONS c on c.id = s.categoryid where i.jql = 'reporter = currentUser()'
Click on Preview Results and have a look at the new column data:
Click on the OK button to accept the changes and see how the new column has been added to the Data Set on the Data Explorer view:
However, the table is not aware about the new column. Select the Table on the Outline view and the Binding tab on the Property Editor:
We have to bind the new column to the Table: click on the Add... button to open the New Data Binding dialog, the on the button to bring up the Expression Builder dialog. Select the Available Data Sets category, the Reported Issues Sub.Category and finally double click on the Resolved elment to add the binding expression:
dataSetRow["Resolved"]
Click on the OK button to close the dialog and fill out the Column Binding Name field with Resolved with Boolean Data type
Click on the OK button to close the New Data Binding dialog. The Data Set's Resolved column is now binded to the Table:
Visualizing Resolved data as striketrhough issues
Once the data are binded to the item, we can use them. We will add a striketrhough line for the resolved issues instead of a new column on the table: edit the Issue Dynamic Text on the Table and replace the Javascrit from:
"<a href='"+vars["JIRA Base URL"]+"/browse/"+row["Issue"]+"' target='_top'>"+row["Issue"]+"</a>"
to:
var issue = row["Issue"];
if(row["Resolved"])
issue = "<strike>"+row["Issue"]+"</strike>";
"<a href='"+vars["JIRA Base URL"]+"/browse/"+row["Issue"]+"' target='_top'>"+issue+"</a>"
In the script above the issue key is stored into a Javascript variable. if the new column bind row["Resolved"] is true then the issue key is envolved with the <strike> HTML5 elememt.
Preview the results: when the issue is Done then is displayed with a strikethrough line:
Grouping
Data on the Table is not grouped. Therefore we used the Data Set as input for the Pie Chart to group by Type. Data can also be grouped on tables: Select the Table item on the Outline view. Then expand its childen to see the Groups. Right click on it and select Inser group:
Fill out the Name (Table Group by Type) and select the Type the Group on option. The dialog will automatically fill the Item Expression with the binded data (row["Type"]):
The table view is added to the Tbale item in the Outline view and the Layout:
Run the report to see how data are now grouped by their issue types:
Formatting the Table Group's Header will help to visualize groups better. Select it on the Layout and set the Background Color to Silver in the Properties Editor:
Now, thhe groups highlight better amiog the rest of the data:
Aggregation
We are going to show the progress (resolved vs unresolved issues) for each Type group.
Drag&drop the Aggregation item from the Quick Tools on the Palette view to the Table Group Footer Row and Count the Issues Resolved within each Table Group Type:
The report displays the amount of resolved issues for each type group:
The BIRT designer has created a new binding on the Table for the Aggregation item so it is possible to ise the aggregated data as input for other items like charts:
To see the progress, we need a new aggretation item to count the unresolved issues. Pelase, repeat the actions described above in order to crate a similar aggregation with the conditon:
row["Resolved"] == false
This is the new report:
We will replace the sub-totals by a bar graph. Select both aggregation items from the Outline view and set Dislplay property to No visible:
Right click on the Grid's last cell (table) and select Insert Row Above:
A new empty Cell has been added between the Pie Chart and the table items:
Drag&drop a new Chart ittem and select a Stacked Bar char type:
In text tab, check the Use Data from option and select the table (it requires that you provide a name for the Table's in its Properties Editor). Select row["Table Group by Type"] as Category X Serires to see the bars grouped by status (one bar per status). Then select the row["Count Resolved Type"] aggegated data as the Series 1 option for the Value (Y) Series:
We need to add another Series 2 for the row["Count Unesolved Type"] aggegated data too: click on the Value (X) Series dropdown and select <New Series...> to create the Series 2:
and select the unresolved type aggregated data as input for the Optional Y Series Grouping field:
Type Progress by Issue Type as name for the Chart Title in the Format tab:
Select Series on the left panel and type Resolved and Unresolved names for the Series 1 and 2 Title respectively. Click on the Series Palette button to open the dialog and remove the current palete by click on the Remove button as many times as requires. Then chose the Green color in the first position (for the Series 1 - Resolved) and the Red color fot the other one.
Close the Palette dialog and check the Show Series Labels for each Value (Y) under the Series (for 1 and 2):
Click in the Finish button to close the dialog and run the report:
