Thursday, March 1, 2007

Another PowerGadgets example

I've blogged a few times before about the 2007 Microsoft Scripting Games.  There was 4 categories this year, and I originally thought I was going to map all the data like I previously did.  Instead, I though I'd do something different: represent the number of participants per country per category in a line graph using PowerGadgets.

Here's the finished product: all countries (with more than 5 total entrants).

Legend:

psaentries: PowerShell advanced, psbentries: PowerShell beginner, vbaentries: VBScript advanced,  and vbbentries: VBScript beginner.  total is just the sum of the former 4 values.

Here's an example of the 'mouse-over' features.  What I did here, was put my mouse over  psaentries in the legend box: PowerShell advanced entries.

Here's another advanced feature where I add a "data grid", so I can view all the data in a table format just below the graph:data grid option.

And finally, one more advanced feature where I do a mouse-over of a particular data point, and I get: a popup tooltip.

To keep the graph "less busy", I've only added countries who had a total number of participants of 5 or more.

All the entries for each category are here: VBScript beginner, VBScript advanced, PowerShell beginner, and PowerShell advanced.

Inspired by /\/\o\/\/, here's the script I used:

$countries=@{}

[string]$pre_url="http://www.microsoft.com/technet/scriptcenter/funzone/games/games07/"
[string]$post_url="scores.mspx"

$categories=@("psa","psb","vba","vbb")

$j=0
$z=0

foreach ($category in $categories){
[string]$url=${pre_url}+${category}+${post_url}
$wc = new-Object System.Net.WebClient
$nl = $wc.DownloadString("$url")
$r = [regex]'l">(.*?)</p'
$m = $r.Matches($nl)
$l = $m |% {$_.groups[1].value}
$list = @()
foreach ($i in 13..($l.count -1) ) {
$Record = new-Object -typename System.Object
$Record | add-Member -memberType noteProperty -name Name -Value $m[$i].groups[1].value
[void] $foreach.MoveNext()
$Record | add-Member -memberType noteProperty -name Country -Value $m[$foreach.current].groups[1].value
1..10 |% {[void] $foreach.MoveNext();$Record | add-Member -memberType noteProperty -name "E$_" -Value $m[$foreach.current].groups[1].value}
[void] $foreach.MoveNext();$Record | add-Member -memberType noteProperty -name "Total" -Value $m[$foreach.current].groups[1].value
$list += $record
}
$list|group Country|`
%{if($countries[$_.name] -eq $null){$countries[$_.name]="0,0,0,0,0"}
$country_val0=$countries[$_.name].split(',')[0]
$country_val1=$countries[$_.name].split(',')[1]
$country_val2=$countries[$_.name].split(',')[2]
$country_val3=$countries[$_.name].split(',')[3]
$cur_count=$_.count
set-variable country_val$z $cur_count
$countries[$_.name]="${country_val0},${country_val1},${country_val2},${country_val3},0"
}
$j=$j+2
$z++
}

$arr_countries=@($countries.keys)

$arr_countries|`
%{[string]$curr_values=$countries[$_]
[int]$country_tot0=$curr_values.split(',')[0]
[int]$country_tot1=$curr_values.split(',')[1]
[int]$country_tot2=$curr_values.split(',')[2]
[int]$country_tot3=$curr_values.split(',')[3]
[int]$country_sum=${country_tot0}+${country_tot1}+${country_tot2}+${country_tot3}
$countries[$_]="${country_tot0},${country_tot1},${country_tot2},${country_tot3},${country_sum}"
}

$listing=@()

foreach ($arr_country in $arr_countries){
[string]$cur_country=$countries[$arr_country]
[int]$cur_country0=$cur_country.split(',')[0]
[int]$cur_country1=$cur_country.split(',')[1]
[int]$cur_country2=$cur_country.split(',')[2]
[int]$cur_country3=$cur_country.split(',')[3]
[int]$cur_country4=$cur_country.split(',')[4]
$record=new-object -typename system.object
$record|add-member -membertype noteproperty -name Country -value $arr_country
$record|add-member -membertype noteproperty -name PSAEntries -value $cur_country0
$record|add-member -membertype noteproperty -name PSBEntries -value $cur_country1
$record|add-member -membertype noteproperty -name VBAEntries -value $cur_country2
$record|add-member -membertype noteproperty -name VBBEntries -value $cur_country3
$record|add-member -membertype noteproperty -name Total -value $cur_country4
$listing+=$record
}

$listing|?{$_.Total -ge 5}|`
out-chart -palette highcontrast -label country -values psaentries,psbentries,vbaentries,vbbentries,total -AllSeries_Gallery Lines -AllSeries_PointLabels_Visible false

[end code]

(I'm still trying to figure out blogspot code formatting...  Leave a comment if you'd like me to send you the script.)