Aggregate Project Site lists on main PWA site RRS feed

  • Question

  • We've got a Project Server 2010 deployment up and running, and I'm being asked to aggregate the Issues and Tasks lists from all of our Project Sites in one location on the PWA site, so that my lead PM can track the Issues and Tasks submitted for all projects in one place. I've been able to create a Content Query Web Part on the Issues page in PWA, and it does aggregate the issues from all of the sites, but I cannot get it to display any fields except Title and Assigned to, and I have no column headers or sorting controls, and I can't follow the prescribed procedure for editing the web part in SPD, because it's within the PWA environment.

    Can anyone give me any guidance on how to either get the CQWP to display the relevant fields and add sorting controls, OR insert the resulting dataset into the standard Issues web part? There has got to be some way to track all the risks, issues, tasks, etc within a site collection in a sortable format.

    Wednesday, June 29, 2011 3:15 PM


All replies

  • We've done that with custom webparts and with SSRS.  My guess is that SSRS
    would be easiest.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    Wednesday, June 29, 2011 5:08 PM
  • So there is no OOTB solution for a sortable aggregation, I take it? I don't have much experience with SSRS as yet. Is it going to natively allow me to select the specific list and the point in the site collection below which I want to perform the aggregation, or am I going to have to decipher the sucture of the content database, and code this manually?
    Friday, July 1, 2011 1:37 PM
  • You are correct - there's nothing OOTB per se.

    Christophe Fiessinger wrote a blog post about this a while back:

    ...and now that you remind me, iPMO has a third party product that may do what you're looking for.  Looks like Christophe played around with that on his blog as well:

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    Friday, July 1, 2011 1:56 PM
  • Thanks for the replies, Andrew. I have come across both of those articles in my searching, but I'm not particularly interested in going the program level route, and I'm certainly not interested in spending several thousand dollars for a third-party solution to a problem that should never have made it out of beta without being addressed. A lego (web part) approach really only works if all of the pieces fit together, but alas I digress. At any rate, it looks like I'm going to have to figure out how to subclass the CQWP.


    On a related note, apparently I misspoke in my original post. The CQWP will correctly aggregate and retrieve field values from the TASKS lists, but it will not retrieve values from the ISSUES list. If I set the query to show items of type ISSUE TRACKING, I get a recordset returned, with the TITLE field, and I can group the results by <SITE>. If, however, I attempt to utilize the ASSIGNEDTO field, it returns a null recordset, whether by filter, group, or sort. I can certainly understand a null result if I filtered by a column value and it returned none, but it doesn't seem like anything I do in a sort operation should result in a null recordset, there just shouldn't be any sort applied if there's no data  by which to sort.

    Any idea what the correct settings are to be able to retrieve data from the ASSIGNEDTO, ISSUESTATUS, PRIORITY, and DUE DATE columns of items of content type ISSUE TRACKING? I've tried adding them to the CommonViewFields property of the webpart, and I'm able to return data from those columns in my Tasks rollup, but I cannot for the life of me get them to show up for issues.

    Thursday, July 7, 2011 2:58 PM
  • No, I haven't played with CQWP in quite a while, so can't address that specific issue.  One potential gotcha to remember is that as I recall (and I may be totally wrong on this), SharePoint Project sites contain an Issue list - but that default Issue list is not the same as the Project Server Issue list.  Hence, you could have two lists generating Issue content types on a site.....or something like that.

    If you're seeing the Title data correctly, it's probably not that, but worth keeping in mind.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    Thursday, July 7, 2011 3:06 PM
  • Thanks Andrew,


    It is precisely the SharePoint Issues lists that I am interested in aggregating. and I do notice that it seems that all links to issues on a project, whether from the icon in Project Center or the Issues link on the ribbon in PWA when the project is open, take you to the list on the SharePoint Project Site. I also notice that if you set the List Type parameter of the CQWP to Project Issues, it automatically changes to the Issue Tracking list type, so I'm not entirely sure that there are actually two different types of Issues lists.

    Another strange behavior that I've noticed is that if I set the query for List Type "Tasks", Content Type Group "List Content Types", Content Type "Tasks", I get the intended results.
    If I set it for "Issue Tracking", "List Content Types", "Issues", I get nothing. Set the content type (last parameter) to "Items", and that's when I get results.

    So I presume from this that my Issue Tracking list type is creating lists of items (of which the only clumn is Title), rather than lists of Issues, which content type contains the columns I am attempting to query.

    So I think the $64k question is: how do I make the Issue Tacking list type contain items of type "Issue" rather than of type "Item"?

    • Marked as answer by Bart Etter Thursday, July 7, 2011 6:13 PM
    • Unmarked as answer by Bart Etter Thursday, July 7, 2011 6:13 PM
    Thursday, July 7, 2011 5:27 PM
  • Well, that's relatively easy:

    The Issue list is actually tracking the generic Item or Task content type.  I'd recommend creating a new one called "Project Issue" or something like that.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    • Marked as answer by Bart Etter Thursday, July 7, 2011 6:13 PM
    Thursday, July 7, 2011 5:36 PM
  • You, sir, are indeed THE MAN!
    And just for clarification for any future thread visitors, the "Issues" content type already exists in a standard PS deployment, but the list is set by default for content type "Project Server Issues" which is not an available option in the CQWP controls.
    Thursday, July 7, 2011 6:13 PM
  • Hey Bart - just because you got me thinking about OOTB solutions, I went
    ahead and blogged up another method using External Content Types.  Those
    posts get kicked off next Tuesday (July 19).  Thanks for the prompt.
    Just wanted to document in this thread for posterity.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    Wednesday, July 13, 2011 1:02 PM
  • Glad to inspire, Andrew, and thanks again for your assistance!


    And just for anyone else who may stumble upon this thread trying to add dynamic filtering to the CQWP, it turns out that you can do that without needing to subclass the webpart. The thrid filter value type option of the CQWP is custom value, one of the options for which is [PageQueryString:VariableName]. I just added a dropdownlist to the page with my filter choices, and passed the value to a redirect with a querystring inclusion. You should note, however, that contrary to the official line, the filter will return a null result if the querystring is not present in the URL. The solution to this bug is to include another filter in the CQWP that always returns true, AND'ed with the first filter. I used Title NEQ null.

    Friday, July 29, 2011 3:23 PM