dcsimg
 

Creating a Custom Report using Minitab, part 2

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.

Comments

Name: Tom • Tuesday, April 22, 2014

Very cool. My workplace uses a network license for Minitab, and it's possible that all licenses are in use, or if not connected to the network, opening Minitab will fail. Do you know of VBA that will tell if Minitab opened correctly before proceeding? Thanks!


Name: Daniel Griffith • Tuesday, April 22, 2014

Tom, that's a good question. Let me try to think up a creative solution.

You can borrow a network license, which would allow you to always have access to Minitab, but I'm not sure the License Coordinator would be a big fan of that.I'll try to follow up tomorrow.


Name: Tom Raidna • Wednesday, April 23, 2014

Dan,
This is interesting, I was looking at the database and he first few rows for march look like :
ID Yield Date
1 1 9.73 2014-03-01
2 2 13.48 2014-03-02
3 3 10.15 2014-03-03
4 4 11.09 2014-03-04
5 5 9.09 2014-03-05

this doesn't seem to match the data on the control chart in the article, did you use the same dataset that you have for down load?
Thanks
Tom


Name: Tom Raidna • Wednesday, April 23, 2014

Daniel,
I downloaded the database and was trying to compare to the report in the article, the data (see below) doesn't seem to match the control chart, did you use a different set of data?
Thanks
Tom

ID Yield Date
1 9.73 3/1/2014
2 13.48 3/2/2014
3 10.15 3/3/2014
4 11.09 3/4/2014
5 9.09 3/5/2014
6 11.04 3/6/2014


Name: Daniel Griffith • Wednesday, April 23, 2014

Tom, the data did change. Good catch. I previously had data just for March, but since this blog post was published in April I recreated the Access table to add more data and overwrote the data that I used in my original post. Oops! Good thing it was fake data.

The code should still work, but you will find that you do not have any out of control points, and Cpk is .73 in the new dataset.

As for checking into how to find if Minitab has an available license programattically. Once I have a solution, I will pass this along.


Name: Daniel Griffith • Thursday, April 24, 2014

Tom, I have done some digging and cannot find a method to handle this situation. Minitab uses third party software to keep track of concurrent usage, and I do not have access to the API to find if a license is available. If you need to have Minitab running often, my suggestion would be to use a borrowed license.

If the code will be run only once a month, you can use the following code, but if you receive a dialog asking for server details, you will need to interact with the dialog and then handle the error:

Dim mtbapp As Mtb.Application
On Error GoTo ErrHandler
Set mtbapp = New Mtb.Application

Sorry I couldn't be of more help. Borrowing may be the best solution in your case.


Name: Tom RAidna • Thursday, April 24, 2014

Daniel,
Thanks for taking time to reply, sorry I didn't mean to post my question twice. Thanks for the post it was interesting to see some Minitab functionality that was new to me.


Name: W. • Thursday, May 1, 2014

I get a error on mtbProject.Commands.Item(3).Outputs.Item(1).Graph.SaveAs "My network place", True, GFJPEG do I need to change some more?


Name: Daniel Griffith • Thursday, May 1, 2014

Hmm. Not sure. You can always check the output type of each command, and ensure that the output you are pulling from is a graph. You can do this using the following code (popping up a message box to show you the output type where the Graph Output Type = 0):
msgbox mtbProject.Commands.Item(3).Outputs.Item(1).OutputType

If you are still having trouble, feel free to shoot me an email at mentoring@minitab.com and I can take a look at the code for you.


Name: W. • Thursday, May 8, 2014

Daniel, it's working now. Thank for your support!!


blog comments powered by Disqus