Can you do cumulative counts with Report Builder
Within Report Builder (v6.0.6) is it possible to create reports where cumulative counts are reported.
The scenario and report I would like to be able to generate is as follows:
I have a set of workitems that are distributed across multiple iterations.
Each iteration represents a particular formal review and each workitem a case to be presented at that review.
I can get a count of how many workitems are associated with each iteration. (fairly standard workitem statistics widget):
For example
Iteration 1 - 20 Workitems
Iteration 2 - 15 Workitems
Iteration 3 - 40 Workitems
Iteration 4 - 25 Workitems
What I would like to be able to do is produce a report that shows a cumulative count of workitems.
For example
Iteration 1 - 20 Workitems
Iteration 1+2 - 35 Workitems
Iteration 1+2+3 - 75 Workitems
Iteration 1+2+3+4 - 100 Workitems
And then effectively plot these numbers against time such that you show the cumulative build-up of workitems as you move in time through each iteration.
Each iteration ends at the same point as the next one starts so are sequential.
|
One answer
Hi Ben,
You can do this but not without dropping into Advanced SQL and using your database vendor SQL syntax to do it. For example:
SELECT DISTINCT T2.REFERENCE_ID AS URL1_title,
T2.URL as URL1, T5.ITERATION_NAME, SUM(CASE WHEN (T5.REQUEST_STATUS='InProgress' OR T5.REQUEST_STATUS='Open') THEN T5.STORY_POINTS ELSE 0 END) OVER (PARTITION BY T5.ITERATION_NAME) AS PLANNED_PTS, SUM(CASE WHEN T5.REQUEST_STATUS='Closed' THEN T5.STORY_POINTS ELSE 0 END)OVER (PARTITION BY T5.ITERATION_NAME) AS ACTUAL_PTS, SUM(T5.STORY_POINTS) OVER (ORDER BY T5.ITERATION_NAME) AS CUMULATIVE_PTS,
Amy
Comments Our database is hosted on Oracle.
Is this syntax you enter in Report Builder itself or would I have to have access to the base database to run these queries?
Hi Ben,
You can define this in Report Builder. However, once you make the decision to edit the SQL, you forever have made that decision and cannot go back to the "Choose Data" part in Report Builder. You can still do the "Format Results" part.
I pulled that SQL from a report I've actually delivered as part of the scaled agile/SAFe reports. I have not heard anyone having issues importing it with the syntax I showed, indicating to me that the syntax may not be DB2-specific (although I developed it on DB2).
The report is the Solution Epic Burn-Up Chart in the SAFe 4.5 Reports - 6.0.5 archive here: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W54ecb028c53d_48b0_9d5e_4584a00489d3/page/SAFe%20Reporting.
Amy
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.