Bert said:
I’m using Crystal Reports to pull detail task data from the MS
Project Server 2003,
Which tables are the best “Transaction Tables†to cross reference
detail task related information between different tables?
I am trying to tackle two issues;
The first one involves a report collecting custom flag fields to
filter specific summary tasks from each project based on individual
project manager’s personal choices.
The second one involves reporting Risks and Issues to the detailed
task as well.
Will I need to perform an “extension†of the cube?
If possible I would like to try to avoid that but it is not out of
the question.
Thanks
There's no tables for cross-referencing, really... you have to do the
JOINs on the UID fields between tables yourself. You need to consult
the Project Server Database documentation located on the PS03 CD for
the database schema. I would advise against extending the cube -- and
FYI, performing this work requires some pretty heavy development
knowledge and familiarity with SQL and OLAP.
Depending on the type of information you want to pull -- and how you
want it pulled -- you will need to use one of several varieties of
JOIN, all with multiple ways of usage.
For example, if you're looking to get a list of tasks in a particular
project where a custom flag field (say Enterprise Task Flag 1) has a
particular value, you would need to do something like this:
SELECT
MT.TASK_NAME,
MT.TASK_START_DATE,
MT.TASK_FINISH_DATE,
(MT.TASK_DUR / 600) AS TaskDurInHours,
(MT.TASK_WORK / 60000) AS TaskWorkInHours
FROM
MSP_TASKS MT
INNER JOIN MSP_FLAG_FIELDS MFF
ON MFF.PROJ_ID = MT.PROJ_ID
AND MFF.FLAG_REF_UID = MT.TASK_UID
WHERE
MFF.FLAG_FIELD_ID = 188744339
AND MFF.FLAG_CATEGORY = 0
If you wanted just Summary tasks (however, this would exclude tasks
which are nonsummary but were accidentally flagged) you could append:
"AND MT.TASK_IS_SUMMARY = 1" to the query.
As you can see, this kind of work can be extremely complicated,
especially in PS03. If you're not familiar with the database, the
product, and T-SQL, it's very easy to twist your information into
knots. I would strongly recommend that you engage someone who has the
necessary expertise to do this work for you.
As for the Issues and Risks, you would need to get this directly from
WSS, which is a whole different can of worms.
--
Stephen Sanderlin
Principal Consultant
MSProjectExperts
For Project Server Consulting:
http://www.msprojectexperts.com
For Project Server FAQS:
http://www.projectserverexperts.com
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/
This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.
