Report Builder: Is there a way to report on work items based on the currently logged in user?
I would like a way to create a cross project report using the Jazz Reporting Service Report Builder that bases it's results on the currently logged in user. i.e. "show everything open and assigned to me".
<I'm going to answer my own question shortly... :)>
|
One answer
|
If you're using Jazz Reporting Service 5.0.2 or 6.0, this is not currently possible. You would need to create multiple reports in the Rational Team Concert query builder to accomplish this.
We are looking to add this capability to a future release of Report Builder. You can subscribe to the story that describes this here: 370717: Ability to choose Current User in Report Builder UI
However, in our upcoming 6.0.1 release (GA scheduled for Dec) of Report Builder you can accomplish this in the Advanced SQL pane. We added support for String templates when editing the query in 6.0.1. Templates are enclosed with "$" - i.e. '$user$' and then values can be chosen at runtime through the Filters dialog.
If we consider a query to retrieve all open work items for a particular user, we'd need to have a query with a template parameter for the current user (see bold section below):
SELECT DISTINCT T1.PROJECT_NAME,
T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1,
(CASE WHEN (T1.REQUEST_STATUS='InProgress' OR T1.REQUEST_STATUS='Open') THEN 'Unresolved' ELSE (CASE WHEN T1.REQUEST_STATUS='Closed' THEN 'Resolved' ELSE T1.REQUEST_STATUS END) END) AS STATUS
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE ( (CASE WHEN (T1.REQUEST_STATUS='InProgress' OR T1.REQUEST_STATUS='Open') THEN 'Unresolved' ELSE (CASE WHEN T1.REQUEST_STATUS='Closed' THEN 'Resolved' ELSE T1.REQUEST_STATUS END) END) = 'Unresolved' AND
CONCAT('mailto:', T2.REFERENCE_ID) = '$currentUser$'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
Then when you run the query, the Filters dialog will appear. This allows you to set the value for the parameter at runtime. In the dashboard, you only need to set this once. We have a special value in 6.0.1 (utilized for RELM workflows), that allows injection of the current user value: '_relm_system_parameter_currentuser_'. This will inject the mailbox (email) of the current user into the query.
Then when the query is run it will return only results for the current user. This is a temporary solution in 6.0.1 until we have formal support in the Report Builder UI for current user ( 370717: Ability to choose Current User in Report Builder UI ).
Regards,
-Steve
|
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.