Friday, January 4, 2013

PowerShell Reporting and a rant

Today I worked my way through +Don Jones's "Making Historical and Trend Reports in PowerShell".  This is a Beta 1 copy, but something really popped when I read it.

Let me say a few disjoint items, then tie them together.


  1. When it comes to reporting data, anything that puts the output in front of a user fulfills the idea of reporting to some extent.
  2. There was an earlier E-Book on HTML Reporting in November when I was preparing a user group presentation on reporting options.  I started a brief discussion @ http://powershell.org/discuss/viewtopic.php?f=2&t=665 where I was really grasping at straws on how to go from table reporting to a more graphic, chart based report.  
  3. In the newer ebook, Don references the horrors that come from taking something simple like Excel and COM or the Office .NET Interop (http://www.microsoft.com/en-us/download/details.aspx?id=3508) and building via commands data and charts.  It is amazing that even with simple parts that this does become a blob of code that automates user actions, which is not the ideal way you want to generate a report.
  4. Will was at one point working with Primal Forms to build a basic UI.  I had a problem with this because Primal Forms builds a PS1 from its project contributing all of the ugly winforms initialization.   If you want to change something after you have modified the generated PS1 then you have use the original project, and re add all of your own code.  At least this was true when we were looking at it several months ago.
  5. One of the things I see come up is when is PowerShell the right tool, and when should you move on to a more robust development solution.  Sometime this grey area can be tough to see, but once your single .PS1 gets giant, and you think, "Hey should I move this to a module?" you should also ask should I move the code to C#?  Because at this point managing the Project can be a key component.  I am not discounting other reason to go between .PS1 .PSM1 and .DLL, but size can be a factor.
  6. For the January DFW PowerShell meeting, +Michael Cruz is geoing to show us a WPF UI built in PowerShell.  He sent me a copy of this to show me the direction.  What I absolutely loved about it, was that he was copying the XAML directly into an @String.  This meant that if something needed to be tweaked it could be pulled out, changed, and with some restrictions everything would work when it was copied back.  We have the maintainability sitting great, and the convenience of a single file deployment. 
  7. Along with with the Reporting Options presentation, I had built up a sample HTML Generation process that used the Google Charts API to show some simple information.  To get the information into the charts, I converted PowerShell hashtables to AJAX. (See below)  I was going for more proof of concept then the Reusable PowerShell style and sole that Don put in the HTML reporting document.  I would have liked to make it work the way we had discussed in the PowerShell.org discussion, but it looked like it was just too complicated wiring the <DIV> tags in HTML and making sure there was one for any chart or table generated against them.
  8. When doing the Reporting Options I went with the Google AP vs. the MS API that Don had used because there were more online examples of hand wiring the Javascript.
  9. look past the code below.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
<#
  'title': 'Final gold medal count for 2012 olympics',
  'titleTextStyle': { color: 'black', fontName: '"Verdana"', fontSize: 12 },
  'width': 350,
  'height': 350,
  'backgroundColor': 'white',
  'chartArea': { 'width': '90%', 'height': '70%' },
  'pieSliceText': 'percentage',
  'tooltip': { textStyle: { color: 'black', fontName: '"Verdana"',fontSize:12}},
  'is3D': true
    vAxis: {title: 'Candidate',  titleTextStyle: {color: 'blue'}},
20           hAxis: {title: '# Contributions',  titleTextStyle: {color: 'blue'}}
#>
function GooglePieChart {
Param ($Data = @{'Yin'=1;'Yang'=1},
 $HeaderRow = [ordered]@{'Topping'='string';'Slices'='number'},
 $options = @{"title"="Test Title";
            "width"=500;
            "height"=500;           
            "is3D"="true";
            'legend'='bottom'; # top, none, left, right, bottom
            "titleTextStyle"=@{"color"="black";
                                "fontName"='"Verdana"';
                                "fontsize"=12};
            'backgroundColor'= 'white';
            'pieSliceText'= 'percentage'
            }
 )
 
$HTML = @"
<html>
  <head>
    <!--Load the AJAX API-->
    <!--     background-color:#b0e0e6; -->
    <style>
    .left
    {
        float:left;
        width:300px;   
    }
    .right
    {
        float:right;
        width:300px;     
    }
    </style>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
 
      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart','table']});
      google.load('visualization', '1.0', {'packages':['corechart','table']});      
      //google.load('visualization', '1.0', {'packages':['piechart']});
 
 
      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);
 
      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
       function drawChart() {
 
        // Create the data table.
        var data = new google.visualization.DataTable();
        
"@
$columns = $HeaderRow
$HTML +=  $columns.GetEnumerator() | % { "data.addColumn('$($_.Value)','$($_.Name)');`n"; }   
 
if ($data.Gettype().Name -eq "OrderedDictionary")
{
    $HTML += "data.addRows($($data.count));`n"
    $i=0;
    $HTML +=  $data.GetEnumerator() | % {
        "data.setValue($i,0,'$($_.Name)');`ndata.setValue($i,1,$($_.Value));`n";
         $i++; }
} elseif ($data[0] -is "object[]") {
    #$JSON =     ($Data | ConvertTo-JSON -compress) -replace '([{]"value":)|(,"Count":2[}])'
    $JSON =     ConvertTo-JSON -compress $Data
    $HTML += "data.addRows($JSON);`n"
} else {
    $HTML += "data.addRows($($data.count));`n"
    $i=0;
    $a=@();$a += $columns.keys | %{ $_ };
    $HTML +=  $data| % {
        "data.setValue($i,0,'$($_.$($a[0]))');`ndata.setValue($i,1,$($_.$($a[1])));`n";
         $i++; }
}
    
$HTML += "var options =$($options | ConvertTo-Json  );"
$HTML += @"
        // Instantiate and draw our chart, passing in some options.
      //  alert('Test')
         
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
         
        var chart2 = new google.visualization.BarChart(document.getElementById('chart_div2'));
        chart2.draw(data, options);
 
        var visualization = new google.visualization.Table(document.getElementById('table'));
        visualization.draw(data, options);
 
      }
    </script>
  </head>
 
  <body>
    <!--Div that will hold the pie chart-->
    <div>
    <div class="left" id="chart_div"></div>
     
    <div class="right" id="chart_div2"></div>
    </Div>
     
 
    <div id="table"></div>
 
    <div id="Links">
    <p>If you don't see anything try a refresh.</P>
    <div><a href="">https://developers.google.com/chart/interactive/docs/examples</a>https://developers.google.com/chart/interactive/docs/examples </div>
    <!-- <div><a href=""></a> </div> -->
     
    </div>
 
 
  </body>
</html>
"@
 write-output $HTML
}
#  $oie = Out-InternetExplorer -NavigateTo "$home\Dropbox\Nov12SIG\googlechart.html" -PassThru
#$oie = Out-InternetExplorer -Text ([io.file]::ReadAllText("$home\Dropbox\Nov12SIG\googlechart.html")) -PassThru
 
$Source = [ordered]@{'Apples'= 3;'Onions'=1;'Olives'= 1;'Zucchini'=1;'Pepperoni'= 2}
#$Source = @(('Mushrooms', 3),('pie',1),('Olives',1),('Zucchini',1),('Pepperoni', 2))
#$source = @([PSCustomObject][ordered]@{"Fruit"="Apple";"Amount"="2"},[PSCustomObject][ordered]@{"Fruit"="orange";"Amount"="5"})
$ObjectHeader =[ordered]@{'Fruit'='string';'Amount'='number'}
$html = GooglePieChart $Source -HeaderRow $ObjectHeader


I was looking at the SSRS report generated in the current ebook, and I thought, oh, all I need to do to is save this HTML and just wire up some AJAX or ViewState tweaks to inject new data.

I mean if you need to persist data and you can use a database, use that database.  Sometimes though I just need a snapshot of a data collection and get an idea for the top item types and see the data in something that can be easily passed to someone else for feedback or to alert them to issues.

Despite all of the above rambling, the "POP" that I had was about complexity and maintenance.  Populating an HTML or Excel report from PowerShell is the role that PowerShell should fill.  If you want a single HTML report to attach to an email, then generate it in one of the many HTML tools out there.  Then wire your data to it.  Trying to Define the report in PowerShell is where it can be cumbersome, ugly, bulky, and a maintenance nightmare.  If you want a report in Excel Build your excel document and import your data from CSV.

I know the idea of reporting tools are ages old, but really they are built for this, so we should use them.
Add to this things like XAML that were designed for MVVM development and the abstraction we need is built in.

Good night,
        Josh