How to change the sort order of values in a local data filter?

[#1]

Hi All,

I'm using JMP 18 and have a JMP file that uses a custom SQL query to create a data table. There are several dashboards (composed of graphs created in Graph Builder) attached to the data table. Some of the dashboards contain a local data filter for users to control select rows should be displayed in the associated graphs.

I want to change the sort order of the values in the local data filter. Since the sorting of the underlying data table is not an issue, I tried using the first suggestion in @txnelson 's reply to this posting (edited code below). This sorts the data table as expected, but the sort order of the Batch/Reactor column does not get applied to the local data filter (it remains in the original sort order, which appears to be alphabetical).

The Batch/Reactor column is character/nominal.

Does anyone have feedback on why this code isn't working as expected, or on other approaches I could take?

Thanks,

Andy

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:..."
	),
	QueryName( "Custom Query" ),
	CustomSQL(
		"		select ...
[... SQL omitted ...]
		group by
			up_pro_name) as dcw on
	dcw.up_pro_name = sample.dn_mat_name
"
	),
	PostQueryScript(
		"dt = Current Data Table();
dt << Sort( By( :Name(\!"Batch/Reactor\!"),:Name(\!"EFT (h)\!") ), Order( Descending, Ascending ), Replace table );
dt:Batch/Reactor << set property( \!"value order\!", {Common Order( 0 ), Row Order Levels( 1 )} );
"
	)
) << Run Foreground( UpdateTable( Current Data Table() ) );

[#2]

Try setting the Value Ordering column property for the columns being used in the data filters.

[#3]

Hi Jim,

I did the following in the UI:

1. right-click column header

2. Column Properties -> Value Order

3. click Custom Order and click Reverse

This worked for the current result set in the SQL-generated data table (the sort order persisted in the data filters of the attached dashboards). However, when new results appear in the SQL, those results appear at the bottom of the result set (and the bottom of the dashboard data filters).

Is there a way to specify Value Ordering as a property in the JSL so that it's applied dynamically to the current result set?

Thanks,

Andy

[#4]

Here is a simple example that sets the Value Order for the column named Sex in the Big Class data table.

Names Default To Here( 1 );

dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/JMP/JMPPRO/19/Samples/Data/Big Class.jmp" );

dt:sex << set property( "Value Order", {Custom Order( {"M", "F"} ), Common Order( 0 )} );

The syntax you need to use to set a Column Property can always be obtained by interactively setting the property in question, and then run this JSL and the syntax will be displayed in the JMP log

dt:sex << get property( "Value Order" );

[#5 · Accepted solution]

For anyone who's interested, here's the code I used to dynamically set the Value Order each time the SQL query is executed. This persisted the desired sort order to the local data filters in the dashboards attached to the data table, which was my goal.

	[...rest of SQL query...]

	order by \!"Batch/Reactor\!" desc, \!"EFT (h)\!" asc 
	
"
	),
	PostQueryScript(
		"dt = Current Data Table();
		br_values = column(\!"Batch/Reactor\!") << GetAsMatrix();
		br_values_sorted = Sort Descending ( br_values );
		br_values_sorted_list = as list (br_values_sorted);
		dt:\!"Batch/Reactor\!" << Set Property(\!"Value Ordering\!",Eval List(br_values_sorted_list));
		dt << sort( by(\!"Batch/Reactor\!"), replace table(1));"
	)
) << Run Foreground( UpdateTable( Current Data Table() ) );

[#6]

I think Batch/Reactor isn't valid column name if you are using it like that. Try changing it to :"Batch/Reacher"n. You also might want to add wait(0) after the sort just to be sure.

Below is an example using JMP example table and On Run Complete instead of Post Query Script

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

Column(dt, 1) << Set NAme("Test/ABC");

dtquery = New SQL Query(
	Version(130),
	Connection("JMP"),
	JMP Tables(
		["Big Class" => "\C:\Program Files\JMP\JMPPRO\18\Samples\Data\Big Class.jmp"
		]
	),
	QueryName("SQLQuery1"),
	Select(
		Column("Test/ABC", "t1"),
		Column(
			"age",
			"t1",
			Analysis Type("Ordinal"),
			Numeric Format("Fixed Dec", "0", "NO", "")
		),
		Column("sex", "t1"),
		Column("height", "t1", Numeric Format("Fixed Dec", "0", "NO", ""))
	),
	From(Table("Big Class", Alias("t1"))),
	
) << Run Foreground(
	On Run Complete(
		queryResult << Sort(By(:height), Replace Table, Order(Ascending));
		wait(0);
		queryResult:"Test/ABC"n << Set Property(
			"Value Order",
			{Common Order(0), Numerical Order(0), Row Order Levels(1)}
		);
	)
);