How to set up a report window so that combined data table will show by value

[#1]

I'm trying to recreate JMP's internal behavior: When doing a combined table in a distribution report, the resulting table will list the by value in the first column. When I create my own nested table structure I do not get this first column in the resulting combined data table. How can I define my window structure in such a way that I will generate two identical combined data tables?

See the difference by running this example script:

/* Open a sample data table */
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

/* Generate the Distribution with a By variable */
dist = dt << Distribution( Continuous Distribution( Column( :height ) ), By( :sex ) ); 

/* Create the combined data table of Summary Statistics */
Report( dist[1] )["Summary Statistics"][Table Box( 1 )] << Make Combined Data Table;

TestWindow = new window("Test with multiple tables",main = v list box());
main << Append(
	Outline Box( "Distributions sex=F",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{60.8888888888889, 3.61189031311679, 0.851330711102296, 62.6850396850604, 59.0927380927173, 18, 1096, 13.0457516339869,
						5.93193664562977, 0}
					)
				)
			)
		)
	)
);
main << Append(
	Outline Box( "Distributions sex=M",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{63.9090909090909, 4.3084533840777, 0.918565347870119, 65.8193521238087,
						61.9988296943731, 22, 1406, 18.5627705627705, 6.7415344558826, 0}
					)
				)
			)
		)
	)
);

TestWindow["Summary Statistics"][Table Box( 1 )] << Make Combined Data Table;

[#2]

https://community.jmp.com/t5/Discussions/JSL-Combined-graphs-from-multiple-data-table-and-combined-i...

[#3]

Unfortunately that is not what I want. I allready have the combined window. Now I want to give my customers the ability to create a useful combined data table from it.

[#4]

Just with the code you posted , I received the data table - is this not what you expect  ?

[#5]

Hi @pauldeen,

There are two approaches I found to tackle this problem.

The first approach is the simplier of the two, but requires putting the By variable value as a column in your summary statistics tables. In the example below, see the additional "Sex" column, which is initially collapsed but is uncollapsed temporarily to make the combined data table. See comments for details.

TestWindow = new window("Test with multiple tables",main = v list box());
main << Append(
	Outline Box( "Distributions sex=F",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box( "Sex", Repeat( {"F"}, 10 ), <<Visibility( "Collapse" ) ),
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{60.8888888888889, 3.61189031311679, 0.851330711102296, 62.6850396850604, 59.0927380927173, 18, 1096, 13.0457516339869,
						5.93193664562977, 0}
					)
				)
			)
		)
	)
);
main << Append(
	Outline Box( "Distributions sex=M",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box( "Sex", Repeat( {"M"}, 10 ), <<Visibility("Collapse") ),
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{63.9090909090909, 4.3084533840777, 0.918565347870119, 65.8193521238087,
						61.9988296943731, 22, 1406, 18.5627705627705, 6.7415344558826, 0}
					)
				)
			)
		)
	)
);

// Get a list of all of all invisible columns 
collapsedCols = TestWindow  <<Xpath( "//StringColBox[@isVisible='false']" );

// turn on the visibility of these columns
collapsedCols << visibility( "visible" );

// make a combined data table from the tables
TestWindow[TableBox(1)] << Make Combined Data Table();

// set the columns back to collapsed
collapsedCols << visibility( "Collapse" );

Here is the resulting table using this method:

 The second method is to grab the By value information from the OutlineBox and concatenate the tables together. This method does not require any changes to the setup to your existing OutlineBox/Sumary Statistics setup, but is a little more complicated. See comments below for details.

TestWindow = new window("Test with multiple tables",main = v list box());
main << Append(
	Outline Box( "Distributions sex=F",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{60.8888888888889, 3.61189031311679, 0.851330711102296, 62.6850396850604, 59.0927380927173, 18, 1096, 13.0457516339869,
						5.93193664562977, 0}
					)
				)
			)
		)
	)
);
main << Append(
	Outline Box( "Distributions sex=M",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{63.9090909090909, 4.3084533840777, 0.918565347870119, 65.8193521238087,
						61.9988296943731, 22, 1406, 18.5627705627705, 6.7415344558826, 0}
					)
				)
			)
		)
	)
);

// get all of the tableboxes within a 'Summary Statistics' OutlineBox
tableBoxes = TestWindow << Xpath("//OutlineBox[text()='Summary Statistics']/TableBox");

//Create and insert each data table
dts = {};
For( i = 1, i <= N Items( tableBoxes ), i++,
	tempDt = tableBoxes[i] << make into data table( "invisible" );
	//set the name of the table to the title of the top outlinebox
	tempDt << Set Name( (((tableBoxes[i] << Parent) << parent) << parent) << get title );
	
	Insert Into( dts, tempDt );
);

// Concatenate the tables together, with a source column
dts[1] << Concatenate( Remove( dts, 1 ), Create source column );

// close all of the temporary tables
For( i = 1, i <= N Items( dts ), i++,
	Close( dts[i] )
);

Here is the resulting table:

[#6]

Justin, thanks for your answers, both seem to do the trick and if nobody comes forward with a simpler solution you have definetly helped me! So let me leave the question open for a little while longer and if nobidy comes up with a better answer, I will mark yours as the answer.

It seems to me that JMP does something else internally (like setting a table property that gets parsed?) and I'm hoping that this trick is exposed to the scripting interface and can be applied here.

[#7]

The JMP platform generated TableBoxes do keep track of the By groups internally, but it is not something you can set yourself when using a custom JMP window.

Feel free to keep it open in case I missed something!

[#8]

Hmm the problem still remains that if people do the right click --> make combined data table in the report window, neither of these work and I'm stuck with adding a column that is nothing but the same value repeated. I was hoping to avoid that.

[#9 · Accepted solution]

You can circumvent that by creating a function to create your combined data table and set the function to run as a context menu script for the TableBox. See below for details. Please note that you can add some of the other context menu items back if you need them (with a little work) using the JSL message equivalents.

customMakeCombinedDataTable = Function({},
	// Get a list of all of all invisible columns 
	collapsedCols = TestWindow  <<Xpath( "//StringColBox[@isVisible='false']" );

	// turn on the visibility of these columns
	collapsedCols << visibility( "visible" );

	// make a combined data table from the tables
	TestWindow[TableBox(1)] << Make Combined Data Table();

	// set the columns back to collapsed
	collapsedCols << visibility( "Collapse" );
);

// List to be used as the argument in the "Set Context Menu Script" message
contextMenuScriptList = {"Make Combined Data Table", customMakeCombinedDataTable()};

TestWindow = new window("Test with multiple tables",main = v list box());
main << Append(
	Outline Box( "Distributions sex=F",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				tb1 = Table Box(
					String Col Box( "Sex", Repeat( {"F"}, 10 ), <<Visibility( "Collapse" ) ),
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{60.8888888888889, 3.61189031311679, 0.851330711102296, 62.6850396850604, 59.0927380927173, 18, 1096, 13.0457516339869,
						5.93193664562977, 0}
					),
					<<Set Context Menu Script( contextMenuScriptList )
				)
			)
		)
	)
);
main << Append(
	Outline Box( "Distributions sex=M",
		Outline Box( "height",
			Outline Box( "Summary Statistics",
				Table Box(
					String Col Box( "Sex", Repeat( {"M"}, 10 ), <<Visibility("Collapse") ),
					String Col Box("",
						{"Mean", "Std Dev", "Std Err Mean", "Upper 95% Mean", "Lower 95% Mean", "N", "Sum", "Variance", "CV", "N Missing"}
					),
					Number Col Box("",
						{63.9090909090909, 4.3084533840777, 0.918565347870119, 65.8193521238087,
						61.9988296943731, 22, 1406, 18.5627705627705, 6.7415344558826, 0}
					),
					<<Set Context Menu Script( contextMenuScriptList )
				)
			)
		)
	)
);