Live SPC Dashboard from Excel Report

[#1]

Hello JMP Community,

This is my first time posting and my first large JMP scripting project so please bear with me.This forum has been very helpful to get where I am so far.  

Goal: Have one JMP dashboard that opens and parses all excel files located in a directory displaying IR charts for each asset number. Live in the sense that each time the dashboard/script is opened it runs and updates the charts.

Constraints: Cannot change layout of excel reports. 

Below are my snipets of code that work OK independently. When I put it all together, JMP crashed on me. 

I might be approaching this all incorrectly. It may be easier to open each file and append the columns to a new data table. Based on how many columns it could generate X number of charts.

Any help would be greatly appreciated.

Code to create control chart with reference line. Would like to pull the value for reference line from attached spreadsheet force requirement field and title from asset number field.

View more...

Control Chart(
	Sample Label( :Date ),
	Group Size( 1 ),
	KSigma( 3 ),
	Chart Col( :Pull Force Reading, Individual Measurement ),
	SendToReport(
		Dispatch(
			{"Individual Measurement of Pull Force Reading"},
			"2",
			ScaleBox,
			{Min( 5 ), Max( 18 ), Inc( 2 ), Minor Ticks( 1 ), Add Ref Line( 8, "Solid", "Black", "LSL", 4 )}
		),
		Dispatch(
			{"Individual Measurement of Pull Force Reading"},
			"1",
			ScaleBox,
			{Min( 0 ), Max( 50 ), Inc( 8 ), Minor Ticks( 0 )}
		),
		Dispatch(
			{"Individual Measurement of Pull Force Reading"},
			"IR Chart of IM",
			FrameBox,
			{Frame Size( 820, 420 )}
		),
		Dispatch(
			{"Individual Measurement of Pull Force Reading"},
			"IR Chart of IM",
			FrameBox( 2 ),
			{Frame Size( 65, 405 )}
		)
	)
);

Code to open all files in directory, but files or data need to have headers row 1 and data starting row 2 in order to work.

View more...

dirpath = Pick Directory();
TheFileList = Files In Directory( dirpath );

For( i = 1, i < 100, i++,
	dt = Open( "F:\IPC\IPC PULL TEST RECORD LOGS\" || TheFileList[i], worksheet( "Sheet1" ) );
	dt << Control Chart(
		Sample Label( :Date ),
		Group Size( 1 ),
		KSigma( 3 ),
		Chart Col( :Pull Force Reading, Individual Measurement ),
		SendToReport(
			Dispatch(
				{"Individual Measurement of Pull Force Reading"},
				"2",
				ScaleBox,
				{Min( 5 ), Max( 18 ), Inc( 2 ), Minor Ticks( 1 ), Add Ref Line( 8, "Solid", "Black", "LSL", 4 )}
			),
			Dispatch(
				{"Individual Measurement of Pull Force Reading"},
				"1",
				ScaleBox,
				{Min( 0 ), Max( 50 ), Inc( 8 ), Minor Ticks( 0 )}
			),
			Dispatch( {"Individual Measurement of Pull Force Reading"}, "IR Chart of IM", FrameBox, {Frame Size( 820, 420 )} ),
			Dispatch( {"Individual Measurement of Pull Force Reading"}, "IR Chart of IM", FrameBox( 2 ), {Frame Size( 65, 405 )} )
		)
	);
);

 Code to format one file by rearranging headers and data.

View more...

dt = Open("F:\IPC\IPC PULL TEST RECORD LOGS\Asset# 3042.xlsx", worksheets("sheet1"));

dt << Delete rows (1::13);

var = dt:column 11[2];
dt:column 11[8] = var; //data

var = dt:column 11[1];
dt:column 11[7] = var; //heading

var = dt:column 3[1];
dt:column 12[7] = var; //heading

var = dt:column 3[2];
dt:column 12 [8] = var; //asset #

dt << delete rows (1::6);
dt << delete columns(1,3,5,7,9,13::25);

colNames = dt << get column names;

For( i = 1, i <= N Items( colNames ), i++,
  Column( i ) << set name( Column( i )[1] )
);

dt << delete rows(1);

[#2 · Accepted solution]

I think this is close to what you want.  It may need some tweeking, but at a minimum it should give you a good start on how to put this all together

Names Default To Here( 1 );

// Delete the old Dashboard
Try( NW << close window );

dirpath = Pick Directory();
TheFileList = Files In Directory( dirpath );

// Create the Dashboard window to place the charts into
NW = New Window( "Asset Dashboard", <<journal, VLB = V List Box() );

// Loop across all of the files found
For( i = 1, i <= N Items( TheFileList ), i++,
	dt = Open( dirpath || "\" || TheFileList[i], worksheet( "Sheet1" ), invisible );
	
	dt << Delete rows( 1 :: 13 );

	var = dt:column 11[2];
	dt:column 11[8] = var; //data

	var = dt:column 11[1];
	dt:column 11[7] = var; //heading

	var = dt:column 3[1];
	dt:column 12[7] = var; //haeding

	var = dt:column 3[2];
	dt:column 12[8] = var; //asset #

	dt << delete rows( 1 :: 6 );
	dt << delete columns( 1, 3, 5, 7, 9, 13 :: 25 );

	colNames = dt << get column names;

	For( ii = 1, ii <= N Items( colNames ), ii++,
		Column( ii ) << set name( Column( ii )[1] )
	);

	dt << delete rows( 1 );

	// Capture the Force Requirement value and add it to the graph if it is valid
	dt:Pull Force Reading << data type( numeric ) << modeling type( continuous );
	theForceRequirement = Num( Word( 1, dt:Force Requirement[1], " " ) );
	If( Is Missing( theForceRequirement ) == 0,
		theForceRequirement = ",Add Ref Line(" || Char( theForceRequirement ) ||
		", \!"Solid\!", \!"Black\!", \!"LSL\!", 4 )",
		theForceRequirement = " "
	);

	// Capture the Asset number
	If( dt:Name( "Asset#" )[1] == "",
		theAsset = "Unknown",
		theAsset = dt:Name( "Asset#" )[1]
	);

	// Generate the code to produce the chart along with the LSL value
	theExpr =
	"cc = Control Chart(invisible,
	Sample Label( :Date ),
	Group Size( 2 ),
	KSigma( 3 ),
	Chart Col( :Pull Force Reading, Individual Measurement , Moving Range),
	SendToReport(
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"2\!",
			ScaleBox,
			{Min( 5 ), Max( 18 ), Inc( 2 ), Minor Ticks( 1 )"
	 || theForceRequirement ||
	" }
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"1\!",
			ScaleBox,
			{Min( 0 ), Max( 50 ), Inc( 8 ), Minor Ticks( 0 )}
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"IR Chart of IM\!",
			FrameBox,
			{Frame Size( 820, 420 )}
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"IR Chart of IM\!",
			FrameBox( 2 ),
			{Frame Size( 65, 405 )}
		)
	)
);";

	// Run the generated code
	Eval( Parse( theExpr ) );

	// Close the outline box of the output and set the title based upon the asset value
	Report( cc )[Outline Box( 1 )] << Close( 1 ) << set title( "Asset# " || theAsset );

	// Append the display output to the display window
	VLB << Append( Report( cc ) );

	// Clean up the environment by closing the display window and the data table
	cc << close window;
	Close( dt, nosave );
);  // End of For Loop
  
  
    

[#3]

Jim,

I have seen you contribute on many discussions during my research and troubleshooting. I am truly honored to have you contribute to my discussion here.

At first I had some problems while running the script. After looking through the log and debugger I found that it was stopping on a certain file. The next file was a test file I had formatted differently. I removed that file, didn't change the script and it worked beautifully. I later had a file open when running the script and received another error, but that was an easy fix (close the file).

I intend to understand the entirety of your code, but I have a few further customizations I hope you could also help with:

• Possible to add LSL value to graph and even move to the same side (right) as UCL/LCL?
• Some data sets have a LSL of 50+. This doesn't fit on current graph so I modified the range to 1 - 100. Is there a way to take the pull force requirement and use that to +/- 10 or 20 for the range?
• Some excel sheets are empty. Is there a way to omit graphs?

Edit: I took some time today to add the customizations above and here is the modified code for the above. Looks like it is quite hard to change the reference label. Only hint I found was to make a new text box and use that code, not sure how to link the Y heigh of the reference line though.

Names Default To Here( 1 );

// Delete the old Dashboard
Try( NW << close window );

dirpath = Pick Directory();
TheFileList = Files In Directory( dirpath );

// Create the Dashboard window to place the charts into
NW = New Window( "Asset Dashboard", <<journal, VLB = V List Box() );

// Loop across all of the files found
For( i = 1, i <= N Items( TheFileList ), i++,
	dt = Open( dirpath || "\" || TheFileList[i], worksheet( "Sheet1" ), invisible );
	
	// Format data table
	dt << Delete rows( 1 :: 13 );
	var = dt:column 11[2];
	dt:column 11[8] = var; //data
	var = dt:column 11[1];
	dt:column 11[7] = var; //heading
	var = dt:column 3[1];
	dt:column 12[7] = var; //heading
	var = dt:column 3[2];
	dt:column 12[8] = var; //asset #
	dt << delete rows( 1 :: 6 );
	dt << delete columns( 1, 3, 5, 7, 9, 13 :: 25 );
	
	// Set first row to headers and delete
	colNames = dt << get column names;
	For( ii = 1, ii <= N Items( colNames ), ii++,
		Column( ii ) << set name( Column( ii )[1] )
	);
	dt << delete rows( 1 );

	// Continue to next asset if data table is empty
	col=Column( Current Data Table(), "Pull Force Reading");
	tableVal = col[22];
	if (tableVal == "", i++);

	// Capture the Force Requirement value and add it to the graph if it is valid
	dt:Pull Force Reading << data type( numeric ) << modeling type( continuous );
	theForceRequirement = Num( Word( 1, dt:Force Requirement[1], " " ) );
	If( Is Missing( theForceRequirement ) == 0,
		range = theForceRequirement;
		theForceRequirement = ",Add Ref Line(" || Char( theForceRequirement ) || ", \!"Dashed\!", \!"Black\!", \!"LSL\!", 4 )";,
		theForceRequirement = " "
	);

	// Capture the Asset number
	If( dt:Name( "Asset#" )[1] == "",
		theAsset = "Unknown",
		theAsset = dt:Name( "Asset#" )[1]
	);

	// Generate the code to produce the chart along with the LSL value
	theExpr =
	"cc = Control Chart(invisible,
	Sample Label( :Date ),
	Group Size( 2 ),
	KSigma( 3 ),
	Chart Col( :Pull Force Reading, Individual Measurement(Show Zones( 1 ), Shade Zones( 1 ),Test 1( 1 ), Test 3( 1 ))),
	SendToReport(
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"2\!",
			ScaleBox,
			{Min( if( (range - 20) <= 0 , 0, range - 20) ), Max( range + 20 ), Inc( 2 ), Minor Ticks( 1 )"
	 || theForceRequirement ||
	" }
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"1\!",
			ScaleBox,
			{Min( 0 ), Max( 50 ), Inc( 8 ), Minor Ticks( 0 )}
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"IR Chart of IM\!",
			FrameBox,
			{Frame Size( 820, 420 )}
		),
		Dispatch(
			{\!"Individual Measurement of Pull Force Reading\!"},
			\!"IR Chart of IM\!",
			FrameBox( 2 ),
			{Frame Size( 65, 405 )}
		)
	)
);";

	// Run the generated code
	Eval( Parse( theExpr ) );

	// Close the outline box of the output and set the title based upon the asset value
	Report( cc )[Outline Box( 1 )] << Close( 1 ) << set title( "Asset# " || theAsset );

	// Append the display output to the display window
	VLB << Append( Report( cc ) );

	// Clean up the environment by closing the display window and the data table
	//cc << close window;
	//Close( dt, nosave );
);  // End of For Loop