none
Reporting on linked documents

Answers

  • Hello,

    Here is an example SQL query those shows documents that are linked to Issue items, this could easily be changed to show documents linked to Tasks on the Task list on the project site:

    Select		P.ProjectName
    		,	D.DocumentUniqueID
    		,	D.DocumentID
    		,	D.Title as [Document Title]
    		,	P2.ProjectName as [Related Project Name]
    		,	I.IssueUniqueID
    		,	I.Title as [Issue Title]
    		,	RT.Description as [Relationship Type]
    From		MSP_WssDocument D
    INNER JOIN	MSP_WssListItemAssociation LA
    ON			LA.ListItemUID = D.DocumentUniqueID
    INNER JOIN	MSP_WssRelationshipType RT
    ON			RT.RelationshipTypeID = LA.RelationshipTypeID
    INNER JOIN	MSP_WssIssue I
    ON			I.IssueUniqueID = LA.RelatedItemUID
    INNER JOIN	MSP_EpmProject P
    ON			P.ProjectUID = D.ProjectUID
    INNER JOIN	MSP_EpmProject P2
    ON			P2.ProjectUID = LA.RelatedProjectUID
    where		LA.RelationshipTypeID = '24'

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Sunday, March 19, 2017 8:40 AM
    Moderator

All replies

  • Hello,

    Here is an example SQL query those shows documents that are linked to Issue items, this could easily be changed to show documents linked to Tasks on the Task list on the project site:

    Select		P.ProjectName
    		,	D.DocumentUniqueID
    		,	D.DocumentID
    		,	D.Title as [Document Title]
    		,	P2.ProjectName as [Related Project Name]
    		,	I.IssueUniqueID
    		,	I.Title as [Issue Title]
    		,	RT.Description as [Relationship Type]
    From		MSP_WssDocument D
    INNER JOIN	MSP_WssListItemAssociation LA
    ON			LA.ListItemUID = D.DocumentUniqueID
    INNER JOIN	MSP_WssRelationshipType RT
    ON			RT.RelationshipTypeID = LA.RelationshipTypeID
    INNER JOIN	MSP_WssIssue I
    ON			I.IssueUniqueID = LA.RelatedItemUID
    INNER JOIN	MSP_EpmProject P
    ON			P.ProjectUID = D.ProjectUID
    INNER JOIN	MSP_EpmProject P2
    ON			P2.ProjectUID = LA.RelatedProjectUID
    where		LA.RelationshipTypeID = '24'

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Sunday, March 19, 2017 8:40 AM
    Moderator
  • Issue and risk links get registered in the database, however documents do not. At least not in 2016 on my deployment.

    I cannot see any document in the document tables or views.


    Rene Alvarez

    Monday, April 24, 2017 11:17 PM