#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

How To: Get Report Containing Work Area Participants

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.

Got a quick one for you today.  Had a request to get a list of all participants assigned to a Rich Project / Work Area and export the results out to an Excel spreadsheet.

We will be using the following cmdlets to accomplish this task. All of the cmdlets are available using 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-PWFolderSecurity
  • Show-PWFolderBrowserDialog
  • ConvertTo-DataTable
  • New-XLSXWorkbook

First thing we need to do is determine which Rich Project / Work Area to report on. We are going to use the Show-PWFolderBrowserDialog to quickly select our project. The selected project’s ProjectWise Folder object will be returned and we will use this to  populate the $PWProject variable.

# Select Project to report on.
$PWProject = Show-PWFolderBrowserDialog


We selected the ‘BSI200 – Peterborough Geospatial Investigation’ project.

The following shows the returned folder object.


Next we will obtain any access control applied to the Project / Work Area using the Get-PWFolderSecurity cmdlet. We will need to filter the results to exclude any inherited access control objects.

# Get all folder security applied to the selected Project / Work Area.
# The following returns all access control including any inherited.
$PWAccessControl = Get-PWFolderSecurity -InputFolder $PWProject -Verbose

The following shows a couple of the entries returned. You can see one of them is inherited from a workflow at the datasource level.


We can exclude these entries by applying a Where-Object as shown in the following.

# We can add a Where-Object to only return the access control 
# explicitly applied to the Project / Work Area.
$PWAccessControl = Get-PWFolderSecurity -InputFolder $PWProject | 
Where-Object InheritingFrom -Like 'none*' 

You can see the inherited object is no longer returned.


We will now convert the returned list of access control objects to a datatable using the ConvertTo-DataTable cmdlet. This is a requirement for the New-XLSXWorkbook cmdlet.

# Convert the access control list to a datatable to be exported.
$dt = ConvertTo-DataTable -InputObject $PWAccessControl

The following shows some of the datarows within the datatable after the conversion.


Finally, we need to export the datatable to an Excel spreadsheet using the New-XLSXWorkbook cmdlet.

# Export the datatable to the specified output file. Include the -Open switch parameter
# to immediately open the spreadsheet after the export process is complete.
New-XLSXWorkbook -InputTables $dt -OutputFileName 'd:\temp\export\accesscontrol.xlsx' -Open -Verbose

The resulting spreadsheet should look something like the following.


Now that we’ve gone through all of the individual steps, I am going to show you how to accomplish this is one line of PowerShell code by using piping.

Select the Project / Work Area to report on.
Apply the resulting ProjectWise Folder object to the Get-PWFolderSecurity cmdlet.
Pipe the resulting objects to the Where-Object to filter on only access control
objects that are explicitly applied to the Project / Work Area.
Next pipe the resulting Access Control objects to the ConvertTo-DataTable cmdlet
to create a datatable that will then be piped to the New-XLSXWorkbook cmdlet for 
exporting to the specified path and file name.
Get-PWFolderSecurity -InputFolder (Show-PWFolderBrowserDialog) -Verbose |
Where-Object InheritingFrom -Like 'none*' |
ConvertTo-DataTable |
New-XLSXWorkbook -OutputFileName 'd:\temp\export\accesscontrol.xlsx' -Open -Verbose

Experiment with it and have fun.

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

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.