A
Adam Bennett
Hello All
We are using PS2003 SP2a with managed time periods and have developed some
reports within SRS against the Project Server database to show us total time
by user by project. In order to reflect this accurately, we've broken it
down into saved, submitted, accepted and rejected columns based on the
various combinations of the WWORK_UPDATE_STATUS, WWORK_APPROVAL_STATUS, and
A.WASSN_Actuals_Pending fields.
Here is the query that we're using:
===================================================================
CASE
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 1 THEN 1 --Submitted
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 0 THEN 2 --Saved
WHEN WWORK_UPDATE_STATUS = 0 AND WWORK_APPROVAL_STATUS = 1 THEN 3 --Accepted
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 2 THEN 4--Rejected
END AS Status,
===================================================================
We've used this for awhile now with fairly good success, however we noticed
recently when trying to pull multiple months of time for a specific project,
not all submitted/accepted time was displaying in the report. After some
investigation, we discovered that a lot of the time entries had the
WWORK_APPROVAL_STATUS flag set to zero and was getting filtered out of the
report based on our query.
Our first assumption was that the query was just written poorly and needed
to account for this but when looking at the documentation, we couldn't find
anything indicating WWORK_APPROVAL_STATUS should ever be set to zero. Based
on the documentation and other posts, it seems to always be set to 1.
Does anyone happen to know if and/or how this flag can be set to zero? Just
wondering if we need to change our query or if we have a larger problem on
our hands (i.e. corrupted db).
Thanks much for any help!
We are using PS2003 SP2a with managed time periods and have developed some
reports within SRS against the Project Server database to show us total time
by user by project. In order to reflect this accurately, we've broken it
down into saved, submitted, accepted and rejected columns based on the
various combinations of the WWORK_UPDATE_STATUS, WWORK_APPROVAL_STATUS, and
A.WASSN_Actuals_Pending fields.
Here is the query that we're using:
===================================================================
CASE
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 1 THEN 1 --Submitted
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 0 THEN 2 --Saved
WHEN WWORK_UPDATE_STATUS = 0 AND WWORK_APPROVAL_STATUS = 1 THEN 3 --Accepted
WHEN WWORK_UPDATE_STATUS = 1 AND WWORK_APPROVAL_STATUS = 1 AND
A.WASSN_Actuals_Pending = 2 THEN 4--Rejected
END AS Status,
===================================================================
We've used this for awhile now with fairly good success, however we noticed
recently when trying to pull multiple months of time for a specific project,
not all submitted/accepted time was displaying in the report. After some
investigation, we discovered that a lot of the time entries had the
WWORK_APPROVAL_STATUS flag set to zero and was getting filtered out of the
report based on our query.
Our first assumption was that the query was just written poorly and needed
to account for this but when looking at the documentation, we couldn't find
anything indicating WWORK_APPROVAL_STATUS should ever be set to zero. Based
on the documentation and other posts, it seems to always be set to 1.
Does anyone happen to know if and/or how this flag can be set to zero? Just
wondering if we need to change our query or if we have a larger problem on
our hands (i.e. corrupted db).
Thanks much for any help!