Filtering Non-Summary Tasks from MSP Portfolio Analyzer Cube and Timephased Assignment Cubes Not Working Properly in Project Server 2013 RRS feed

  • Question

  • I am trying to build an SSRS report using the MSP Portfolio Analyzer Cube and/or Assignment Timephased Cubes from Project Server 2013 and am finding that, although there is a dimension "Task Is Summary" in both cubes, when I set a Filter to show only Tasks where "Task Is Summary" = {false}, Summary Tasks are still returned in the resulting set, causing duplicate accounting for any particular Measure.  I've experimented with MDX expressions which would only return "Leaf" items in the Task Hierarchy thinking it would be another way to isolate the Lowest Level or Non-Summary Tasks/Assignments, but to no avail.  Screen shot below shows how I have set the filter, and the resulting dataset includes Summary Tasks (you'll have to believe me!). Ultimately I'm trying to produce a report that shows planned work by day by resource by time period optionally showing down to task level when resource is over-allocated.  I know I can get to the data from the Project Web App database, but not as efficiently as I could through a cube (i.e. performance using relational database is not acceptable.)

    Has anyone experienced this, or does anyone have thoughts on how I might accomplish this with the cubes?



    Wednesday, April 23, 2014 3:20 PM

All replies

  • Perhaps someone could simply verify that when they connect to the Project Server 2013 Portfolio Analyzer and/or the Assignment Timephased cubes, and set a filter for "task is Summary" = {false}, that they, too are seeing Summary Tasks.  If that is the case, we'll probably open a ticket with Microsoft. Thanks. Justin

    Justin Naughton

    Wednesday, April 23, 2014 6:55 PM
  • It would be nice if the "Task Is Summary Field" worked to isolate non-Summary tasks, however, it looks as though the way the cube is built, the Task Attribute "Task Is Summary" doesn't roll down to a resource assignment and therefore can't be used as a filter in an OLAP query (at least the way the Project Server Cubes are built).

    I was able to solve this need with an MDX Query.  Taking the Query I set up in the screen shot above, I removed the second to last line in the Dimension Filter Area "Task List" in which I attempted to place the MDX Query.

    Instead, clicking on the Upper Right Icon, you leave the design mod and enter the text editor, you can insert the MDX Query in another location. 

    The highlighted text above is the MDX Expression I inserted into the Query which worked to remove on summary tasks keeping the aggregations on planned work accurate.  Keep in mind two things:

    1.  Once you edit the query in the text editor vs the design mode, you can't switch back, so make sure you have your query as close to final before switching, or you'll need to make any follow-on changes in the text editor (or you can roll back to an earlier version of the report/query.)

    2.  I am referencing a Resource Custom Field "MerWeeklyTimesheetRequired" which we are using to identify people we've determine need to submit timesheets.  

    Here is the text of the query in case anyone wants to use it.

     SELECT NON EMPTY { [Measures].[Work] } ON COLUMNS, NON EMPTY { ([Time].[Time].[Day].ALLMEMBERS * [Resource List].[Resource List].[Resource List].ALLMEMBERS * DESCENDANTS([Project List].[Project List].[Level 02].ALLMEMBERS) * DESCENDANTS([Task List].[Task List].[Level 02],,Leaves) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM ( SELECT ( STRTOSET(@MerWeeklyTimesheetRequiredResourceMerWeeklyTimesheetRequiredResource, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TaskIsOverallocatedTaskIsOverallocated, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TaskIsActiveTaskIsActive, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ResourceStatusResourceStatus, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ResourceIsGenericResourceIsGeneric, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ResourceIsActiveResourceIsActive, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ProjectListProjectList, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ResourceListResourceList, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TimeTime, CONSTRAINED) ) ON COLUMNS FROM [Assignment Timephased]))))))))) WHERE ( IIF( STRTOSET(@ResourceIsActiveResourceIsActive, CONSTRAINED).Count = 1, STRTOSET(@ResourceIsActiveResourceIsActive, CONSTRAINED), [Resource Is Active].[Resource Is Active].currentmember ), IIF( STRTOSET(@ResourceIsGenericResourceIsGeneric, CONSTRAINED).Count = 1, STRTOSET(@ResourceIsGenericResourceIsGeneric, CONSTRAINED), [Resource Is Generic].[Resource Is Generic].currentmember ), IIF( STRTOSET(@ResourceStatusResourceStatus, CONSTRAINED).Count = 1, STRTOSET(@ResourceStatusResourceStatus, CONSTRAINED), [Resource Status].[Resource Status].currentmember ), IIF( STRTOSET(@TaskIsActiveTaskIsActive, CONSTRAINED).Count = 1, STRTOSET(@TaskIsActiveTaskIsActive, CONSTRAINED), [Task Is Active].[Task Is Active].currentmember ), IIF( STRTOSET(@TaskIsOverallocatedTaskIsOverallocated, CONSTRAINED).Count = 1, STRTOSET(@TaskIsOverallocatedTaskIsOverallocated, CONSTRAINED), [Task Is Overallocated].[Task Is Overallocated].currentmember ), IIF( STRTOSET(@MerWeeklyTimesheetRequiredResourceMerWeeklyTimesheetRequiredResource, CONSTRAINED).Count = 1, STRTOSET(@MerWeeklyTimesheetRequiredResourceMerWeeklyTimesheetRequiredResource, CONSTRAINED), [Mer Weekly Timesheet Required_Resource].[Mer Weekly Timesheet Required_Resource].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Justin Naughton

    Wednesday, April 23, 2014 11:08 PM
  • Looks like the screen shot may be hard to see. 

    I changed DESCENDANTS([Task List].[Task List].[Level 02].ALLMEMBERS) to DESCENDANTS([Task List].[Task List].[Level 02],,Leaves).  Hope you find this helpful!

    Justin Naughton

    Wednesday, April 23, 2014 11:10 PM