2007 Reporting frustrations

[James Fraser]

I'm looking into moving some reports from Project Server 2003 to 2007,
and I haven't noticed a way to determine task predecessor or
successors from the reporting database.

Has anyone done anything like this or have any ideas? Like much
information, it appears that it is no longer available in the
published DB structure. I can't believe this information isn't in the
reporting DB somewhere and I think I must be missing something.

If it's not in the DB, just as an exercise for the reader, what
approach would you take to get this info? Build a custom reporting
extension accessing the PSI and iterating through projects and/or
tasks to build a custom table? Maybe write an event to catch any
project publish and put the task successors into a custom table at
that time? Or the cheap way out, peek into the published or Draft db's
and figure out the schema for yourself.

Ugh. Don't get me started on task update reporting...


James Fraser

 

[Jonathan Sofer]

You are right that there seems to be no mention of predecessor or successors
in the reporting database, very strange. I did, however, find it in the
published database in a similar fashion to how it was set up in 2003, a
table called dbo.MSP_LINKS using fields LINK_PRED_UID and LINK_SUCC_UID and
so I would suggest possibly using a join to the published db from the
reporting db to get this particular information. I have to join to the
published db to get the available custom field values and their ordering as
well since this does not seem to be available in the reporting db either.

Have you used the schema for the reporting db as well as the sdk yet? Some
of it is very useful to try to get to the bottom of things quickly in 2007.
It does not provide the schema for the other DBs but its a good start.
http://www.microsoft.com/downloads/...f9-7028-4b30-99a2-18cb1eed1abe&displaylang=en

Hope this helps,

Jonathan Sofer

 

[James Fraser]

. . . I have to join to the
published db to get the available custom field values and their ordering as
well since this does not seem to be available in the reporting db either.

Have you used the schema for the reporting db as well as the sdk yet? Some
of it is very useful to try to get to the bottom of things quickly in 2007.
It does not provide the schema for the other DBs but its a good start.http://www.microsoft.com/downloads/details.aspx?familyid=2672f6f9-702...

Thanks... I've read through those documents. Not as nice as the 2003
schema doc with examples, but helpful. One reason that MS does not
publish the schema of the other DBs is that they are considered
internal, and liable to change with service packs or releases. Also,
MS discourages use of those databases, since locks on the data will
slow Project Server down. (Even read-only "Select"s lock some of the
data.)

[cue grumpy old man voice]
2007 has been significantly harder to write reports for than 2003 for
anything other than simple project information. Within the published
DB, various important information is captured in binaries that make
reading it near impossible. And the alternate programming interfaces
don't fill the gap. The best idea I've come up with so far is to write
event handlers to copy lots of information to my own tables on certain
events. I just keep hoping that I'm missing something.
[grumpy old man fades away]

Oh well,


Thanks for the pointers...
James Fraser

... Like much

[I should clarify here: I should have said the "Released and publicly
available" DB schema instead of "published DB structure."]