topic Re: How do I add prompt boxes for parameters when writing custom SQL expressions in Discussions

How do I add prompt boxes for parameters when writing custom SQL expressions

Joeystringer — Tue, 06 Mar 2018 03:19:36 GMT

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

Joeystringer — Tue, 06 Mar 2018 03:24:35 GMT

I use ssrs a lot and am used to having parameters like @Sitename or @Date. I have several queries I use in other platforms, but I like to be able to put them in JMP. the issue I run into is that I don't know how to use the prompt box for my parameters when I do the custom sql. For example, how would I write this so that a box would appear asking for "Lineid"? they would be able to respond, and the query would run.

SELECT *

FROM tblline

where tblline.lineid=@LineID;

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

cwillden — Tue, 06 Mar 2018 15:59:48 GMT

Hi @Joeystringer,

Here's an example of something you can run before initiating the query:

dlg = New Window("Parameter Specification", << Modal,
	V List Box(
		Text Box("Please specify the value of lineID."),
		H List Box(
			Text Box("LineID: "),
			neb = Number Edit Box();
		),
		Button Box("OK", LineID = neb << Get);
	)
);

Show(LineID);

This window just asks the user to enter a number into the number edit box. The "OK" button gets the value in the box and saves it as "lineID" which you can use in your query. The "<< Modal" option makes the window modal, which means the JMP will not execute the rest of the script that comes after the window until the user presses "OK".

I highly recommend you check out the Scripting Guide (Help > Books > Scripting Guide) to learn how to script dialogs if you want to do anything more complicated

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

Jeff_Perkinson — Tue, 06 Mar 2018 19:36:55 GMT

If you use the Query Builder it will build the prompts for you automatically using the "Prompt on Run" option in the filters area.

When you run the query the user will be prompted. No reason to build it yourself.

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

Joeystringer — Tue, 06 Mar 2018 19:41:35 GMT

Yes. when I build the queries directly in JMP, I can get the prompts. My problem I have is when I take an existing query and put into the custom sql query, my prompts aren't available. For a simple query, the query builder in JMP works, but I have some fairly lengthy and complicated joins that I'm trying to not have to rebuild.

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

Joeystringer — Tue, 06 Mar 2018 19:42:41 GMT

Can you show me this with the query included? I have tried to add the query, but when I put this in, I'm not sure what to put for the variable in the "Where" statement of the query.

Re: How do I add prompt boxes for parameters when writing custom SQL expressions

Jeff_Perkinson — Wed, 07 Mar 2018 22:48:30 GMT

I'm not sure I understand exactly what you'd like to see but here's the JSL generated from a query like I showed above.

Notice the Where() portion. It's pure JSL. There are no variables that you could stick in your Custom SQL.

It looks like your choices for effort are:

Writing the JSL to build the prompts and figuring out how to insert the responses into your SQL; or,
Converting the query to Query Builder and letting it prompt for you.

I'm not sure which is more effort for you but converting to Query Builder seems more flexible and will give you more options in the long run.

New SQL Query(
	Version( 130 ),
	Connection(
		connection_string
	),
	QueryName( "inventory" ),
	Select(
		Column( "warehouseid", "t1" ),
		Column( "sku", "t1" ),
		Column( "sku_description", "t1" ),
		Column( "quantityonhand", "t1" ),
		Column( "quantityonorder", "t1" )
	),
	From(
		Table( "inventory", Schema( "Sporting Goods By Stan" ), Alias( "t1" ) ),
		Table(
			"warehouse",
			Schema( "Sporting Goods By Stan" ),
			Alias( "t9" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "warehouseid", "t9" ), Column( "warehouseid", "t1" ) )
			)
		),
		Table(
			"order_item",
			Schema( "Sporting Goods By Stan" ),
			Alias( "t6" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "sku", "t6" ), Column( "sku", "t1" ) )
			)
		),
		Table(
			"sku_data",
			Schema( "Sporting Goods By Stan" ),
			Alias( "t8" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "sku", "t8" ), Column( "sku", "t1" ) )
			)
		),
		Table(
			"retail_order",
			Schema( "Sporting Goods By Stan" ),
			Alias( "t7" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "ordernumber", "t7" ), Column( "ordernumber", "t6" ) )
			)
		)
	),
	Where(
		In List(
			Column( "department", "t8" ),
			{"Water Sports"},
			UI(
				SelectListFilter(
					ListBox,
					Base(
						"Categorical",
						Prompt(
							"Select a department:",
							Character,
							PromptName( "t8.department_1" ),
							ValueSource(
								Column(
									"department",
									Alias( "department" ),
									Table(
										"sku_data",
										Schema( "Sporting Goods By Stan" )
									)
								)
							)
						)
					)
				)
			)
		)
	)
) << Run;