Query Task Outline Code against Actual hours

[Skip Purdy]

Dear Fellow EPM Types,

I'm running into trouble trying to gen a report for a customer engagement.
Here are the particulars:

I have two Enterprise Task Outline codes that track program and accounting
codes on per task basis. These are required fields for non-summary,
non-summary task types. From every indication, my users are entering data
using these fields correctly.

Here's the part I have a grey area on - how do I build a report that shows a
summary of actual hours entered against each outline code? At first, we
considered extending the cube with Task-level data, but quickly figured out
that would fill up the cube storage at very high rate, effectively
eliminating any performance gains that would have been indicated through
Portfolio Analyzer.

So instead we turned to SQL Reporting Services. We can't find any Microsoft
documentation on which database fields to query (I think this IS my REAL
problem) to extract task-level data. We believe that we have built the
correct query to correlate against enterprise field to add the hours in the
AssignmentTimeActualWorkProtected field from the table
MSP_View_proj_assn_TP_By_Day. This was after MUCH trial and error of trying
to find where Project Server deposits task level data.

Well, the report runs (we think), but it has extraneous data in it.

I have every reason to believe we are extracting data from the wrong
field/wrong table, but I can't find any GOOD documentation on which field to
query by.

ANY pointers would be a lifesaver.

Thanks
Skip

 

[Aaron Tamblyn]

Hi Skip,

It sounds like you are on the right track. Reporting Services is a good way
to go. The set of view tables in the Project Server database that are
populated during the Project publish process. This is where we do the
majority of our reporting from. You will need to use the time phased data if
you are querying for a specific date range.

The following schema references are available for download from Microsoft,
these are the primary reference that I use. With regard to Outline codes you
will need to use the MS Project database reference to sort out how to query
outline code values, it is a bit tricky.

MS Project Server 2003 Database schema reference
================================
http://www.microsoft.com/downloads/...75-1367-4b66-870d-f280c054efe2&DisplayLang=en

MS Project Database schema reference
========================
http://www.microsoft.com/downloads/...c2-3ac1-4fc8-8e67-d908cdb9faf2&DisplayLang=en

Be careful to test your reports throughly to ensure the data queries are
accurate.

Good luck with it all.