Can I choose the where clause depends of a parameter?

[carolopia]

Hi everyone,

I have a report where the dataset comes from a SQL but I need to choose the where clause from a parameter.
For example if the user choose in the parameter "ALL" then the where clause is " Where fieldA = 'X' " but if the user choose in the parameter "B" then the where is "where fieldB = 'Y'"

How can I do this? Is posible?

thanks.

Regards.

Caro.

[jpauze]

Yes this is possible,  there are two ways to use parameters embedded within SQL statements.

Example:  SELECT col1, col2, col3 FROM table1 WHERE col1= 2

First option,  let's say you want to substitute the value 2 in the where clause

create a parameter and assign value 2, for instance parameter named pValue set to 2, the statement would then be changed to the following:

SELECT col1, col2, col3 FROM table1 WHERE col1= $P{pValue} 

This would then dynamically use the value in the parameter to match in the where statement.

More specific to your question,  let's say you wanted to build a dynamic where statement, there is another mechanism which instead of doing parameter substitution it wil actually parse the string before exection.

Create parameter pWhere (or whatever you want) and for the expression set to "WHERE col1 = 2" or whatever you want your where statement to be and then the change the statement to:

SELECT col1, col2, col3 FROM table1 $P!{pWhere} 

The $P! will parse the string into SQL statement before exectuting, this is how you woudl get what you describe, having different where conditions exectued from parameters.

[carolopia]

Thanks, It works for me!!! /tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

[shazone]

carolopia
Wrote:

Thanks, It works for me!!! /tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

i cant seem to do this ... im using ireport 4.0.2 ... what im missing??  :(

Compilation exceptions: com.jaspersoft.ireport.designer.compiler.ErrorsCollector@a9ca97  net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: calculator_report1_1308654826146_929587: 129:  [1] is a constant expression, but it should be a variable expression at line: 129 column: 41. File: calculator_report1_1308654826146_929587 @ line 129, column 41. 1 error      at net.sf.jasperreports.compilers.JRGroovyCompiler.compileUnits(JRGroovyCompiler.java:101)     at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:188)     at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:215)     at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:131)     at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:515)     at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)     at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: calculator_report1_1308654826146_929587: 129:  [1] is a constant expression, but it should be a variable expression at line: 129 column: 41. File: calculator_report1_1308654826146_929587 @ line 129, column 41. 1 error      at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:296)     at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:829)     at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:511)     at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:487)     at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:464)     at net.sf.jasperreports.compilers.JRGroovyCompiler.compileUnits(JRGroovyCompiler.java:97)     ... 6 more

Compilation running time: 250!

 

[shazone]

sorry please ignore .. its working now... it doesnt work if i set the default value ... i need to leave it empty then it is working .. hrmmmm ..