0

I am trying to use PowerShell Group-by on the DataTable obtained from the SPListItemCollection. The reason for using the PS Group-By is because the CAML query groupby options do not return the SPListItemCollection already grouped.

I can group-by on 1 column fine. However, when I group on 2 columns, I am not sure how to get my intended output.

I want to group by a field called Contractor Name and then Project Name/Number. Then I use the ConvertTo-Html to convert it to HTML which works good for 1 column. However, the intended output is:

 CONTRACTOR NAME
    PROJECT #
         Project Name, Total Hours
    PROJECT #
         Project Name, Total Hours

 CONTRACTOR NAME
    PROJECT #
         Project Name, Total Hours
    PROJECT #
         Project Name, Total Hours

I have not been able to get this output.

Here is the psuedo-code - not posting the actual code because it is very simple and nothing too fancy but let me know if someone needs to see it.

Retrieve SPListItems using SPList.GetItems(CamlQuery) -- this returns SPListItemCollection
GetDataTable from SPListItemCollection
Group-By the data table on Contractor (store in variable GroupedResults)
For each GroupedResult in GroupedResults --> create HTML

The above works good for 1 field (contractor), but when I add another field to the group-by, I cannot get it in my required output.

Can someone please help me with it?

0

Sample tested script based on this thread

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$siteURL="http://sp"
$site = Get-SPSite $siteURL
$web = $site.RootWeb
$splList = $web.Lists.TryGetList("Projects")
$spqQuery = New-Object Microsoft.SharePoint.SPQuery 
            $spqQuery.Query =  
                "" 

$splListItems = $splList.GetItems($spqQuery) 
Write-Host $splListItems.Count      

$dt=$splListItems.GetDataTable()
$Contractor = "Contractor_x0020_Name";
$ProjectName = "Project_x0020_Name";
$ad = $dt | Group-Object $Contractor,$ProjectName # | Sort-Object -Property {$_.Values[1]},{$_.Count} -Descending

foreach($d in $ad){$d.Group | Format-Table @{Label="Contractor Name";Expression={$_["Contractor_x0020_Name"]}},@{Label="Project Name";Expression={$_["Project_x0020_Name"]}},@{Label="Total Hours";Expression={$_["Total_x0020_Hours"]}}}
<# or iterate
foreach($group in $ad){
    foreach($item in $group.Group){
        $item
    }
}
#>
Write-Host "---"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.