How to manage a LOV using $X parameter in input control

[marco.gambardella]

Hi,

I'm working with Jasper Studio 6.2 and JasperReports Server Community Project 6.1.1.

I want to present to the user a list of values selecting   from a  field of an Oracle table, this field (DATA_RIFERIMENTO) is a varchar2(10) containing a very simple values in this format DD/MM/YYYY.

In my report I defined a query with this condition: WHERE  $X{IN,DATA_RIFERIMENTO,DATA_INPUT} AND DATA_RIFERIMENTO IS NOT NULL;

DATA_INPUT, the parameter name use the  java.util.Collection  and prompt = true;

I defined on JasperServer an input control as the same name DATA_INPUT with this query: SELECT DISTINCT .DATA_RIFERIMENTO FROM FACT_TABLE  WHERE FACT_TABLE.DATA_RIFERIMENTO IS NOT NULL ORDER BY FACT_TABLE.DATA_RIFERIMENTO DESC.

I deployed the report on the server and related local report paramete to input control on the server but when the report run the LOV contains only - this value '- - -' and query of the report run with 0 = 0 where condition but I have the significant values in DATA_RIFERIMENTO field.

If I  can try to chose a diffrent empty row in the list of value obtain this error:

"Invalid type java.lang.String for parameter PARAMETRO used in an IN clause; the value must be an array or a collection."

But DATA_INPUT parameter is already defined  in the report with java.util.Collection class!

 

 

Can you help me

 

Many thanks

 

Marco

[mwarner_2]

Not sure I understand exactly what you are doing.

$X{} is used in a server input control to take the values from another server multi-select input control.

It sounds like you are using the $X{} in your report's main data source.  I have not ever used it there. Not sure you even need $X{}.

I am thinking that your data source sql would be like ...Where DATA_RIFERIMENTO in (select LTRIM(RTRIM(Item)) from util.split_string_table_nvarchar($P{DATA_INPUT_STRING}, ',')).

DATA_INPUT_STRING is an unprompted parameter, with default expression of $P{DATA_INPUT}.toString().replace("[","").replace("]","")

util.split_string_table_nvarchar is SQL function that you can probably google "split string table' and find lots of examples of. 

----

I usually use stored procedures in my main data sql and in this case I would pass $P{DATA_INPUT_STRING} to a parameter, say @DATA, in the stored procedure, and in the stored procedure it would do the DATA_RIFERIMENTO in (select LTRIM(RTRIM(Item)) from util.split_string_table_nvarchar(@DATA, ','))

And my main data source would just have something like: EXEC STORED_PROCEDURE_SPECIFC_NAME @DATA = $P{DATA_INPUT_STRING}

Hope I have been of any help.

 

[marco.gambardella]

Many many many thanks,

the report run correctly,

my error was not click to add visible columns, I assumed,in input control definition, an implicit visualitation of

DATA_RIFERIMENTO, I worked with Business Objetcs and Microstrategy in wich isn't necessary tedefine it.

So not in my mind to declare filter visualitation, I will use a different approach with Jasper.

Again many thanks

Marco

[mwarner_2]

Create a date parameter in your main report.  Let's name it "Select_Date".

Export the report to the server.

In JS Server, under input controls, create an input control with a Parameter name of"Select_Date".  Make it a single select query.  In the input control's Define the Query, you make a query like "Select DATA_RIFERIMENTO from Oracle_table_exact_name".  For both the Value Column and the Visible Column enter DATA_RIFERIMENTO.  Don't forget to press Add after entering the Visible Column.

In JS Server, find and edit your imported report, specifically edit the input controls.  Click on Controls and Resources.  Click on the "Select_Date" control.  Select an Input Control for the Repository.  Choose your JS Server input control called "Select_Date".  Save the changes to the report. 

When you run the report, the JS Server input control runs, and its "Select DATA_RIFERIMENTO from Oracle_table_exact_name" SQL runs and fetches the dates, and displays them in the Select_Date report parameter.