Introduction

Intended audience

This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.

Context

In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell.

Now, it’s time to use what we’ve learned so far.

We will first build a general purpose methodology for building charts. In short, we will build a template script that we will then apply to practical examples and check we can build different kinds of charts using this template.

Creating a chart, general case

In next section, we will have a close look at some practical examples with sample data. While each example will show a different kind of chart, the same principle will be used all the time and it’s the subject of this section: presenting a generic script that can be specialized for each kind of chart.

Creating a chart object and setting some properties

Here is a generic function that will be used to build an empty chart object. We will limit to set following properties:

• Width
• Height
• Title
• Background color
• Title (if any)

It will also optionally create a default chart area.

Here is a sample call:

Common ChartArea object creation and settings instructions

In this example, a default chart area is created. We will access it using following code:

We can create one using the code from above:

In that case, it’s preferable to give a name to the areas:

We could by the way set titles for X and Y axes of this area as follows:

We could also set visual attributes like the interval step to be used for one or both axes:

Or also precise the kind of data that is stored for that axis:

The type of interval is a value of the enumeration called:

Please, refer to documentation page for further details about axis settings.

Defining a legend for our chart

We can create a Legend object and add it to the $Chart object we created.

The name of the Legend object instance is pretty important. We will use it when adding data series.

Adding data series to an existing chart object

In order to create data it’s pretty simple and straight forwards:

As we set a name to the series, here is a mean to get back to the object quite simply:

Alternately, you could use following PowerShell function:

Example usage

Following code will create a chart series that will be displayed as a histogram (bar columns) and add it to the $Chart object we defined previously:

Adding data points to a data series

If we look at the Series object documentation, we will see that it has a collection object called Points that stores all the data points of the chart. This object has multiple methods to add data to it and we will review two of them:

• DataBindXY(<XaxisValues>,<YaxisValues>)
• AddXY(<XaxisValue>,<YaxisValue>)

The former will allow you to bind data sets if you got all the values that go to the X axis separately from all the values that go to the Y axis. A good case usage for this is when your data is stored in a HashTable from which it’s easy to get back only keys and only values:

The latter will be used to add one point at a time. It’s pretty useful when you run a query that returns the (key,value) pair against SQL Server and you want to create a chart with the results of that query.

In that case, we will use following algorithm:

Displaying a chart

Now we saw how to build a chart object instance and add data to it. That a good point, but we need to do something with that object and why not displaying it on screen?

To do so, we will define a PowerShell function that will create a window form and add the chart to it and display.

Exporting a chart to an image file

Now, let’s say we just want to store the chart on disk. It’s pretty simple to accomplish using the SaveImage method from the Chart object class:

The available file formats are defined in the System.Drawing.Imaging.ImageFormat class.

Amongst the 12 formats defined in this class, we will find:

• BMP
• GIF
• JPEG
• PNG

Putting everything together

You will find below as a summary a generic template that can be used to build any kind of chart.

Example usages

In following, we will use AdventureWorks database and a different T-SQL query per example.

As we don’t want to give a good reading experience to our readers, we will use different queries and different data sets and generate charts.

Creating a pie chart

We will take data from the HumanResources.Employee table and create a pie chart that will show the number of employees by gender and marital status. This is translated to following T-SQL query:

This query gives back following results:

Now, let’s build a chart object using following PowerShell script. We will use the general template and add two parameters for target server instance and database. We will choose the option of displaying the chart on screen.

Here is the PowerShell code:

We can see that most employees are single men (what we could have noticed based on the results set).

Creating a bar chart

We will use following query and same algorithm except that we will define a Bar chart and not a Pie chart.

This query gives back following results set:

Here is the corresponding PowerShell Code:

And here is the corresponding chart:

If you take a closer look to the code, you see that, except the query that is different, the only line that has changed is the assignment to the $ChartSeriesType variable.

Going further

These two examples will give you, I hope so, the knowledge necessary to be able to create advanced charts with multiple chart areas and/or multiple series into the same chart.

As a last advice, don’t forget that a chart area will need you to define the same values for X axis along all the series when you want to plot something like this:

In this example, I set values of 0 for occurrences of LOG backups in the “FULL-WITH-LOG backups occurrences” series and 0 for occurrences of FULL-WITH-LOG backups in the “LOG backups occurrences” series.

By the way, if you are willing to get a more in-depth knowledge on the DataVisualization namespace, there are two ways to do so. Firstly, you can go through the documentation and test yourself on concrete examples (that would add some value to your job, it’s always a better way to learn). Secondly, you could also get into Visual Studio, try to build charts using WYSIWYG tools and look at the code used behind.

Previous article in this series:

• Overview of PowerShell Charting capabilities

• Author
• Recent Posts

Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Latest posts by Jefferson Elias (see all)

• How to perform a performance test against a SQL Server instance - September 14, 2018
• Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
• How to link two SQL Server instances with Kerberos - July 5, 2018