Remaining Availability by Project

[Gregg Savage]

Hello,
I am trying generate a Portfolio analyzer view which displays a
running total of the remaining availability. The attached spreadsheet
shows an export of the MSPSampleDatabase data with the computed remaing
availability. As can be seen on the "Resource" sheet, the Rem Avail is
computed correctly. However, when Projects (see "Resource_Project"
sheet) are added in to the rows, the "Rem Avail" is not correct. What I
am trying to accomplish is to see the "Rem Avail" decrease each time a
project has work (see "Resource_Project_Correct" sheet).

The set of projects/resources (and order of projects/resources) is
dynamic.

Any assistance is greatly appreciated.

Again, the MSPSampleDatabase is the source for this data. Binary files
are not supported on this site so the XLS is stored as XML (Excel 2003)

 

[Gregg Savage]

I think I have finally figured out the MDX problem. The solution
here is to a slightly modified version of the problem. Instead of
computing remaining availability, I am computing a running total of Work
(which can then be subtracted from Availabilty to get Remaining
Availability). To compute the running total of Work use the following:

iif(isempty(([Resource List].currentMember,[Time].currentMember,[Project
List].prevMember,[Measures].[Accum
Work])),[Measures].[Work],[Measures].[Work]+([Resource
List].currentMember,[Time].currentMember,[Project
List].prevMember,[Measures].[Accum Work]))

Where "Accum Work" is the custom field containing the above formula
(i.e. self-referencing).

Thanks for considering the problem, but I think it is now solved.
Thought you'd like to know the solution.

 

[Gregg Savage]

Unfortunately, this formula has a unacceptable side-effect, it assigns a
value to a row that might no otherwise have a value. The better
replacement formula is:

iif(isempty([Measures].[Work]), null,
sum({[Project List].FirstSibling:
[Project List].currentMember},[Measures].[Work]))

I think I have finally figured out the MDX problem. The solution here
is to a slightly modified version of the problem. Instead of computing
remaining availability, I am computing a running total of Work (which
can then be subtracted from Availabilty to get Remaining Availability).
To compute the running total of Work use the following:

iif(isempty(([Resource List].currentMember,[Time].currentMember,[Project
List].prevMember,[Measures].[Accum
Work])),[Measures].[Work],[Measures].[Work]+([Resource
List].currentMember,[Time].currentMember,[Project
List].prevMember,[Measures].[Accum Work]))

Where "Accum Work" is the custom field containing the above formula
(i.e. self-referencing).

Thanks for considering the problem, but I think it is now solved.
Thought you'd like to know the solution.

Hello,
I am trying generate a Portfolio analyzer view which displays a
running total of the remaining availability. The attached spreadsheet
shows an export of the MSPSampleDatabase data with the computed
remaing availability. As can be seen on the "Resource" sheet, the Rem
Avail is computed correctly. However, when Projects (see
"Resource_Project" sheet) are added in to the rows, the "Rem Avail" is
not correct. What I am trying to accomplish is to see the "Rem Avail"
decrease each time a project has work (see "Resource_Project_Correct"
sheet).

The set of projects/resources (and order of projects/resources) is
dynamic.

Any assistance is greatly appreciated.

Again, the MSPSampleDatabase is the source for this data. Binary
files are not supported on this site so the XLS is stored as XML
(Excel 2003)