Unexpected behaviour of data grouping in Excel Services RRS feed

  • Question

  • Hi,

    I am trying to create an excel services report with data coming from default cubes. Initially I thought it would be a five minute job but somehow data the behaviour is strange. Or maybe I am missing something. Here is the scenario.

    I have a resource level lookup type custom field by name of role (Project Manager, Program Manager, Technical Consultant, Functional etc.). All I wanted to do was have a report, with shows role wise timephased baseline and actual work for a particular project. I added the resource CF to the resource cube(and later to assignment also). Now when I use the portfolio analyzer cube as data source, I simply added the CF to to Row label, Actual Work and BL Work to value, Time to column label and finally project list to filter. It shows same values of actual and BL work for all roles. If I add resource list to row label to check for grouping, it shows all resources against all roles, whereas one resource has been associated with only one role. Same thing happens if I use assignments cube. However, resource timephased cube shows correct role wise grouping, but, ofcourse it does not have other data. And I am stuck over something which I thought was a five minute job.

    Am I doing something wrong or silly or is this the expected result. If so, any advise on how to get such a report?



    Thursday, May 24, 2012 5:25 AM


  • ok, next hint, try to check in your CF settings if you have value "Calculation for Assignment Rows" setup to "roll down"


    Thursday, May 24, 2012 12:28 PM

All replies

  • Hi Atul,

    This behiavior is "expected" if you use dimension without relationship to assignments. it means that your CF eg. called ProjectRole is mapped to resource and in this scenario project server generate new dimension to OLAP cubes but mapped only for resourec timephased measures (capacity, base capacity). If you add this field also to another cubes in OLAP configuration, project server should generete next dimension called ProjectRole_R_assignments. For your report is nessesary use this dimenesion ProjectRole_R_assignments because here is designed relation for assigmnent timephased data (measures like actual work, BL work etc)

    In first check wich dimension you using in your report
    then open BI development studio and check in tab dimension usage if you have created correct mapping


    Thursday, May 24, 2012 11:18 AM
  • Thanks Tomas,

    I have already tried with the assignments dimension also. Strangely, if I use ProjectRole_R_Resource, the correct grouping comes (but I cannot get BL work etc.), but as soon as I use  ProjectRole_R_assignments, the list of Roles itself is not displayed and hence no grouping. And then if I insert BL work/work, the total is displayed in all rows, irrespective of role. That is why I though behaviour to be strange. Any thoughts?

    Thursday, May 24, 2012 12:08 PM
  • Agree with Tomás...
    Depending on your SQL skills, I've also found it sometimes easier to simply
    query the AssignmentsByDay_UserView and Resources_UserView table to get much
    the same data.  I then take the query and drop it into an ODC for use within
    Excel.  That may be another option.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky
    Thursday, May 24, 2012 12:23 PM
  • ok, next hint, try to check in your CF settings if you have value "Calculation for Assignment Rows" setup to "roll down"


    Thursday, May 24, 2012 12:28 PM
  • And it worked!! Changed the setting of custom field and bingo!!

    Thanks much Tomas



    Thursday, May 24, 2012 1:24 PM