Project Server 2007: My filters don’t work in my Data Analysis views


This came out of a recent Project Server support case where a customer was trying to show the capacity of resources by department in a Data Analysis view, but found that the filter for the resources department (a resource level field) wasn’t working as expected.  If capacity wasn’t shown then the department filter was good – but as soon as capacity was added then all resources were shown regardless of the departmental filter.  I thought the cause and resolution was worth sharing.  Thanks Chris Bulson for your great work on resolving this one.

When using Data Analysis views against the SQL Server Analysis Services OLAP (cube) database you are often getting data about multiple entities each having their own grouping.  This is similar in some ways to querying a SQL Server databases – if you get the joins wrong between tables you can either get nothing or too much data! 

In the example above you are showing an assignment level field and wanting to filter by a resource level field – and there is no default way to join these.  The answer can be found in the cube configuration section of Server Settings, where you can ensure you add any task or resource custom fields you want to use to both the task/resource level and also the assignment level.  You would then need to rebuild the cube and would then be able to show capacity by department – as per the example above.

Comments (3)

  1. Dick Ruemmele says:

    Brian we created a view showing Resources and Pproject list with Availability and Work in the measures.  When we expand resource to see what projects they are assigned to we get all projects listed.  We want this list filtered for only those projects where the resource has work.  Can this technique be used to accomplish same?

  2. Dick Ruemmele says:

    We have ben trying to recreate a view we had in data analysis 03 but have been unsuccessful.  We want to simply show  for 2009 Departnment – resource – project list with measures of Availability and Work.  WHen we drill down to look at PRoject list for a given resource we can not filter projects to see only those where the eresource has work.  Instead we get all projects in the system.  Could this approach be used to filter projects by work > 0?

  3. Hi Dick,

    Sorry for the delayed response – not in the office at the moment, but I think this can be done – possibly by using one of the client side filters by right-clicking the measure you want to filter.  I’m not connected to a server so can’t check the exact syntax.

    Best regards,

    Brian.

Skip to main content