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.