How to Query Task Settings in Project Server 2019

Project Server 2019 Get Task Settings and Display

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

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.

Tracking Method in Project Server 2019

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

 Field Option Value
WADMIN_DEFAULT_TRACKING_METHODPercent 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_LOCKEDForce 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.

Reporting Display in Project Server 2019

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_ENUMResources should report their hours worked every day. 0
 Resources should report their total hours worked for a week. 1
 WADMIN_WEEK_STARTS_ONSunday 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.

Define Near Future Planning Window
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.

You may also like to read
Have a Question?

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

Leave a Reply