Share via

T-SQL: Query Task and Display Settings In Project Server 2016

  • Article

Introduction

In this article, we will show How to retrieve the Project Server 2016 Task and Display Settings from Project Server 2016 Content Database via T-SQL.

https://gallery.technet.microsoft.com/site/view/file/181549/1/PS%20Datababase.jpg

Note

In Project Server 2016, there is no Project database, only single database (SharePoint Content Database) holds the project data and the content to facilitate the database operations, such as backup and restore, migration, etc. For more Details check 

Task settings and Display 

In Project Server 2016, you can manage the Task settings and Display from the PWA Settings below Time and Task Management.

 https://gallery.technet.microsoft.com/site/view/file/181563/1/Task%20settings%20and%20Display.png

The Task settings and Display has the below section

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

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

In the next section, we will how to retrieve these settings in details via T-SQL.

Tracking Method

It specifies the default method for reporting progress or tasks, and whether the tracking mode should be enforced on all projects.

https://gallery.technet.microsoft.com/site/view/file/181565/1/Tracking%20Method.png

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 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
method specified above for all projects.		  1 Yes
 0 No

Get 'Tracking Method' via T-SQL

-- 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

It specifies how you want resources to report their hours.
https://gallery.technet.microsoft.com/site/view/file/181568/1/Reporting%20Display.png
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

**
Get 'Reporting Display' via T-SQL**

-- 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

Select the Only allow task updates via Tasks and Timesheets checkbox if your business requires that the project manager not be able to change actual time worked.

https://gallery.technet.microsoft.com/site/view/file/181569/1/Protect%20User%20Updates.png

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

Get 'Protect User Update' via T-SQL

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.
https://gallery.technet.microsoft.com/site/view/file/181570/1/Define%20Near%20Future%20Planning%20Window.png

The below table shows the corresponding field in 'pjpub.MSP_WEB_ADMIN' table for 'Define Near Future Planning Window' value

 Field  Option
 WADMIN_STAT_LOOK_AHEAD_PERIODS  Near Future planning Windows

Get 'Define Near Future Planning Window' value

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

Download

Download the full query from Project Server 2016 - Query Task and Display Settings In PWA Settings in TechNet Gallery.

Conclusion

In this article, we have explained How to retrieve the PWA settings like Task and Display from Project Server 2016 Database?

Reference

See Also

Back To Top