none
Project Server 2010 Customize Sample Report RRS feed

  • Question

  •  There is a Sample Report named IssuesAndRisks which fetches fields from Project Server Reporting Databases. We want to add a few fields from Content Database UserData table to this report both for Issues and Risks reports.

    How best to join this report with the UserData table in the Content database so that no record from original report is lost?

    For reference the original built-in query for these reports is :

    For Issues Report the query is :

    -----------------------------------

                 SELECT                 ProjectOwner.ProjectUID as [ProjectUID],            

       ProjectOwner.ProjectName as [ProjectName],                ProjectOwner.ProjectStartDate as [ProjectStartDate],                ProjectOwner.ProjectFinishDate as [ProjectFinishDate],                MSP_WssRelationshipType.RelationshipTypeID as [RelationshipTypeID],                MSP_WssRelationshipType.Description as [RelationshipDescription],                MSP_WssIssueToTaskLinks_UserView.IssueID as [IssueID],                MSP_WssIssueToTaskLinks_UserView.Title as [Title],                MSP_WssIssueToTaskLinks_UserView.AssignedToResource as [AssignedToResource],                MSP_WssIssueToTaskLinks_UserView.NumberOfAttachments as [NumberOfAttachments],                MSP_WssIssueToTaskLinks_UserView.DueDate as [DueDate],                MSP_WssIssueToTaskLinks_UserView.Category as [Category],                MSP_WssIssueToTaskLinks_UserView.Status as [Status],                MSP_WssIssueToTaskLinks_UserView.Priority as [Priority],                MSP_WssIssueToTaskLinks_UserView.Owner as [Owner],                MSP_WssIssueToTaskLinks_UserView.Discussion as [Discussion],                MSP_WssIssueToTaskLinks_UserView.Resolution as [Resolution],                MSP_WssIssueToTaskLinks_UserView.CreateByResource as [CreateByResource],                MSP_WssIssueToTaskLinks_UserView.CreatedDate as [CreatedDate],                MSP_WssIssueToTaskLinks_UserView.ModifiedByResource as [ModifiedByResource],                MSP_WssIssueToTaskLinks_UserView.ModifiedDate as [ModifiedDate],                TaskRelated.TaskUID as [TaskUID],                TaskRelated.TaskName as [TaskName],                TaskRelated.TaskStartDate as [TaskStartDate],                TaskRelated.TaskFinishDate as [TaskFinishDate]          

      FROM                dbo.MSP_WssIssueToTaskLinks_UserView                 LEFT OUTER JOIN dbo.MSP_EpmProject_UserView AS ProjectOwner ON                   

    ProjectOwner.ProjectUID = MSP_WssIssueToTaskLinks_UserView.ProjectUID                LEFT OUTER JOIN dbo.MSP_EpmProject_UserView AS ProjectRelated

    ON                    ProjectRelated.ProjectUID = MSP_WssIssueToTaskLinks_UserView.RelatedProjectUID                LEFT OUTER JOIN dbo.MSP_EpmTask_UserView AS TaskRelated ON                   TaskRelated.ProjectUID = MSP_WssIssueToTaskLinks_UserView.RelatedProjectUID                   AND TaskRelated.TaskUID = MSP_WssIssueToTaskLinks_UserView.RelatedTaskUID                LEFT JOIN dbo.MSP_WssRelationshipType ON                   MSP_WssIssueToTaskLinks_UserView.RelationshipTypeID = MSP_WssRelationshipType.RelationshipTypeID          

      ORDER BY                ProjectOwner.ProjectName ASC,                MSP_WssIssueToTaskLinks_UserView.Title ASC  

    ----------------------------------------------------------  

    We need to add fields from UserData table to this query.

    Similarly we want to add fields from UserData table to Risk report also. The original query for Risks is similar to the above query with MSP_WssIssueToTaskLinks_UserView being replaced by MSP_WssRiskToTaskLinks_UserView.

    How best to do it without losing records from original report?

     

    Friday, January 31, 2014 5:32 AM

Answers

  • I wont recommend this, but still in case still  you have to join Reporting DB with WSS_Content DB.

    Then join MSP_EMPProjectUserView with UserData table on Projectname.

    You already have ProjectName in MSP_EMPProjectUserView and using SQL Substring function you can fetch ProjectName from SiteURl or Workspave URL in UserData table. Problem Solved.

    Note : But this approach of reporting not recommended and supported by MS



    Sachin Vashishth MCTS

    • Marked as answer by geeam Monday, February 3, 2014 12:17 AM
    Sunday, February 2, 2014 7:28 PM

All replies

  • Hi,

    It's not recommend and supported by MS to directly hit WSS_Content database. Instead you can consume SP list in Reporting Services.

    For reference you can check these posts

    http://nikpatel.net/2010/04/30/step-by-step-consuming-sharepoint-lists-data-in-the-ssrs-reports

    http://sharepointtaskmaster.blogspot.in/2011/08/make-report-by-reporting-service-with.html


    Sachin Vashishth MCTS

    Friday, January 31, 2014 7:41 AM
  • Thanks for your reply. Another small question : how does the MSP_WssIssueToTaskLinks_UserView used in the above query differs from MSP_WssIssue table as both contain the same fields except for IssueUniqueId field.

    Friday, January 31, 2014 12:07 PM
  • In Project Server when you create a new issue, you can link that Issue to particulars task in Project Plan.

    This MSP_WssIssueToTaskLinks_UserView contains those details for the issue and linked task in Project Plan.

    And as you know MSP_WssIssue contains Issue details.

    Thanks and let me know in case have any doubts.


    Sachin Vashishth MCTS

    Friday, January 31, 2014 1:23 PM
  • Sachin. Thanks again for your reply. The above links primarily seem to be dealing with getting data through reporting with SharePoint list as data source. Not much on combining project server fields with fields from SharePoint custom list. Any suggestions for the best approach?
    Saturday, February 1, 2014 9:23 PM
  • I wont recommend this, but still in case still  you have to join Reporting DB with WSS_Content DB.

    Then join MSP_EMPProjectUserView with UserData table on Projectname.

    You already have ProjectName in MSP_EMPProjectUserView and using SQL Substring function you can fetch ProjectName from SiteURl or Workspave URL in UserData table. Problem Solved.

    Note : But this approach of reporting not recommended and supported by MS



    Sachin Vashishth MCTS

    • Marked as answer by geeam Monday, February 3, 2014 12:17 AM
    Sunday, February 2, 2014 7:28 PM
  • Hi Geeam,

    Well I don't know this would be best approach or not, but at least it would work for you and won't hurt MS!. Get the SP List data through BCS (google if you don't know) into your custom table (make one for User Data) and make a join with with built-in query. This way you'll have full command over the query.

    Hope this helps!


    IN
    Thanks, Kashif

    Tuesday, February 4, 2014 1:20 PM