How To Query Task Settings In Project Server 2019

In this post, we’re gonna learn how to query Task Settings in Project Server 2019 database.

You may also like to read Project Server 2019: Query Multiple Values Lookup Field

Query Task Settings in Project Server Database

In rare scenarios, you may need to know the Project Server database structure and where the settings are stored.

In this post, we are trying to dig deeply into the Project Server database structure to get the Task Settings and Display that can be managed from PWA settings.

Query Task and Display Settings in Project Server 2019

Task Settings and Display In Project Server

The Task Settings and Display have the below sections:

Tracking Method.
Reporting Display.
Protect User Updates.
Define the Near Future Planning Window.

All the above settings are stored for each PWA instance in Project Server 2019 Content Database, specifically at ‘pjpub.MSP_WEB_ADMIN’ table.

Tracking Method

In PWA Settings, the Tracking Method specifies the default method for reporting progress or tasks, and whether the tracking mode should be enforced on all projects.

The below table shows the corresponding fields in ‘pjpub.MSP_WEB_ADMIN’ table for ‘Tracking Method’ options.

Field	Option	Value
WADMIN_DEFAULT_TRACKING_METHOD	Percent of work complete.	2
	Actual work is done and work remaining.	3
	Hours of work done per period.	1
	Free form.	NULL
WADMIN_IS_TRACKING_METHOD_LOCKED	Force project managers to use the progress reporting the method specified above for all projects.	1 Yes 0 No

Query ‘Tracking Method Settings’ from Project Server Database

-- Tracking Method.
SELECT
  CASE WADMIN_DEFAULT_TRACKING_METHOD
    WHEN 1 THEN 'Hours of work done per period'
    WHEN 2 THEN 'Percent Of Work Complete'
    WHEN 3 THEN 'Actual Work Done and Work remaining'
    ELSE 'Free form'
  END AS 'Tracking Method',
  CASE WADMIN_IS_TRACKING_METHOD_LOCKED
    WHEN 0 THEN 'No'
    WHEN 1 THEN 'Yes'
  END AS 'Force the PM to Use the Progress Reporting Method'
FROM pjpub.MSP_WEB_ADMIN

Reporting Display

In PWA Settings, the Reporting Display specifies how you want resources to report their hours.

The below table shows the corresponding fields in ‘pjpub.MSP_WEB_ADMIN’ table for ‘Reporting Display‘ options.

Field	Option	Value
WADMIN_TS_DEF_ENTRY_MODE_ENUM	Resources should report their hours worked every day.	0
	Resources should report their total hours worked for a week.	1
WADMIN_WEEK_STARTS_ON	Sunday	0
	Monday	1
	Tuesday	2
	Wednesday	3
	Thursday	4
	Friday	5
	Saturday	6

Query ‘Reporting Display Settings’ from Project Server Database

-- Reporting Display
SELECT
  CASE WADMIN_TS_DEF_ENTRY_MODE_ENUM
    WHEN 0 THEN 'Resources should report their hours worked every day'
    WHEN 1 THEN 'Resources should report their total hours worked for a week'
  END AS 'Reporting Display',
  CASE WADMIN_WEEK_STARTS_ON
    WHEN 0 THEN 'Sunday'
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednsday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
  END AS 'Week Start Day'
FROM pjpub.MSP_WEB_ADMIN

Protect User Updates

If your business requires to prevent the project manager to don’t change the actual time worked, you should check the “Only allow task updates via Tasks and Timesheets”.

How to Query Task Settings in Project Server 2019

The below table shows the corresponding fields in ‘pjpub.MSP_WEB_ADMIN’ table for ‘Protect User Update‘ options.

Field	Option	Value
WADMIN_PROTECT_ACTUALS	Only allow task updates via Tasks and TimeSheet	1
	Otherwise	0

Query the ‘Protect User Update’ from Project Server Database

SELECT
  CASE WADMIN_PROTECT_ACTUALS
    WHEN 1 THEN 'Yes'
    ELSE 'No'
  END AS 'Only allow task updates via Tasks and TimeSheet'
FROM pjpub.MSP_WEB_ADMIN

Define Near Future Planning Window

It specifies the number of reporting periods to include in the Near Future Planning Window on the Tasks page.

Query the ‘Near Future Planning’ value from Project Server Database

SELECT
  WADMIN_STAT_LOOK_AHEAD_PERIODS AS 'Near Future planning Windows'
FROM pjpub.MSP_WEB_ADMIN

Download the final query and check other Project Server Database Queries on Github.

Conclusion

In conclusion, we have learned how to query Task Settings in Project Server 2019 database.

Applies To

Project Server 2019 Database.
Project Server 2016 Database.

Download

Check other Project Server Database Queries on Github.

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.

How to Query Task Settings in Project Server 2019