Retrieving Sharepoint Lists' Information In Project Sites - Project Server 2010





In a previous blog post we have discussed how to retrieve Risks and Issues custom columns information (built in project properties) from Sharepoint through SQL query. But ... what If I have another custom list that is not a project property by default, lets say "Project Monthly Review" that contains these columns "Month" as text, "Year" as text, and "Review" as multiline text. for retrieving this information in Sharepoint 2010 through SQL below is the query that will just do the work:
SELECT P.ProjectUID, A.tp_ListId, A.tp_IsCurrent, A.nvarcahr3 as [Year], A.nvarchar4 as [Month], A.nText2 as [Review]
FROM PWA_ContentDB.dbo.AllLists as L
 INNER JOIN PWA_ContentDB.dbo.AllUserData AS A ON L.tp_ID = A.tp_ListId
There the trick ... again ... the new custom Sharepoint list added to this certain site template (or project site) is not connected to the project, which this site is for.

Tracking this issue, and lots of googling ... I couldn't find a direct way to do so, but trying to find the link between Sharepoint and Project Server databases there was a link that can be used.

Adding to the query above a join for the table "AllWebs" (which contains all Web sites that are contained within the site collection) from Sharepoint content database , and the view "MSP_EpmProject_UserView" from Project Server Reporting Database.

And to connect the Sharepoint list with the Project, joining them on site URL will do the work. I have faced and error with the collation when joining but unifying the collation resolves it, to make the final query look like:
SELECT P.ProjectUID, A.tp_ListId, A.tp_IsCurrent, A.nvarcahr3 as [Year], A.nvarchar4 as [Month], A.nText2 as [Review]
FROM PWA_ContentDB.dbo.AllWebs AS W
 INNER JOIN PWA_ContentDB.dbo.AllLists as L ON W.Id = L.tp_WebId
 INNER JOIN PWA_ContentDB.dbo.AllUserData AS A ON L.tp_ID = A.tp_ListId
 INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    ON P.ProjectWorkSpaceInternalHRef COLLATE DATABASE_DEFAULT LIKE '%' + W.FullUrl COLLATE DATABASE_DEFAULT
WHERE (L.tp_Title LIKE 'Project Monthly Review')
I have added the condition to check the lists name to retrieve all that list's items, another way to do it (which may be better) is to bind it with the GUID.

A disadvantage for this is you will have to change the list name in the where condition (or the GUID) whenever a change took place on that list.

Any other suggestions to enhance this query is more than welcome :) have a nice productive day.

Comments

Post a Comment

Popular posts from this blog

Project Server 2010 : Server Event Handlers (Part 1)

Image
Server Event handlers are the expansion points for Project Server 2010, to register a custom event handlers with project server's events, Project Server raises pre-events and post-events when Project Server Interface (PSI) methods change business objects such as Project, Task, Resource, or Timesheet. When you associate an event handler with an event, Project Server runs the event handler when that particular event occurs. To start with event handlers development, you need to create new Class Library Project, from File > New > Project and select Class Library template (Figure 1) Figure 1 Delete the default class file class1.cs and right click on the project Add > New Item select class and rename it to ProjectEventHandler.cs , that will create new class file with a class called ProjectEventHandler . Add public access modifier to make this class accessible. Add these Project Server 2010 reference, right click on the project > Add Reference on the browse ta...
Read more

Project Server 2010 : Server Event Handlers (Part 2)

Image
Now we have wrote the business logic code, and registered the dll in GAC (in Part 1 ), we will register this event handler association in Project Server Application, so that when an event is raised, our function will be triggered. lets open server side event handler from Server Settings under Operational policies chose Server Side Event Handler , this will open the following page: Figure 1 Chose the event that we need to trigger our function on, in our example from the previous post it will be Project on Creating; locate the Project parent and Creating event, it will list all the registered associations (if there are any). to add new association click on New Event Handler which will open the following page: Figure 2 the Display Information section is for the name and description that appears in the list. the System Information section is to fill the dll information from GAC, as below: Assembly Name: which is the dll's name in this format ddlName, Version=1.1....
Read more