729128_en-US

liliconcom

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

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.

 

 

 

Automation and ScriptingBasic Data Analysis and ModelingRe: How to build boxplot chart and save it into local then insert into ppt

Upload the test data.

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

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", "<DL><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;<BR />);</SS></MM></HH24></YYYY></MMM></DD></DL>

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

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?

 

 

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

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 

 

 

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

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", "<DL><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=""><P>&nbsp;</P><P>&nbsp;</P></SAVE></SS></MM></HH24></YYYY></MMM></DD></DL>

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

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