In this post I’m going to give a quick example using Microsoft Report Viewer in PowerShell. PowerShell makes is easy enough to slap together a simple report by converting data to HTML and adding some CSS but every now and then you need something a bit more polished. For instance generating a chart and or report in Word format that is ready for distribution or printing, laid out according to page size and orientation with headers, footers, logos etc. HTML doesn’t work that great for this so I did a POC using Microsoft ReportViewer 2012, this should work with the 2015 version as well but I didn’t try it.
I’m not going to dig into creating reports with the report viewer in this post, I’ll be focusing on using it with PowerShell. If you are not familiar with the report viewer you can catchup over here and there are some very good resources and samples on the GotReportViewer website as well. Short version, you design reports using the report designer in Visual Studio, at run time you pass it data and render the report to a file or display it in the report viewer. If you don’t have the report viewer installed you can download it here. The whole solution is in my GitHub repo here but I will be highlighting/explaining some aspects of it as we go along.
Code Highlights
When you design the report you have to assign data sources to it that will provide the report with the fields it will use at design time to author the report and it will also expect the same objects at run time with the populated data. You’ll have to create these in a .NET project, compile it and load the assembly along with the ReportViewer assembly into PowerShell.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.ReportViewer`.WinForms")
[System.Reflection.Assembly]::LoadWithPartialName("ReportPOC")In this solution the data source is the ReportData class in the ReportPOC assembly. When you create the data source collection you have to create a strongly typed collection, in this case a generic List of the data source type.
$data = New-Object "System.Collections.Generic.List[ReportPOC.ReportData]"
Creating the records and adding them to the data source is pretty straight forward.
$item1 = New-Object ReportPOC.ReportData $item1.ServerName = "Server1" $item1.CPUAvail = "128" $item1.CPUUsed = "103" $data.Add($item1)
Then you create the report viewer instance specify the path to your report file and add the data source.
$rep = New-Object Microsoft.Reporting.WinForms.ReportDataSource $rep.Name = "ReportDS" $rep.Value = $data $rv.LocalReport.ReportPath = "C:\MySource\ReportPOC\POC.rdlc"; $rv.LocalReport.DataSources.Add($rep);
Next you render the report and write the output byte array to a file stream, remember to cast the render result to type [byte[]] or else it won’t work.
[byte[]]$bytes = $rv.LocalReport.Render("WORDOPENXML");
$fs = [System.IO.File]::Create("C:\MySource\ReportPOC\POC.docx")
$fs.Write( [byte[]]$bytes, 0, $bytes.Length);
$fs.Flush()
$fs.Close()The result, a chart and report using the same data source with a page break between them as it displays in Word 2013:
Next Steps
If you want a re-usable solution I would create a more generic data source class to avoid coding a new data source class for different charts. Also add some parameters to the report/chart to control the headings, legend and labels, page headers/footers etc. You can also export to other formats by passing different parameters to the render method like “EXCEL”, “PDF”, “HTML” and “XML”. You can also create different series to group categories and apply logic to the report/chart to calculate values or control colors for example if CPU usage is > 90 % color the bar red, this is done in the report designer.
