Fork me on GitHub

Wednesday, September 30, 2009

Project Server Data Analysis View Issues

The following image shows a rough mock-up of the chart I would like to make with explanations of each field:

To explain further, I can't figure out how to show the three different bar charts, where two are broken down (and stacked) by Funding Status, on a single chart.
- I found these resources on how to accomplish this in Excel, but do not know how to translate it to Data Analysis View.
"How to create charts with multiple groups of stacked bars in Excel"

Also, I created a new MDX Expression 'Availability FTE' as: ([Measures].[Capacity] - [Measures].[Work]) / 520
- this might be able to represent the 'Under or unassigned' bar if it can be filtered to only be non-Generic
Unfortunately, as described above, I have no idea how to add it as a third bar on the chart.

The following is a screen shot of my best attempt so far:

If there were a way to plot the highlighted 'Resource Is Generic' Total (in addition to True and False) I'd be a little closer
- Still need to add availability.
- If I drag Availability in next to FTE, it gets stacked on-top of FTE, rather than placed as a new bar to the right of FTE (as I would want)...

UPDATE: I was able to accomplish this by referencing the cells from three different Excel exported pivot tables... the down-side to this is that it is not very user-friendly (e.g. if date ranges are changed in one pivot table, they must be changed in all tables, not to mention missing data for various date ranges is very difficult to deal with when referencing cells)