Parameters as SQL list with iReport

[schone]

Hi all,

I just got started using JasperReports and I'm intially stuck on this parameter problem.

I have a report which is just a list of cities and the country they belong to.

I would like to create a list/dropdown parameter so that when the report is run a unique list of all countries available populate the list/dropdown and the user can select to filter the report on the selected country.

SELECT * FROM LOCATIONS WHERE COUNTRY_NAME = $P{COUNTRY_LIST}[/code]

I have set up a parameter called COUNTRY_LIST but when I run the report nothing appears in the drop down list, I also included this query in the default parameter value area:

SELECT DISTINCT(COUNTRY_NAME) FROM LOCATIONS[/code]

SELECT DISTINCT(COUNTRY_NAME) FROM LOCATIONS

And all I get is a incorrect token errror.

Can this be done in JasperReports?

Please help!

Thanks in advance!

[pmurali26]

Hi did u get the solution to ur problem.. can u please help me with the solution (if ur problem is rectified)... am in urgent need of that..

Thanks in advance

[mdahlman]

It's not possible to define an input control with a list of countries in iReport or in JasperReports. It doesn't apply to these because JR is a report library and iReport is a report designer.

 

JasperServer can do exactly what you're describing. It has the concept of an "input control" the input control includes a query to get a list of values to be displayed to the user. Download that and take a look at the sample reports. Post to the JasperServer forum if you have questions implementing your input control.

Regards,
Matt
Jaspersoft

[sdemills]

Am I right in thinking that it is possible to define Report Parameters in iReport and then populate those parameters from a Java program which then subsequently compiles and executes the report?

Kind Regards
Steve

 

[sdemills]

I can answer partly my own question: yes it's easy to add parameters and then populate them from a Java program.

 

What I need to know now is the correct syntax for embedding them into the SQL Query within the iReport designer please.

[pranjalihapase]

can u please tell me how u add input control list/drop down to the project.

thank you.

[pranjalihapase]

install new jasper version of 3.5.0.

add a parameter.

try to add input control that name should match with the parameter name.

[aalexkun]

In iReport 3.0.0 I did SQL injection and it work.

I use zuckerReport, a php component that use a small Java class to pass the parameter from php to java and Jasper. So I guess you can do the same like that

 

My iReport where clause look like that

WHERE Q.contract_date BETWEEN $P{FROM} AND $P{TO} and

'' = $P{REP_LIST} = '' AND

Q.quote_stage = 'Confirmed' AND NOT Q.Deleted and p.quantity > 0

 

The parameter REP_LIST is use for the injection.

' AND Q.Assigned_user_id in ('','id1','id2','id3','id4') AND '

(iReport will circle the param with the other apostrophes)

So, at the end, we got something like that

WHERE Q.contract_date BETWEEN 'date1' AND 'date2' and

'' = '' AND Q.Assigned_user_id in ('','id1','id2','id3','id4') AND '" = '' AND

Q.quote_stage = 'Confirmed' AND NOT Q.Deleted and p.quantity > 0

 

Of course, you only can run it using some code, because the ireport GUI will parse an error on this param input.

 

Post Edited by aalexkun at 09/16/2010 17:00

[bigteddo]

 This just works .

all u need to do is select control type as single select query and banm.

thank you so much

[ibradien]

Hi aalexkun and bigteddo,

 

I have a similar problem, but when I try to apply that idea ireport displays an error warning.

>> first trial :  Error:org.postgresql.util.PSQLException: ERROR: invalid input syntax for type boolean: ''''

>> second trial : Error:org.postgresql.util.PSQLException: The column index is out of range: 18, number of columns: 17.

Here I am using iReport Designer version 5.1.0 and PostgreSQL 9.1.

I have tried to execute my query using pgAdmin III and produce the expected records. (but it is not successful in iReport)

anyone can help find the solution?

 

Thank you