#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Add Document Descriptions to a Reference Report

In this post, I am going to step through the process of adding addition columns to a reference file report generated using the Get-PWReferenceReportForDocuments. By default, this report does not include the document / file descriptions. I recently had a request to add this information to the report. This is one approach which does not require any changes made to the PWPS_DAB module. And the concept can be used to manipulate other datatables when generating reports.

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

  • Get-PWDocumentsBySearch
  • Get-PWReferenceReportForDocuments
  • New-XLSXWorkbook
  • Import-PWDocuments
  • Get-Item

Parameters

First thing we need to do is create our parameter and value pairs. The comments will explain the purpose for each parameter. I am also going to include a few requires statements to ensure the correct version of PowerShell and the PWPS_DAB module are used. As well as require that the Run As Administrator option is used to run the PowerShell Console or PowerShell ISE. 

#Requires -Version 5.0
#Requires -Modules @{ModuleName="PWPS_DAB";ModuleVersion='1.12.2.0'}
#Requires -RunAsAdministrator

[CmdletBinding()]
param(
# ProjectWise Rich Project / Work Area / Folder to generate a reference report for.
[string] $pwFolderPath = 'BSI900 - Adelaide Tower',
# ProjectWise folder to import document into.
[string] $pwFolder_Import = 'PowerShell\Reports',
# Local folder to create report in.
[string] $Path = 'd:\TEMP\Export',
# Report path and name.
[string] $OutputFilePath = "$Path\$($pwFolderPath)_ReferenceReport_$(Get-Date -Format yyyyMMdd).xlsx",
# Name for datatable. Will correspond to worksheet created in Excel.
[string] $DataTableName = 'ReferenceReport'
)

Begin

The next thing we will do is ensure the specified folder exists, and that we are logged into ProjectWise.  I am going to add these steps into the BEGIN section of the script.

I want to take a second and point out a couple of conventions that I use in my scripts. First, you may notice that I put a comment at the end of many of the blocks of code. Doing this makes it easier for me to determine where a block of code starts and ends. Comes in handy when troubleshooting a script.  I also add text ([BEGIN], [PROCESS], [END]) in the output messages (Write-Verbose, Write-Warning, Write-Error) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.

BEGIN {
    $Continue = $true

    $StartTime = Get-Date
    Write-Verbose -Message "[BEGIN] Start time: $StartTime"

    <# Test to see if the provided path exists. If not, set the variable Continue to false.
          This will be used in the PROCESS code block to exit the script. #>
    if( -not (Test-Path -Path $Path -PathType Container -Verbose -ErrorAction Stop)) {
        Write-Warning -Message "[BEGIN] Folder '$Path' not found. Update path variable value and try again."
        $Continue = $false
    }
    <# Determine if we are currently logged into a ProjectWise datasource.
         If not, display the ProjectWise Login Dialog. #>
    if(Get-PWCurrentDatasource) {
        Write-Verbose -Message "[BEGIN] Currently logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'."
    } else {
        if(New-PWLogin -UseGui) {
            Write-Verbose -Message "[BEGIN] Successfully logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'."
        } else {
            Write-Error -Message '[BEGIN] Failed to log into ProjectWise datasource.'
            $Continue = $false 
        }
    } # end if(Get-PWCurrentDatasource...
} # end BEGIN

PROCESS

Now that we verified our local folder exists, and we are logged into a ProjectWise datasource, we can proceed to the PROCESS code block.

The followings shows the specified project to report on within ProjectWise.

pwc

You can see that if the Continue variable equals false, a warning message will be displayed and we will proceed to the END code block to log out of ProjectWise and exit the script.

PROCESS {
    if( -not ($Continue)) {
        Write-Warning -Message "[PROCESS] Exiting script."
    } else { ... }
} # end PROCESS

 

Next, we will populate the $pwDocuments variable with the ProjectWise document objects returned from the Get-PWDocumentsBySearch cmdlet. We will then run the Get-PWReferenceReportForDocuments to return the reference data within a datatable. We will give the datatable a name. This name will correspond to the worksheet within the Excel file when we export the data.

Note: I am deliberately not piping the results of the Get-PWDocumentsBySearch. If I do, the result will be an array of datatables. One datatable for each document which returned reference data. 

# Get array of document objects to get reference information for.
$pwDocuments = Get-PWDocumentsBySearch -FolderPath $pwFolderPath

<# Return reference objects. Excluding the output file path to only
return a datatable. #>

$dtReferenceData = Get-PWReferenceReportForDocuments $pwDocuments

<# Give the table a name. When exported to Excel, this will be used
to name the worksheet. #>

$dtReferenceData.TableName = $DataTableName

Now, we get to the main purpose of this post. We are going to add two columns to the existing $dtReferenceData datatable. Each column will be placed in a specific position within the datatable to keep the related data next to each other.

  • ParentFileDescription
  • ReferenceFileDescription

We will get the position of the ParentFileName column within the datatable. Once we have it’s position (ordinal) we can insert the ParentFileDescription column immediately following by incrementing one ordinal value. This process is repeated for the ReferenceFileName.

#region ADD Descriptions to report

<# Insert a new column for the Parent File descriptions.
Columns will be created after the corresponding column. #>

$dtReferenceData.Columns.Add("ParentFileDescription").SetOrdinal($($dtReferenceData.Columns |
Where-Object ColumnName -EQ 'ParentFileName' |
Select-Object Ordinal).Ordinal + 1) | Out-Null

# Insert a new column for the Reference File descriptions.
$dtReferenceData.Columns.Add("ReferenceFileDescription").SetOrdinal($($dtReferenceData.Columns |
Where-Object ColumnName -EQ 'ReferenceFileName' |
Select-Object Ordinal).Ordinal + 1) | Out-Null

With both description columns added to the datatable, we can now loop through the datatable and populate the descriptions with the appropriate values. For each row in the datatable, we will use the Get-PWDocumentsBySearch cmdlet using data from the row to select the corresponding document. We will then extract the description data and add it to the two description columns in the current row.

# Loop through each row in the $dtReferenceData datatable to the descriptions.

# The following is a counter for the progress bar.
$Counter = 1
$ItemCount = $dtReferenceData.Rows.Count
# Loop through each item in the collection.
foreach ($row in $dtReferenceData.Rows) {

#region PROGRESS SECTION

$Progress = @{
Activity = "Getting document information for '$($row.ReferenceFileNameBare)'."
Status = "Processing $Counter of $ItemCount"
PercentComplete = $([math]::Round($(($Counter / $ItemCount) * 100 ), 2))
}
Write-Progress @Progress -Id 1

# Increment the counter.
$Counter++

#endregion PROGRESS SECTION

<# Get the parent file description using the ParentFolder and
ParentFileName values. #>
$Splat_ParentFile = @{
FolderPath = $row.ParentFolder
FileName = $row.ParentFileNameBare
}
$ParentFileDescription = Get-PWDocumentsBySearch @Splat_ParentFile -JustThisFolder |
Select-Object Description

<# Get the reference file description using the ReferenceFolder
and ReferenctFileName values. #>
$Splat_ReferenceFile = @{
FolderPath = $row.ReferenceFolder
FileName = $row.ReferenceFileNameBare
}
$ReferenceFileDescription = Get-PWDocumentsBySearch @Splat_ReferenceFile -JustThisFolder |
Select-Object Description

$row["ParentFileDescription"] = $ParentFileDescription.Description
$row["ReferenceFileDescription"] = $ReferenceFileDescription.Description
} # end foreach ($row...

#endregion ADD Descriptions to report

Now that we have the datatable updated with the descriptions, we can export it out to an Excel workbook. 

# Create new report
New-XLSXWorkbook -InputTables $dtReferenceData -OutputFileName $OutputFilePath -Verbose

The following shows the progress bar in action.

progress

Next we will import the Excel file into the specified ProjectWise folder.

# Import generated report into the specified ProjectWise folder.
Import-PWDocuments -InputFolder $OutputFilePath -ProjectWiseFolder $pwFolder_Import -CreateVersions -ExcludeSourceDirectoryFromTargetPath

The following shows the target folder within ProjectWise after the report was imported.

pwc2

And now that the document has been successfully imported into ProjectWise, we need to clean up the file from the local drive. This is the last step accomplished within the PROCESS block.

    # Delete generated report from local drive.
Get-Item -Path $OutputFilePath | ForEach-Object {$_.Delete()}
} # end PROCESS

END

Lastly, we will proceed to the END block of code to log out of our ProjectWise session. You will also see that we have calculated the amount of time it had taken to complete the process.

END {
    $EndTime = Get-Date
    Write-Verbose -Message "[END] It took $([Math]::Round($EndTime.Subtract($StartTime).TotalMinutes, 2)) minutes to complete process."

    Write-Verbose -Message '[END] Logging out of ProjectWise.'
    Undo-PWLogin
} # end END

The following shows the contents of the report generated.

resultsresults1


The following is a link to the full PowerShell script.

HowTo_GenerateReferencesReport_v1

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s