This project has moved. For the latest updates, please go here.

Another Kanban Board Report Error

Mar 15, 2012 at 12:12 AM

While reviewing the Kanban Board report, we identified an issue related to the sub-reports and the queries that they use to get the card data.  In each sub-report, the main data query includes a correlated sub-query to attempt to get only the most recent version of a card's data.  The sub-query looks like this:

SELECT TOP 1 wi2.WorkItemSK
FROM  DimWorkItem wi2
WHERE  wi2.System_Id = wi.System_Id
ORDER BY wi2.System_Rev DESC

However, I don't believe the System_Id value is unique across team project collections or team projects.  Thus, if additional collections and/or team projects exist, the sub-query will not return the right WorkItemSK.  We seem to have gotten the sub-reports to work properly by changing the sub-query to look like this:

SELECT TOP 1 wi2.WorkItemSK
FROM  DimWorkItem wi2
WHERE  wi2.System_Id = wi.System_Id
  AND wi2.TeamProjectCollectionSK = wi.TeamProjectCollectionSK
  AND wi2.TeamProjectSK = wi.TeamProjectSK
ORDER BY wi2.System_Rev DESC

Hopefully, someone can validate this and correct the reports for the final version.

Developer
Mar 15, 2012 at 10:58 AM

Hi Dave,

Thanks for raising this - we'll put it on our bug list.

Thanks,

adam