Multi Select Query returning only single option (string)

[nubius]

I have almost the exact same issue as posted in :http://community.jaspersoft.com/questions/800974/collection-parameter-xi...

The only difference is my parameter is referencing string datatype not integer.

I also observe that the input control only returns a single input, instead of a list of options as expected from a collection.

For reference, I'm running Jaspersoft Studio (ce) 6.3. and my Jasper Server environment is Pro version 6.1.1.

my WHERE clause is
$X{IN, ActivityLabel, msactlab}

parameter name "msactlab" is setup as a multi select query on server.

I've specified class as "java.util.Collection"
and nested type as "java.lang.String"

The input control dialog box shows up when I run the report in Server.
But just like the post referenced above, I only see an single option, not the full list I would expect.

If this helps, some of the entries in the column "ActivityLabel" are null, so please let me know if I need to try an alternate syntax.

--> I tried searching the community site before posting this.

I also looked at this post for help: http://community.jaspersoft.com/questions/1019936/solved-dynamic-sql-sta...

No luck so far.

Any and all assistance would be greatly appreciated.

[Friendly User]

is the issue on report level or input control level? If it is report, check if collection parameter correctly populated? Please try to create a field somewhere in report title with expression $P{myCollectionP}.toString() to see the contents of that array. Maybe the issue is on that level.

If the input control is having trouble returning you a list of available selections then most likely the problem is on the input control query level. Report parameters should not have any effect on the execution of these queries.

[nubius]

Thanks for the suggestions Friendly User. (nice doge btw ^_^)

I tired what you suggested, and didn't get anything back.

On my report title band, I created a text field (not a static text field) with this expression:  $P{msactlab}.toString()

When I went to run the report on jasper server, the only thing in the field were a couple of brackets with no text inbetween. like so... []

 

So I'll give you some more background if it will help.  As I stated above, I created the parameter in jaspersoft studio called "msactlab"

I set the class as java.util.Collection, and I even specifed the nested type name as a java.lang.String.

I've tried running this input control both with and without specifiying the nested type name, but observed no difference in behavior or outputs.

 

Now I'll go over my settings on the Jasper Server input contorl settings:

When I created the input control I selected its type as "Multi-select query"

I ensured that the parameter name matched msactlab (yes, all lower case just like the parameter in the jrxml report.)

I defined my query in SQL, and yes I linked it to the approprate data source for this field/table.

The query I defined is very simple as this is a proof of concept:  SELECT ActivityLabel FROM dbo.dimActivity

For the sake of argument, I've also tested SELECT DISTINCT ActivityLabel FROM dbo.dimActivity with no observed change in behavior or outputs.

At the set parameters values screen, I set visible colums to ActivityLabel only, as that's the only field I wanted to make selections from.

From all of the online reading, and tutorial videos I've seen this should be a fairly routine syntax.

WHERE $X{IN,FieldName,parametername}

Create input control/query in jasper server, and ensure parametername matches the parameter defined in the Jasper report JRXML.

I'm usually pretty good at following documentation, but I'm coming up empty this time.

For example I found this on sourceforge which is usally a definiative resource for me, http://jasperreports.sourceforge.net/sample.reference/query/

I'll echo the "Built-in SQL Clause Functions" section below...

***********************************

1) The $X{IN, <column_name>, <parameter_name>} clause function 
 

 

The function expects three mandatory clause tokens:

The first token represents the function ID and always takes the fixed value IN.

The second token is the SQL column (or column combination) to be used in the clause.

The third token is the name of the report parameter that contains the value list. The value of this parameter has to be an array, a java.util.Collection or null.

 

If the parameter's value is a collection of not null values, the function constructs a <column_name> IN (?, ?, .., ?) clause 

If the parameter's value is a collection containing both null and not null values, the function constructs a (<column_name> IS NULL OR <column_name> IN (?, ?, .., ?)) clause 

If the parameter's value is a collection containing only null values, the function constructs a <column_name> IS NULL clause 

If the parameter's value is null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 

***********************************

Per the section I hilighted above, I took that to mean if I there are any null values in the field referenced, the function will still return a valid list of selections.  So far, I'm only getting one item returned, which is not a complete list.  I can access the field in SQL Server and see that there are about 8 unique items that a user should be able to select from.  

My goal is to have a multi-select dialog box appear that will alow my users to select one or more "Activity Labels" and click apply or okay from the input control before the report runs.

I don't care how complicated this has to be, as long as it works, though I would prefer an elegant solution like the $X syntax above seems to be.  

Thanks in advance for your help, and please let me know if I can provide any further information.

 

[mike.daniels_1]

Did you ever resolve this? I am having same issue even as today?