How to build boxplot chart and save it into local then insert into ppt

[#1]

Hi,JMPer

I had one test data like as below.

I want to auto build boxplot chart which can compare each Col with Fixture, and each Col had one remark. (The title format: Fixture Comparison：Col--Remark).

How to auto build those boxplot chart loop with Col_1、Col_2、Col_3、Col_4. And auto save boxplot chart then insert into ppt?

Thank you very much.

[#2]

Upload the test data.

[#3]

I don't have much experience saving PowerPoint presentations, but this script does what you asked for, assuming the same column format every time.  It allows for more or fewer :Col_n columns without needing to make any adjustments.

Names Default To Here( 1 );
dt = Open(
	"$DOWNLOADS/jmp test data.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
thedate = Uppercase( Format( Today(), "Format Pattern", "<DD><MMM><YYYY> <hh24><mm><ss>" ) );
cols = Filter Each( {val, idx}, dt << Get Column Names( "String" ), Contains( val, "Col_" ) );
remarkarray = Associative Array( :Col, :Remark );
For Each( {val, idx}, cols,
	text = val || "--" || remarkarray[Substitute( val, "_", "" )];
	pres = dt << Graph Builder(
		Size( 534, 450 ),
		Show Control Panel( 0 ),
		Variables( X( :Fixture ), Y( Column( val ) ) ),
		Elements( Points( X, Y, Legend( 7 ) ), Box Plot( X, Y, Legend( 8 ) ) ),
		SendToReport( Dispatch( {}, "graph title", TextEditBox, {Set Text( "Fixture Comparison: " || text )} ) )
	);
	If( idx == 1,
		Report( pres ) << Save Presentation( "$DOCUMENTS/Fixtures " || thedate || ".pptx" ),
		Report( pres ) << Save Presentation( "$DOCUMENTS/Fixtures " || thedate || ".pptx", Append )
	);
 (pres << Get Window) << Close Window;);

[#4]

Hi, mmarchandTSI

Thank you very much. "Associative Array" is useful for me.

The picture save to PPT sames not beautiful. 

I want to the picture save to ppt like this. Is it possible?

[#5]

You could copy the pictures to the clipboard and paste them to PPT via a PowerShell script, like it's done in the Graph Builder Toolbar 

[#6]

Thank you hogi. I used MAC, it sames the toolbar not effective for me. 

[#7]

In this post @julian gives an example how to script in on a Mac:
https://community.jmp.com/t5/Discussions/Powerpoint-trigger-paste-clipboard/m-p/598508/highlight/tru... 

[#8 · Accepted solution]

This method will keep the original Graph Builder format using a few more lines of code.

Names Default To Here( 1 );
dt = Open(
	"$DOWNLOADS/jmp test data.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
thedate = Uppercase( Format( Today(), "Format Pattern", "<DD><MMM><YYYY> <hh24><mm><ss>" ) );
cols = Filter Each( {val, idx}, dt << Get Column Names( "String" ), Contains( val, "Col_" ) );
remarkarray = Associative Array( :Col, :Remark );
For Each( {val, idx}, cols,
	text = val || "--" || remarkarray[Substitute( val, "_", "" )];
	pres = dt << Graph Builder(
		Size( 534, 450 ),
		Show Control Panel( 0 ),
		Variables( X( :Fixture ), Y( Column( val ) ) ),
		Elements( Points( X, Y, Legend( 7 ) ), Box Plot( X, Y, Legend( 8 ) ) ),
		SendToReport( Dispatch( {}, "graph title", TextEditBox, {Set Text( "Fixture Comparison: " || text )} ) )
	);
	Report(pres)<<Save Picture("$DOCUMENTS/Fixtures " || thedate || " " || Char(idx) || ".png","png");
	pic = New Image("$DOCUMENTS/Fixtures " || thedate || " " || Char(idx) || ".png");
	nw = New Window("ppt pix",pb = Picture Box(pic));
	If( idx == 1,
		pb << Save Presentation( "$DOCUMENTS/Fixtures " || thedate || ".pptx" ),
		pb << Save Presentation( "$DOCUMENTS/Fixtures " || thedate || ".pptx", Append )
	);
	(pres << Get Window) << Close Window;
	nw << Close Window;
);

[#9]

Thank you very much.

I will used python-pptx for generate PPT file.