Applied task update requests history

[Huckey]

Hi all,
StatusApprovalsHistory.aspx in PWA shows all task update requests
and errors applied to project assignments. For some reason though it
is not possible to export the data displayed there to Excel which I am
interested in.

How can I export the data about task updates to Excel? If I can not
do it form PWA, how can I get the equivalent dataset from MS SQL
Server? I have found table MSP.ASSIGNMENTS_TRANSACTIONS but there the
data about dates and % complete is stored as binary data so I could
not see the details.

I am using MS Project Server 2007 on MS SQL Server 2005.

Thanks!

Best regards

Huckey

 

[Sander]

Hi Huckey,

If you need the hours and % complete, you will be disappointed. There is no
way to get the history of these figures. To have a report that has all the
updates displayed will be a lot of digging in the table to have the correct
selection of transactions.
This report will never be supported as you will be querying on the published
database, which can change when updates/hotfixes arrive.

What is the purpose of having this report for you?

Sander

 

[Huckey]

What is the purpose of having this report for you?

Sander

Hi,
the purpose of this report is that I would like to see how the
project has historically developed - say how many tasks were finished
on each day to have some kind of a chart showing dates on the X axis
and the number of finished tasks on Y axis.
Now the problem that I have is that since the project I work with
uses Percent of work complete as the tracking method, the actual
finish date is always set to the plan finish date since the resources
update only the % Complete. In that case the date of update is more or
less the correct finish date and that is why I would need that kind of
report.

You are saying that such a report will never be supported but on the
other hand PWA somehow displays this data when I click on Approvals
and then Go To -> Applied Requests and Errors. It would suffice me if
I could export the grid visible there to Excel but that doesn't work
unfortunately :-(

Best regards

Huckey

 

[Sander]

just a tought:

in the reporting database is the fiels "pct work complete" available on
assignemt_userview. If you create a scheduled job that extracts weekly the %
complete for assignments, you wil be able to see the history.
But this will not help you getting the history of the progress until now,
you will only get from the day you have this in place.

I could not think of other options in this case.

regards,
Sander

 

[Huckey]

 How can I export the data about task updates to Excel? If I can not

do it form PWA, how can I get the equivalent dataset from MS SQL
Server? I have found table MSP.ASSIGNMENTS_TRANSACTIONS but there the
data about dates and % complete is stored as binary data so I could
not see the details.

Hi again,
I have found a solution which is good enough for me so I want to
share it in case someone else has a similar problem. Hope it can help
somebody.

The SQL below displays the total work of assignments reported as
finished on each day. It can easily be changed to show the total
number of assignments reported as finished on each day. In both cases
"reported as finished on each day" refers to the date of sending the
respective assignment update with % Complete set to 100%.

SELECT ASSN_UPDATE_DATE, SUM(ASSN_WORK) AS WORK_DONE FROM (
SELECT A.ASSN_UID, A.ASSN_WORK/60000 AS ASSN_WORK,
T.ASSN_TRANS_UPDATE_DATE, CAST(FLOOR(CAST(T.ASSN_TRANS_UPDATE_DATE AS
DECIMAL(12, 5))) AS DATETIME) AS ASSN_UPDATE_DATE
FROM DBO.MSP_ASSIGNMENTS AS A, DBO.MSP_ASSIGNMENT_TRANSACTIONS AS T
WHERE A.ASSN_UID=T.ASSN_UID
AND T.ASSN_TRANS_UPDATE_DATE=(SELECT MAX(ASSN_TRANS_UPDATE_DATE) FROM
DBO.MSP_ASSIGNMENT_TRANSACTIONS AS TA WHERE TA.ASSN_UID=T.ASSN_UID)
AND A.ASSN_PCT_WORK_COMPLETE=100
) AS ORACLE_WOULD_NOT_NEED_AN_ALIAS_HERE
GROUP BY ASSN_UPDATE_DATE
ORDER BY ASSN_UPDATE_DATE

Best regards

Huckey