2

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.

Alex K
  • 22,315
  • 19
  • 108
  • 236
Subodh Joshi
  • 12,717
  • 29
  • 108
  • 202
  • You can look at [passing a "where condition" to jasperreports exporting pdf](http://stackoverflow.com/questions/12081601/passing-a-where-condition-to-jasperreports-exporting-pdf), [JasperReports: Passing parameters to query](http://stackoverflow.com/q/11871042/876298) & [JasperReports: How to add a WHERE statement in SQL query depending on a Boolean parameter?](http://stackoverflow.com/q/12889023/876298) posts – Alex K Oct 26 '12 at 19:04

3 Answers3

7

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.

mdahlman
  • 9,204
  • 4
  • 44
  • 72
  • Thanks for your reply. But it can be performance issue.can it be possible we can ignore empty argument from the where clause in Jasper report – Subodh Joshi Oct 27 '12 at 12:58
  • What makes you think it could be a performance issue? You could certainly convert it to an empty string instead of '1=1'. You just need to be more careful about generating the 'AND ' keyword where you need it. But performance will be identical. – mdahlman Oct 27 '12 at 14:34
  • Let us suppose if user giving any argument empty then we will go for '1=1' which will fetch all record for that particular column. Is is not? – Subodh Joshi Oct 27 '12 at 15:24
  • Yes. If you do pass a value, then columnA will filter on that column. If you do not pass a value, then columnA will have no filter. That's your requirement, right? – mdahlman Oct 28 '12 at 04:44
  • Thanks can it be possible if user not giving we can ignore that individual where statement? – Subodh Joshi Oct 29 '12 at 04:26
  • Yes. That's what this sample does. – mdahlman Oct 29 '12 at 18:14
1

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.

Jacob Schoen
  • 14,034
  • 15
  • 82
  • 102
0

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}
GenericJon
  • 8,746
  • 4
  • 39
  • 50
  • Thanks GenericJon for your reply but this approach will not work for me because i can not tell user to give the where clause to me – Subodh Joshi Oct 26 '12 at 11:36