This posting came about based on a comment on my posting http://blogs.msdn.com/brismith/archive/2009/05/13/project-server-2007-my-filters-don-t-work-in-my-data-analysis-views.aspx from Dick Ruemmele. Chris Bulson had also chased me up on this as I hadn’t had a chance to follow up. The issue is that OWC is somewhat limited in filtering – and the desire here was to have a list showing capacity and work – but only if work had a value (capacity almost always has a value). This isn’t possible with OWC against the default cubes (not sure if MDX could help here…) but if you use Excel as the client and point at your Analysis Services server and your project cubes then you have more flexibility – including Value Filters! I found the details here on the Excel blog (and do take a look at the latest postings on the Excel 2010)
With the default view we see all rows (names obliterated to protect the innocent!)– but we only want ones where Work > 0.
But by selecting Value Filters (on drop down next to Resource List) we can select Greater Than, and then set Work Greater than 0:
And we only see the rows with work!
SSRS or Excel Services might also be other options to explore to overcome some of these limitations of Office Web Components (OWC)