As a developer I’ve found it necessary from time to time to generate reports, especially for internal consumption. Internal reports tend to be more adhoc than those presented to clients or non-technology managers, and therefore have less time devoted to their care and feeding (and cost). While I’ve used Crystal Reports and similar tools for external reporting, most development reporting has consisted of spreadsheets and any charts that they generate in the ten minutes I have before a review meeting.
Always wanting to explore various technologies, I took the opportunity to try my hand at using the BIRT (Business Intelligence and Reporting Tools) implementation in Eclipse. If you’re interested in finding out specifics about this plugin, you can find it on the Eclipse web site. While I find the tool to have a higher learning curve than I would have expected, it shows promise for the future.
There are a number of data points that we generate as part of our development, and capturing all of them in a report format proves to be a surprisingly complex task. It is a task that gets little time and investment, as development goals are not report oriented, they are product delivery oriented. I’ve attempted to capture some of the more mundane details, and set aside future report details that require a larger effort. I’ll be describing what I’ve done to create the reports, but not really discuss about why or how to interpret the data. A discussion of some other data and what you can do with it can also be found here.
Given that, and the goals for the reporting that I defined, I had to find a way for AccuWork to communicate information that BIRT could interpret. In the past using Excel, I’d simply query issues in AccuWork and export the data into an HTML table that Excel could then read in and, with the help of pivot tables, generate the desired reports. The basic information reported included:
- Number of issues per developer
- Count of issues by priority per developer
- Distribution of issue resolution (Whether issues are duplicates, regressions, cancelled, deferred, etc)
- Distribution of issue severity (Whether issues are crashes, major implementations, cosmetic, etc)
- Weekday issue completed per developer
- Weekly Find rate vs. Fix rate
- Weekly average number of days to complete issues
Using this information, it is easy to see projects taper off to closure, as well gather some ideas as to how the developer workload was being distributed. Some developers cringe at this information, but I believe as long as it is used positively and to evenly distribute work, then everyone gains from reviewing this information.
With these same charts in mind, I loaded up my version of Eclipse, installed BIRT and its associated plugins, and started to figure out how to get the data into the tool.
BIRT provides a number of different data sources, from file based to database, to XML. While I would like a little more flexibility in the XML source, like the ability to preprocess data before usage, it provided me with the means to bring in issues from AccuWork, with a little external help. The first thing I needed to do was get the information from AccuWork.
I used ‘accurev xml -l getActiveIssues.xml’. The getActiveIssues.xml file I used looks like this:
<issuelist show_active = “true”>
This tells AccuWork to return the active issues in the depot for the given stream. This will return a full description of the issues, with all the fields populated. This is where I would have liked a preprocessor. Instead I massaged the data myself, removing fields like description. It wasn’t necessary, but given the number of issues fixed it can improve performance when you generate your report.
With this information I was able to generate charts to answer the first four questions. Now I was left scratching my head on the next set, so I went back to the spreadsheet to see how I had solved this problem. I realized after importing the data into Excel, I had a number of columns that were computed values. Here BIRT shows some flexiblity, with its own computed columns.
I added the <dateClosed> field that is part of the issue description in AccuWork to the column mappings in BIRT, and created a computed column and used the following as the formula:
( row["dateClosed"] – ( row["dateClosed"]%(60*60*24*7) ) ) / (60*60*24*7)
AccuRev communicates information in seconds, so using this calculation I can compute the number of weeks since Jan 1, 1970 when a particular issue was closed. I did the same for dateSubmitted and dateCompleted. Finally, I created a computed column to determine the weekday completed using this formula:
(new String(“SunMonTueWedThuFriSat”).substr( (new Date(row["dateCompleted"]*1000)).getDay() *3, 3 ) )
This one is a little messy, so let’s break it down. Using the bolded section I compute the day of the week as a numeric value (zero based, starting with zero as Sunday). I then used this value to determine the substring as an offset in a fixed length string. Not so bad, and avoided the dreaded mid-processor (something sitting between AccuWork and BIRT) that I was afraid I would have to write.
So, using a single query from AccuWork, and computed columns in BIRT, I now have the data I need to generate the 7 charts I described earlier. Next time I’ll talk about what I had to do to get the information to chart in BIRT, and show some of the output.