Question:
Hi, I recently attended the MPUG webinars on Reporting (first two). I though you guys did an excellent job with this series (very impressed). Unfortunately I have been forced to listen to the recorded versions since my audio does not work for live meeting. Due to this, I was unable to submit my question. At a high level I am trying to pull custom fields from Content Type “Project Site Risk” into the Risks Report under the Business Intelligence Center --> Templates. We have been successful at pulling custom MS Project Fields into these reports however our DBA's cannot seem to locate the custom fields created in SharePoint. For example, we modified the Content Type to include Risk Cause (text), Risk event (text), Impact (text), Risk Probability (numeric), Effort Impact (numeric), ect. I am wondering if you have any advice on where to locate the custom fields added to the "Project Site Risk" Content Type. Thank you very much!
Answer
Thank you for the comments. To answer your question, new meta-data added to the Risks or Issues list does not show up in the Project Server Reporting Database (unfortunately). In order to get this data you will need to either harvest it from the content database in which your Project Site is located, or use SSRS with a SharePoint list as your data source. The former is the more popular method, although both are somewhat complex.
Method 1
Determine which content database contains your Project Sites, then select * from AllUserData (screenshot1). AllUserData is a table in the content database that contains all meta-data fields. I’ll tell you now this is sometimes like finding a needle in a haystack, because the table and rows are not logically laid out. Below is a select of some of the rows/columns. Note the headings are nText1, nText2, etc. In my experience I’d basically have to focus on a value in my Project Site. Maybe it’s a text-based field of “Stakeholders met”. I would then look at the results of the query and more specifically in the nText<x> columns until I locate the value. That would give me the tp_ListID, tp_SiteID and other key fields that help me identify how I will need to build my select statement to pull in not only the default fields in the Reporting Database, but also the custom meta-data fields I created in my Risks or Issues list (Screenshot 2). Finally, screenshot 3 shows an example of what the SQL code might look like to pull in custom meta-data. The items in the red box were custom fields created in a Risks list. In the example below a stored procedure was created and referenced in the Excel Template data source. You could have just as easily used embedded SQL.
Regarding method 2, watch out for our Webinars because we will be scheduling a session on SSRS that uses SharePoint as it’s data source.
Screenshot 1
Screenshot 2
Screenshot 3
-Dan
