locked
Work Item Description in Related Work Items report RRS feed

  • Question

  •  

    I'd like to be able to see the descriptions of the related work items that I see in this report by "drilling into" the top level.

     

    I thought it would be as easy as expanding the WorkItem dimension and adding description to the result set but I don't see it there. 

     

    What's the best way to get this field in the report dataset?

     

    Thanks!

    Tuesday, May 27, 2008 8:06 PM

Answers

  • Just in case anyone else ever searches on the same thing:

     

    What i did finally was create a new datasource to TfsWorkItemTracking and created a report against that database.  I queried the WorkItemLongText table (that's close if it's not the right name; don't have the db with me right now).  The query returned the Words field where FldID = 52 (that's System.Description) and ID = @workitem.

     

    The layout was a single cell table with the Words field. 

     

    Now I use that as a subreport on any report I'm returning ID and also want the Description.  Set the parameter of the subreport to the Fields!System_ID field of the master dataset. 

     

    It's very possible I went around the world so that i could get around the block.  If there's a more direct way to pull description into the OlapDatabaseDS report, someone please set the record straight here.

    Friday, May 30, 2008 4:31 AM

All replies

  • Just in case anyone else ever searches on the same thing:

     

    What i did finally was create a new datasource to TfsWorkItemTracking and created a report against that database.  I queried the WorkItemLongText table (that's close if it's not the right name; don't have the db with me right now).  The query returned the Words field where FldID = 52 (that's System.Description) and ID = @workitem.

     

    The layout was a single cell table with the Words field. 

     

    Now I use that as a subreport on any report I'm returning ID and also want the Description.  Set the parameter of the subreport to the Fields!System_ID field of the master dataset. 

     

    It's very possible I went around the world so that i could get around the block.  If there's a more direct way to pull description into the OlapDatabaseDS report, someone please set the record straight here.

    Friday, May 30, 2008 4:31 AM
  • Can I get the modified and the new report. Because, I am exactly looking for the same and don't want to reinvent the wheel.

    Thanks,
    Siva

    Wednesday, November 12, 2008 6:44 PM
  • The problem with this workaround is that 1) it will duplicate the descriptions if there is more than one revision for it. 2) the subreport is not actually joined to your original table, so if anything is changed within either database table, your reports probably will no longer work (aka they won't match)

    I had this exact same issue and ended up having to create a SQL query. I created a new dataset in my report linked to TfsReportDS (not OlapReport) and then entered in the following query for the dataset query:

    select person.*, wi.*, wih.*, area.area as AreaName, wilt.*, cwi.*
    from person, [work item] wi, [work item history] wih, area, TfsWorkItemTracking.dbo.WorkItemLongTexts wilt, [Current Work Item] cwi
    where wih.[work item] = wi.__id and wih.area = area.__id and wilt.id = wi.system_id and cwi.[work item] = wi.__id and cwi.[assigned to] = person.__id and
       (select max(rev) from TfsWorkItemTracking.dbo.WorkItemLongTexts wilt2 where wilt2.id = wi.system_id)
    order by person, AreaName, system_workitemtype, system_state

    Clearly, this is going to pull a BUNCH of information into the same table. If you make sure you select the correct columns and pull them into your layout in your report, you should be just fine. You can also SELECT only the exact columns you want by putting in the name of the column field in the SELECT statement where the *s are. If you aren't familiar with SQL queries, you should take a look at http://www.w3schools.com/sql/default.asp so you can edit the query. Hopefully this helps... good luck!
    Tuesday, July 7, 2009 7:11 PM