Creating a Custom Report using Minitab, part 2

Minitab Blog Editor | 4/22/2014

Topics: Data Analysis

Now that you’ve seen how to automatically import data and run analyses in my previous post, let’s create the Monthly Report!

I will be using a Microsoft Word Document (Office 2010) and adding bookmarks to act as placeholders for the Graphs, statistics, and boilerplate conclusions.

Let’s go through the steps to accomplish this:

  1. Open up an existing report that you have previously created in Microsoft Word.
  2. Highlight a section of the document where you would like to place the created Minitab graph or statistic.
  3. Go to the Insert tab, click the Bookmark link, and type in the name of what you will be replacing.  In this instance, I typed ‘NormalityPlot’ and clicked Add:
     

  1. Repeat the steps above for each graph and statistic that will need to be inserted into your report.

Now go to the Developer tab in Microsoft Word, and click on Macros.  Here you can enter the name of your macro and click Create.

Let’s first make sure we reference the Minitab COM API, so Microsoft Word can talk directly to Minitab.  In Visual Basic for Applications, go to Tools > References, and check the box for ‘Mtb 17.0 Type Library’.

 

We can now start coding.  Let’s start by declaring some variables and initializing them:

Dim mtbApp As Mtb.Application
Dim mtbProject As Mtb.Project
Dim mtbWorksheet As Mtb.Worksheet

Set mtbApp = New Mtb.Application
Set mtbProject = mtbApp.ActiveProject
Set mtbWorksheet = mtbProject.ActiveWorksheet

' We can even have Minitab run behind the scenes, hidden from the user.
mtbApp.UserInterface.Visible = False
mtbApp.UserInterface.DisplayAlerts = False

The code above gets Minitab running.  Next let’s run the Minitab exec we created in the last blog post:

mtbProject.ExecuteCommand "Execute 'C:UsersdgriffithDesktopblog.mtb' 1."

(If you’re following along, you’ll need to alter the exec location C:USERSDGRIFFITHDESKTOPblog.mtb to reflect the location of the file on your machine.)

Now we can tell Minitab to save the graphs as JPEGs and place them in the Report. Once again, you’ll need to change the file locations to match yours.

mtbProject.Commands.Item(3).Outputs.Item(1).Graph.SaveAs "C:normtest", True, GFJPEG

ActiveDocument.Bookmarks("NormalityPlot").Range.InlineShapes.AddPicture "C:normtest.jpg"

mtbProject.Commands.Item(4).Outputs.Item(1).Graph.SaveAs "C:controlchart", True, GFJPEG

ActiveDocument.Bookmarks("ControlChart").Range.InlineShapes.AddPicture "C:controlchart.jpg"

mtbProject.Commands.Item(6).Outputs.Item(1).Graph.SaveAs "C:capability", True, GFJPEG

ActiveDocument.Bookmarks("Capability").Range.InlineShapes.AddPicture "C:capability.jpg"

At this point, we have placed all the graphs successfully into the report.  Let’s add some boilerplate text around the Normality test P-Value, the number of data points that are out of control, and whether or not Cpk meets our guideline of 1.33:

Dim pValue As Double

pValue = mtbWorksheet.Columns.Item(3).GetData(1, 1)

If pValue <= 0.05 Then

    ActiveDocument.Bookmarks("Pvalue").Range.Text = "Data does not pass Normality Test"

Else

    ActiveDocument.Bookmarks("Pvalue").Range.Text = "Since Normality Test P-Value is greater than 0.05. Assume Normal Distribution"

End If

Dim oocPoints As Integer

oocPoints = mtbWorksheet.Constants.Item(1).GetData

If oocPoints = 0 Then

    ActiveDocument.Bookmarks("OutOfControl").Range.Text = "Data appears to be stable over time"

ElseIf oocPoints = 1 Then

    ActiveDocument.Bookmarks("OutOfControl").Range.Text = "Investigation is needed as 1 data point was found to be out of control."

Else

    ActiveDocument.Bookmarks("OutOfControl").Range.Text = "Investigation is needed as " & oocPoints & " data points were found to be out of control."

End If

Dim cpk As Double

cpk = mtbWorksheet.Columns.Item(5).GetData(1, 1)

If cpk < 1.33 Then

    ActiveDocument.Bookmarks("cpk").Range.Text = "Cpk does not pass Acceptable Guideline of 1.33."

Else

    ActiveDocument.Bookmarks("cpk").Range.Text = "Cpk passes Acceptable Guideline of 1.33.  Process is operating at Acceptable level."

End If

So what does all this get us?  The code above generates the following report:

Running the Microsoft Word macro automatically generates a report that communicates with Minitab Statistical Software (in the background) and provides an understanding of our process for the current month.  And now that the code is written, you have a fully functioning Report generation tool that works with the press of a button. 

Pretty neat, if you ask me.

If you are intimidated by this type of process, we provide a service that does the programming for you. More information can be found here. You can also e-mail us, and I will personally follow up to see if we can automate any processes for you.  Also, if you would like to see additional examples illustrated in a blog post, feel free to post a comment below.

If you want to get your hands dirty in the programming, you can find more information in Minitab’s Help System:

For Minitab Command Language, go to Help > Help and click the link for Session Commands.

For Minitab’s API, go to Help > Help and click the link for Minitab Automation.