CODEWITHSUNDEEP
javajasper-reports
Subodh Joshi
Subodh Joshi

Reputation: 13492

How to pass optional parameter to the report?

I am creating JR report with embedded sql query by iReport 4.5.0.

I already added parameter in Query. But I will want to give flexibility so that user can pass parameter or can not pass parameter so that same report can work with my web application.

Can anyone tell me how can make my report that much flexible so that user can or cant pass parameters.

I modified the queryString like this:

SELECT columnA, ColumnB FROM Table WHERE Table."columnA" = $P!{}

But iReport failed to generate report.

Upvotes: 2

Views: 9088

Answers (3)

mdahlman
mdahlman

Reputation: 9390

I usually follow this pattern: Define $P{MyParam} as you did. Then add $P{MyParam_SQL} with a default value like this:

$P{MyParam} == null ? "1=1" : "columnA = '" + $P{MyParam} + "'"

And the SQL in the report is like this: 

SELECT columnA, ColumnB 
FROM table 
WHERE 
  some_filters
  AND $P!{MyParam_SQL}
  AND some_other_stuff

It's not fundamentally different from the other two suggested answers. But I find it easy to understand and maintain like this.

Upvotes: 7

Jacob Schoen
Jacob Schoen

Reputation: 14202

You need to restructure your SQL Query to do this. Essentially have the default value set to null for the parameter and write your query something like:

Select columnA,ColumnB From Table Where ($P!{} IS NULL OR Table."columnA" = $P!{})

So essentially if a value is passed in for the parameter the query will compare it to the column, and if it is null, it will just evaluate to true, and skip the second part.

Upvotes: 1

GenericJon
GenericJon

Reputation: 8986

You could try passing the whole where clause as a parameter. That way, if no parameter was given, the whole clause is omitted and the query will still be valid.

You'd just need to prepare your input something like this:

String whereClause = null
if (inputString != null && inputString != ""){
    whereClause = "Where Table.\"columnA\" = \"" + inputString + "\"";
}

Pass that as a parameter to your report and then change the query to:

Select columnA,ColumnB From Table $P!{whereClause}

Upvotes: 0

Related Questions