How To: Generate Report For Checked Out Documents

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

Recently, I had a user needing to generate a report listing all ProjectWise documents checked out. He had created a saved search within ProjectWise Explorer which quickly returned all document objects where the status equaled ‘Checked Out’. Easy enough. However, when he attempted to generate a report within PowerShell, he was not getting the user the document was checked out to, or the date in which the document was checked out. Ultimately, the user was looking for a report containing the document name, user name, checked out date, full path, and the document URN. The following is what I came up with.

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available with the PWPS_DAB module. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWDocumentsBySearch
• Select-PWSQL
• New-XLSXWorkbook

First thing we will do is define a few variables containing values for the saved search name, report date, and the export file name.

# Saved Search looking for Documents that meet our Requirements
$SavedSearch = 'Checked Out'

# Populate Report date for File Name
$ReportDate = Get-Date -Format yyyyMMdd
$FileName = "d:\Temp\CheckedOut_$($ReportDate)_Report.xlsx"

Next, we will use the Get-PWDocumentsBySearch cmdlet to return document objects for all of the ProjectWise documents returned by the saved search.

# Get all checked out documents.  
$PWDocuments = Get-PWDocumentsBySearch -SearchName $SavedSearch -GetAttributes

The following shows we had 10 document objects returned from the saved search.

We will create a datatable to add document information to for export. The datatable name will be “CheckedOut”. We will add 5 column.

$dt = New-Object System.Data.Datatable ("CheckedOut")
$dt.Columns.Add('DocumentName') | Out-Null
$dt.Columns.Add('UserName') | Out-Null
$dt.Columns.Add('CheckedOutDate') | Out-Null
$dt.Columns.Add('FullPath') | Out-Null
$dt.Columns.Add('DocumentURN') | Out-Null

Now, we can loop through each of the document objects. Using the document GUID, we can run a SQL query to get the user a document is checked out to, and the date in which it was checked out. We will, then add the results to a new datarow. We will also add the FullPath and DocumentURN values from the document object to the datarow. Once that is complete we can add the datarow to the datatable.

# Loop through each document object
foreach ($PWDoc in $PWDocuments) {
    $SQLString = "SELECT D.o_itemname 'Document_Name', U.o_username 'User_Name', D.o_dmsdate 'CheckedOut_Date' 
                  FROM dms_doc D, dms_user U
                  WHERE o_docguid LIKE '$($PWDoc.DocumentGUIDString)' AND D.o_dmsuserno = U.o_userno" 
    $SQLResults = Select-PWSQL -SQLSelectStatement $SQLString -Verbose
    
    # Create new datarow
    $dr = $dt.NewRow()
    # Populate the datarow with the metadata from the document object
    #    and the results of the SQL query.
    $dr.ItemArray = $SQLResults.Rows[0].ItemArray
    $dr.FullPath = $PWDoc.FullPath
    $dr.DocumentURN = $PWDoc.DocumentURN
    # Add datarow to the datatable.
    $dt.Rows.Add($dr)
}

The following is an example of the data returned from the Select-PWSQL cmdlet.

Finally, we export the resulting datatable to an Excel spreadsheet.

New-XLSXWorkbook -InputTables $dt -OutputFileName $FileName -Open -Verbose

The following shows the resulting Excel spreadsheet.

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.