#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

How To: Get Report Containing Work Area Participants

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

showfolderdialog

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

The following shows the returned folder object.

showfolderdialog_results

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.

access_inheriting

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.

access_notinheriting

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.

access_dt

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.

access_export

 

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 )

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

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