Reporting on Custom Fields in Project Server 2016 or 2019

If you’re like me, every day you thank your lucky stars you can work in a Project Online environment where all infrastructure maintenance is handled for you, reporting packages are practically tied up in a pretty bow, and SQL table structures are concise. Occasionally, however, I encounter a client who must work in a Project Server environment, and I have to go way back to my Project Server 2010 administration days to remember where to find everything I need.

Custom fields you create in the environment are and example of some of the tricky elements you may encounter.  In Project Online, any of those custom fields will show in the Task, Project or Resource tables. That is not the case in Project Server 2013 or 2019. Instead, you need to query these tables:

pjrep.MSP_EpmTask_UserView
pjrep.MSP_EpmProject_UserView
pjrep.MSP_EpmResource_UserView

So you’re thinking, “okay, great, I’ll just pull those tables into my dataset, also, and the custom fields will be there! Great!” Okay, that’s true, and points for your sunny outlook.  But it requires two more steps to get there.

1. Filter the view by the table you’re looking for. So if you’re looking at the pjrep.MSP_EpmTask_UserView query, filter the name column by that value and that value alone:

image

Now you’ll have just one row of data in your table, so you’ll need to expand the data. This will be the Data column of your table.

image

Click on the expand arrow, and from there, you can select the fields from the Tasks table that you need in your report. As an FYI, your custom fields will be at the end of the list, which is not in alphabetical order.

image

Now, just like in the Project Online dataset, you’ll have all the custom fields you’ve created in your Project Server environment.  Follow this same routine for the pjrep.MSP_EpmProject_UserView and pjrep.MSP_EpmResource_UserView tables.

Good luck, and happy reporting!