How to create subcategories for rows and columns similar to tabulate

[#1]

I'm currently working on creating a data table of results from a SQL query. The final data table has a set up that merges the shared column groups and merges the rows of shared values as well (see Table 1).

The data sometimes has numeric values, but most of the results are strings like "Negative" or "< 0.1". The way I summarize the data table with the character/nominal values is by transposing the results based on the type of test. I can group the columns, but when I send the data table to a new window as a report or to a journal page, it doesn't display the grouped columns. I can't use Tabulate because it only summarizes numeric values and it also won't allow you to keep the formatting when you make it into a data table. I also can't color the cells within a Tabulate table. I just want the ability to add subcategories and nested columns/rows like tabulate does to a regular data table. I'm wondering if there's any way to achieve what I'm looking for within JMP using JSL code, and if there isn't, can this be a feature request?

Table 1. Example of Final Table

[#2 · Accepted solution]

JMP does not allow you to group rows.  Tabulate does not group rows, it simply leaves some rows blank (see below).

If you look at the tree structure for Tabulate then you'll notice that JMP uses special constructs such as MultiTblStrColBox and MultiTblNumColBox to create the tabulate's table.  These are not something that can be created in JSL by the user.  The next best thing is to utilize the <<Get As Report with some fancy group and name handling to create something similar with TableBox -- which we do have access to in JSL.  Here is a quick function "tabulatish" that I threw together that can take a table that has specified colors on it's cells with any column grouping you may desire and creates something like tabulate.  You do need to specifiy a table and special categorical column within:

Names Default To Here( 1 );
table = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );
theGroup = table << Group Columns( "BP", :BP 8M :: :BP 8W );
theGroup = table << Group Columns( "ELSE", {:BP 12W, :BP 6W} );
For( i = 1, i <= N Cols( table ), i++,
	For Each Row( table,
		Column( table, i ) << Color Cells( Eval List( Repeat( {Random Uniform( 0.9, 1 )}, 3 ) ), Row() )
	);
);

tabulatish = Function( {table, category},
	{Default Local},
	column names = table << Get Column Names( "String" );
	For( i = 1, i <= N Items( column names ), i++,
		If( Uppercase( category ) == Uppercase( column names[i] ),
			category = column names[i];
			Break();
		)
	);
	Eval( Parse( Eval Insert( JSL Quote(
	table << Sort( By( :Name("^category^") ), Order( "Ascending" ), "Replace Table" );
	) ) ) );

	categories = Associative Array( Column( table, category ) ) << Get Keys;
	locs = Repeat( {""}, N Rows( table ) );
	For( i = 1, i <= N Items( categories ), i++,
		locs[(Loc( Column( table, category ) << Get Values, categories[i] ))[1]] = categories[i]
	);
	
	nt = New Table( "temp", <<New Column( category, "Character", <<Set Values( locs ) ), Private );
	olb = Outline Box( "",
		nt << Get As Report
	);
	Close( nt, No Save );
	
	group names = table << Get Column Groups Names();
	groups = {};
	For( i = 1, i <= N Items( group names ), i++,
		Insert Into( groups, table << Get Column Group( group names[i] ) )
	);
	For( i = 1, i <= N Items( groups ), i++,
		groups[i] = Char( groups[i] )
	);
	
	Remove From( column names, Contains( column names, category ) );

	While( 1,
		cols = {};
		Insert Into( cols, column names[1] );
		If( Contains( groups, column names[1] ),
			For( i = 1, i <= N Items( group names ), i++,
				names = table << Get Column Group( group names[i] );
				For( j = 1, j <= N Items( names ), j++,
					names[j] = Char( names[j] )
				);
				If( Contains( names, column names[1] ),
					cols = names;
					Break();
				)
			)
		);
		t = table << Subset(  All Rows, Columns( cols ), Not Linked, Private );
		report = t << Get As Report;
		If( N Items( cols ) == 1,
			olb[Table Box( 1 )] << Append( Try( report[String Col Box( 1 )], report[Number Col Box( 1 )] ) );
		,
			span box = Col Span Box( group names[i] );
			For( i = 1, i <= N Items( cols ), i++,
				span box << Append( Try( report[String Col Box( i )], report[Number Col Box( i )] ) )
			);
			olb[Table Box( 1 )] << Append( span box )
		);
		Close( t, No Save );
		column names = Associative Array( column names );
		column names << Remove( Associative Array( cols ) );
		column names = column names << Get Keys;
		If( N Items( column names ) == 0, Break() );
	);
	olb[Table Box( 1 )] << Set Scrollable( 30, N Cols( table ) + 25 ) << Set Shade Alternate Rows( 0 );
	olb
);

New Window( "Test",
	tabulatish( table, "dose" )
);

Close( table, No Save );

[#3]

This looks very promising!! Thank you! I'll let you know if it works.

Edit: Spent the morning going through your code. It's a great start for me to figure out how to build more custom tables. Thank you! I appreciate the time and effort. Is there a way to add a span box above the columns to an already existing table? For example, let's say I organized the table without the extra categorization. I don't want to go column by column to piece together the table. I just want to pop it up above. Hope that makes sense.

[#4]

I don't think you'd be able to do that very easily.  Due to the hierarchical nature of the display tree you can't simply insert a display box such that objects that weren't its children then become its children.  You could insert a ColSpanBox, then using <<CloneBox you could insert children into the ColSpanBox, but at that point you'd be better off just creating it from scratch

[#5]

That's what I thought but figured I'd ask. Thanks again for the help!