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.
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.
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.
|WADMIN_DEFAULT_TRACKING_METHOD||Percent of work complete.||2|
|Actual work is done and work remaining.||3|
|Hours of work done per period.||1|
|WADMIN_IS_TRACKING_METHOD_LOCKED||Force project managers to use the progress reporting|
the method specified above for all projects.
| 1 Yes|
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
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.
|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|
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”.
The below table shows the corresponding fields in ‘pjpub.MSP_WEB_ADMIN’ table for ‘Protect User Update‘ options.
|WADMIN_PROTECT_ACTUALS||Only allow task updates via Tasks and TimeSheet||1|
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
In conclusion, we have learned how to query Task Settings in Project Server 2019 database.
- Project Server 2019 Database.
- Project Server 2016 Database.
You may also like to read
- Install and Configure Project Server 2016.
- Project Server 2013 Migration from Staging to Production Farm.
- Migrate to Project Server 2016 Step By Step.
- Project Server 2016: Query Enterprise Custom Fields.
Have a Question?
If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.