How To: Export ProjectWise Datasource Statistics To Excel Using PowerShell

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.

I want to show a simple way to create an Excel file containing ProjectWise Datasource  Statistics data. When run over the course of several weeks, months, years, this data can be used for datasource analysis, trending reports, etc.  We will be using PowerShell of course.  Thanks to Mahesh Patel for the idea.

The cmdlets we will focus on are:

• Get-PWStatistics
• New-XLSXWorkbook

First things first. Take a look at the help for the cmdlets mentioned to familiarize yourself with the available parameters, etc.

Get-Help Get-PWStatistics -Full
Get-Help New-XLSXWorkbook -Full

The Get-PWStatistics has a couple of optional parameters. The -ForceRefresh parameter allows you to force an update of the statistics prior to obtaining the data. And the -IgnoreLastLoginCount parameter, allows you to skip the reporting for user logins as this can take some time to complete.

The New-XLSXWorkbook has a couple of required parameters and one optional parameter.  The two required parameters are as follows:

• InputTables – allows us to pass an array of tables to be exported to Excel
• OutputFileName – output name of Excel file. If the Excel file already exists, tables will be added.

The -Open parameter is optional and allows you to open the Excel file immediately after exporting the datatables.

---

First, we will obtain the ProjectWise Datasource Statistics for the current datasource. In this example we will be forcing a refresh on the datasource statistics.

$PWStatistics = Get-PWStatistics -ForceRefresh -Verbose

Next, we will create a new datatable to populate with the statistics data. We will name the table  using the following format: “Stats_YYYY_MM_DD”.

$dt = New-Object System.Data.Datatable ("Stats_$(Get-Date -Format yyyy_MM_dd)")

Now, we need to add the columns to the datatable. We can use the property names of the statistics object as our column names. If you aren’t familiar with the Get-Member cmdlet, it is worth a look.

# Get a list of the statistics properties.
$PWStatisticsProperties = $PWStatistics | Get-Member -MemberType Property

# Add columns to the datatable for each of the properties in $docProperties
foreach ($property in $PWStatisticsProperties){
    $dt.Columns.Add($property.Name) | Out-Null
}

Next, we will create a new datarow, loop through the statistics data and populate the row, and then add the row to the datatable.

# Create New Datarow
$dr = $dt.NewRow()

# Populate Datarow with statistics values
foreach ($column in $dt.Columns) {
    $dr[$column.ColumnName] = $PWStatistics.$($column.ColumnName)
}

# Add new row to the datatable
$dt.Rows.Add($dr)

Finally, we will export the new datatable to the specified Excel spreadsheet.

# Export datatable to an Excel spreadsheet
$Splat = @{
    InputTables = $dt
    OutputFileName = "$Path\$($DatasourceName.Split(':')[1].ToString())_Statistics.xlsx"
}
New-XLSXWorkbook @Splat

Experiment with it and have fun.

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