So your boss is going to want stats for your OSD sequence? [Part 2 – Reporting with Excel]

How to provide hard statistics on your build sequences.

---

In Part 1 I went into how I collected the data that would be used to report on the 2 goals my boss set out for our OS deployment; 1) a 90% success rate and 2) 60 minute build times.

---

Part 2: Reporting on the Results using Excel

With the data collected, you can then report on it using a variety of tools.  You could open the CSV in Excel and create graphs and pivot tables to illustrate the results.  You could import it into Tableau or Power BI and create the reports.  We’ll start with Excel here and  get into Tableau and Power BI in the next posts.

Report Examples

Over time I’ve used all three to generate reports on build statistics.  I started with some basic Excel spreadsheets and then moved to Tableau when I started creating charts and graphs to put on our SharePoint site.  Then at MMS this past Spring I attended a session presented by Mike Terrill and Ronnie Jakobsen called “Power BI and Configuration Manager: Data Your Way”.  Well, I was hooked on Power BI at that point.

Sample Data

To help things along I wrote a small script that you can use to generate some sample data.  [You can download it here.]

I’ve also made my sample CSV file available as well here any my sample Excel workbook here.

The script will read in 3 simple text files.  The first is a list of computer model names.  I populated it with the top 70-some models being used at work.  The second is a list of task sequence names.  I just made up some names for a little variety.  The final is a list of failure reasons.  I just took some sample failures from work and populated this list.  Keep the script and the 3 text files in the same folder.  Feel free to modify and/or replace the contents of the 3 list files.

The script will prompt you for how many records you wish to generate.  It will then start writing out an “OSDResults_Sample.csv” file.  The computer names will be “PC####” in numerical order.  Build start and finish times will be randomly generated.  Model names, task sequence names and success or failures will also be randomly generated.  I geared it to generate about a 4% failure rate.  For those tagged as failing, a random failure reason from the text file will be selected.

I’ll be using the same sample results file generated from this script for all of my examples.

I’ll illustrate basic reporting on:

• Results (Success vs. Failure)
• Average Build Times
• Model Counts
• Failure Reasons

Excel

Let’s start with Excel.  Of course, you can probably do similar reports using any spreadsheet application like OpenOffice Calc or Google Docs Sheets.  We’re going to use Pivot Table/Charts.  Pivot Tables are a great tool in Excel that makes consolidating data very easy.  For an introduction to Pivot Tables see here.

You can download my Excel workbook which will have all of these Pivot Tables and Charts here.

Open the CSV in Excel and select all of the cells (Ctrl-A).

From the Insert menu select Pivot Table and create the pivot table on a new tab.

---

Results (Success vs. Failures)

We are going to want to see the ratio of failed builds to successful builds.  If you have different build sequences you can break it down by those sequences or you can show overall success v. failures.

For overall results, in the Pivot Table editor, drag Results from the field list to the
“Rows” area.  Then drag Results from the field list to the “∑ Values” area.

You’ll end up with a cute little table like this:

 

On the Ribbon, you should see the options for the various chart types.  Select the pie chart and pick one to your liking.

It will drop a simple pie chart onto the sheet.

It’s as simple as that.  You can edit the charts to change headings, colors, etc.  But that is the basic process for the other Pivot Tables/Charts we are going to create.

For example, here is a different chart that shows the results broken down by each task sequence.

The Pivot Table editor is just setup slightly different.

 

By dragging the TSName field to the “Rows” area the data is broken down by the different task sequences.

Next, drag Results to the “Columns” area so that successes and failures will be tallied up separately.

Finally, drag Results to the “∑ Values” area to tally up the different results.

For the graph, I just selected a stacked horizontal bar graph.  Excel automatically worked out the bar lengths as a percentage.

 

 

 

 

 

---

Average Build Times

Create another Pivot Table.  For this one, we’re going to look at average build times.

 

Like before, drag TSName from the available fields to the “Rows” area.

Next, drag BuildTime to the “∑ Values” area.  You’ll notice that Excel added up all of the build times.  Click on the arrow next to “Sum of BuldTime” in the “∑ Values” area and select “Value Field Settings”.

 

 

 

 

 

 

 

 

Change the Summarize value field by to Average.  Click on the Number Format button to adjust things like the number of decimal places if you would like.

For the graph, simply select a basic horizontal bar chart.

---

Model Counts

Create another Pivot Table tab.  (You should be an old hand at this now.)

 

Drag Model to the “Rows” area and then drag Model to the “∑ Values” area.  You’ll then have a table showing how many of each model has been built.

---

Failure Reasons

Create one last Pivot Table tab.  Time to see what problems are causing our failures so we can focus on big issues.

As you’ve probably guessed, drag FailureReason to the “Rows” area and then drag FailureReason to the “∑ Values” area.

For the graph, again just a simple horizontal bar chart.

 

 

 

 

 

 

 

 

 

 

Now, notice that you have “Success” far out pacing everything else in the graph.  That’s a good thing, but this report is meant to focus on the reasons builds are failing.

It is easy to remove the “Success” entries.  At the top of the table, click on the down arrow by Row Labels and remove the checkmark from “Success”.

There, fixed it!

---

That’s some basic examples on how you can report on your build results using Excel.  It’s easy and it’s quick.  You can dress up the charts, put them into documentation, or onto your internal web site.  Plus with some decent PowerShell you could automate this entire process.

Next time we’ll do the same sort of thing using Tableau.  Again, I’ll use the exact same sample data to make it easier to compare reports side by side.

Advertisement