Dependent Input Control and Stored Procedure

[mwarner_2]

Can this be done?  If so, how?  If I can't do it as I want to, then how can I do it?

 

I want to have a server-stored multi-select input control, say for city.  The SQL to fill the control is a stored procedure (with an input parameter for state).

When the user chooses their cities, then I want those cities to be passed (and used) in the Main Report, which is another stored procedure, with city as an input parameter.

 

I have seen the $X method, but in order to use that, I have to have and use straight SQL, in $X(IN ...

 

Thanks

[zh3ntil]

For multi select report, using store procedure is little bit complicated comparing to single select. Because for multi select control, the parameter type should be a list or collection. If you send your list parameter to store procedure as $P{param},ıt sends something like 'USA','Germany','Brazil',... You should parse this string in the SP or use in SP like countryName IN(@param).

You can add your parameter to report to see the output.

 

[mwarner_2]

I have found my own answer.  Maybe this will help you as well.

Maybe most importantly, is that JS server input controls are not a separate, different animal, but rather are exactly the same as if they had been created (if they could be created) in JS Studio.

 

The JS server input control for a multi-select query, creates a collection.  These collections are enclosed by square brackets, have a comma and a space in between items in the collection. Like this [item1, item2, item3].  Unless the stored procedure has been written to strip out the square brackets and the spaces, the SQL might not run correctly.  Additionally, but of course, the stored procedure must be written so as to allow multiple values to be passed to its input parameter (for the parameter that is to have multiple values).

 

What I do in JS Studio is to create a prompting parameter, and define it as a collection.  That name for this parameter must be exactly the same as JS server input control internal name.  Let's call this Cost_Code.

 

I then create a non-prompting parameter, and define it as a string.   Let's call it Cost_Code_String.  It has a Default Value Expression of $P{Cost_Code}.toString().replace("[","").replace("]","").  This turns the collection into a string and removes the square brackets.

 

Next, in the data source (of the Main Report), my Dataset sql query is like below, where GETDATA is the name of the stored procedure, and @Cost_Code is the name of its input parameter.
EXEC GETDATA @Cost_Code=$P{Cost_Code_String}

 

When the report is exported to JS server, the Cost_Code prompting parameter, must be changed in Repository, [Report], edit, Controls & Resources, Cost_Code so as to point it at the server input control (something like this: /public/Controls/Cost_Code).

 

Hope this helps.  It shouldn't have been this hard for me, but I somehow thought that JS server input controls were different animals than JS Studio prompting parameters, and that I had to treat them differently.