Last submitted

V

Vazabe

Hi,

I would like a report stating when each resource last submitted their
timesheet. Anybody who have any ideas on this?

/V
 
J

John Sitka

I satisfy this requirement by returning a set via a stored procedure
and then building a simple asp.net grid with the results.
It dosen't give last update but it does give last connection
The base assumption is that if a resource dosen't
connect they can't update.

Here is what I use for 2003

CREATE PROCEDURE dbo.ab_GET_PWA_last_login

AS

SELECT
RES_NAME as resource,
WRES_LAST_CONNECT as lastconnected,
DATEDIFF(dd, WRES_LAST_CONNECT, GETDATE()) as daysago,
GETDATE() as runtime
FROM MSP_WEB_RESOURCES
WHERE WRES_NT_ACCOUNT IN
(
'Domain\username1',
'Domain\username2',
......)
OR
RES_NAME IN
(
'PWAuthentication1'
......
)

I actually started writing this and similar sets to an audit table
on a daily basis via an sql job. I can examine over history
who in the organization are not even visiting PWA and therefore
probably under reporting.

Both IN statements can be set up as lists or dynamic Selects
to filter the audit as needed.
 
J

John Sitka

I'm a little forgetful on this, but I think at the time I really tried to get what you want
and couldn't find it but that was in 2003. Since 2007 has another partition of task/time
records and a reporting database maybe that lastupdated field is available,
realize you still need to search across the whole set of resources or assignments
in any case. If you have SQL reporting services you could take the below select
statement and put it in there. If the audit is only for you, you could use an Excel Query..
but never (opinion) distribute Excel Querys beyond the Project Server Administrator
or DBA. Newer Excel versions will plug into a web service easy so if you could get a M$
developers attention for about 15 minutes they could publish that webservice for you (WSDL).
 
Top