Creating a BIRT report based on a query with computed column
Is it possible to create a BIRT report based on an query like one of the demo videos show, then add a computed column to the report output based on what the query returns?
I'm trying to create a report that prioritizes our Developer's work based on values in several other columns. For example, Customer problems with a medium Severity have a higher priority than an internal defect that is a non-regression and lower Severity.
The computed column would be called something like Rank and will order the results returned by the query (all non-resolved work items assigned to current user). Worst case I don't need the computed column, just the query output ordered based on specific criteria.
Am I going down the right path here with the existing RTC queries or would there be a better way to creat this report?
Thanks,
I'm trying to create a report that prioritizes our Developer's work based on values in several other columns. For example, Customer problems with a medium Severity have a higher priority than an internal defect that is a non-regression and lower Severity.
The computed column would be called something like Rank and will order the results returned by the query (all non-resolved work items assigned to current user). Worst case I don't need the computed column, just the query output ordered based on specific criteria.
Am I going down the right path here with the existing RTC queries or would there be a better way to creat this report?
Thanks,
6 answers
On 1/14/2010 3:38 AM, miwalker wrote:
So I think you're on the right track with the idea of a computed column.
If you open your data set, you can add a computed column, you can write
any javascript you like to compute the value of that column, and this
expression can have access to the columns for that specific row. In
addition, the "aggregation" pull-down in the computed columnn dialog
gives you access to many BIRT built-in aggregation functions that would
let you do things like rank, running percent, running sum, min, max,
median/mode/average, and a bunch of others. I'd take a look at these and
see if any of them gives you the computation you want. Then in your
table or graph, you can use this computed column just like any other
column of the data set, you can sort on it, etc.
Hope this helps.
james
RTC Reports Team Lead
Is it possible to create a BIRT report based on an query like one of
the demo videos show, then add a computed column to the report output
based on what the query returns?
I'm trying to create a report that prioritizes our Developer's work
based on values in several other columns. For example, Customer
problems with a medium Severity have a higher priority than an
internal defect that is a non-regression and lower Severity.
The computed column would be called something like Rank and will order
the results returned by the query (all non-resolved work items
assigned to current user). Worst case I don't need the computed
column, just the query output ordered based on specific criteria.
Am I going down the right path here with the existing RTC queries or
would there be a better way to creat this report?
Thanks,
So I think you're on the right track with the idea of a computed column.
If you open your data set, you can add a computed column, you can write
any javascript you like to compute the value of that column, and this
expression can have access to the columns for that specific row. In
addition, the "aggregation" pull-down in the computed columnn dialog
gives you access to many BIRT built-in aggregation functions that would
let you do things like rank, running percent, running sum, min, max,
median/mode/average, and a bunch of others. I'd take a look at these and
see if any of them gives you the computation you want. Then in your
table or graph, you can use this computed column just like any other
column of the data set, you can sort on it, etc.
Hope this helps.
james
RTC Reports Team Lead
On 1/14/2010 3:38 AM, miwalker wrote:
Is it possible to create a BIRT report based on an query like one of
the demo videos show, then add a computed column to the report output
based on what the query returns?
I'm trying to create a report that prioritizes our Developer's work
based on values in several other columns. For example, Customer
problems with a medium Severity have a higher priority than an
internal defect that is a non-regression and lower Severity.
The computed column would be called something like Rank and will order
the results returned by the query (all non-resolved work items
assigned to current user). Worst case I don't need the computed
column, just the query output ordered based on specific criteria.
Am I going down the right path here with the existing RTC queries or
would there be a better way to creat this report?
Thanks,
So I think you're on the right track with the idea of a computed column.
If you open your data set, you can add a computed column, you can write
any javascript you like to compute the value of that column, and this
expression can have access to the columns for that specific row. In
addition, the "aggregation" pull-down in the computed columnn dialog
gives you access to many BIRT built-in aggregation functions that would
let you do things like rank, running percent, running sum, min, max,
median/mode/average, and a bunch of others. I'd take a look at these and
see if any of them gives you the computation you want. Then in your
table or graph, you can use this computed column just like any other
column of the data set, you can sort on it, etc.
Hope this helps.
james
RTC Reports Team Lead
Thanks James,
I took a different path and decided to create the report without the query. Right now my report lists the Summary, State, Type, Priority, and Severity of the Work Items by Contributor. I have the user pass in the Contributor name as a parameter and the Report lists all the Work Items under that name.
The Priority and Severity fields are only showing the Literal values, so I started following Rafik's demo for Plan Items by Theme where he handles this same case with Themes.
I tried finding the ID for Priority in the Config Source and found
Am I handling these 2 fields correctly or is there an easier way to get the Priority/Severity field values? And what should the ID be for Priority?
Thanks,
Michael
Can you please try priority and severity instead of com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity?
com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity are the ids of the work item attributes.
priority and severity are the ids of the enumeration attribute types.
BTW, the process specification has all the information you need.
com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity are the ids of the work item attributes.
priority and severity are the ids of the enumeration attribute types.
BTW, the process specification has all the information you need.
Can you please try priority and severity instead of com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity?
com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity are the ids of the work item attributes.
priority and severity are the ids of the enumeration attribute types.
BTW, the process specification has all the information you need.
Thanks Rafik,
Following along with your Demo, but using Priority instead of Themes, I first created the Plan Items by Priority dataset and specified
I continued on and when I create the Priority dataset and drag it to the Layout I get the output below which looks good.
But when I add the PRIORITY computed column to Plan Items by Priority and drag to the Layout, nothing is returned...I'm guessing because of my original problem where preview of the Plan Items by Priority dataset returns nothing.
Should I be using 'priority' as the ID?
Separate question: I have an Owner parameter tied to the Contributor table so the Report output will return all work items associated with the Contributor. Is it possible to specify Current User instead? I tried {Current_User} but that didn't work. Thanks,
LITERAL_ID LITERAL_NAME
priority.literal.l01 Unassigned
priority.literal.l02 Low
priority.literal.l07 Medium
priority.literal.l11 High
Jan 26, 2010 2:47 PM
Can you please try priority and severity instead of com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity?
com.ibm.team.workitem.attribute.priority and com.ibm.team.workitem.attribute.severity are the ids of the work item attributes.
priority and severity are the ids of the enumeration attribute types.
BTW, the process specification has all the information you need.
Thanks Rafik,
Following along with your Demo, but using Priority instead of Themes, I first created the Plan Items by Priority dataset and specified
I continued on and when I create the Priority dataset and drag it to the Layout I get the output below which looks good.
But when I add the PRIORITY computed column to Plan Items by Priority and drag to the Layout, nothing is returned...I'm guessing because of my original problem where preview of the Plan Items by Priority dataset returns nothing.
Should I be using 'priority' as the ID?
Separate question: I have an Owner parameter tied to the Contributor table so the Report output will return all work items associated with the Contributor. Is it possible to specify Current User instead? I tried {Current_User} but that didn't work. Thanks,
LITERAL_ID LITERAL_NAME
priority.literal.l01 Unassigned
priority.literal.l02 Low
priority.literal.l07 Medium
priority.literal.l11 High
Jan 26, 2010 2:47 PM
It seems like for these non-custom columns like Priority and Severity I should be using the WORKITEM_CHNGS_TO table and the WI_PRIORITY column. That seems to give me the non-literal value.