how to use BETWEEN clause in jasperserver report?

[HBK32]

Hello,

Can anyone tell me how to use BETWEEN clause in where clause of JasperServer report?

e.g. there are two input(textbox) control to enter value which is NOT manadatory means its optional.Suppose user enter 10 in one textbox & 500 in second textbox then it should come as COLUMN_NAME BETWEEN 10 AND 500.

If user don't enter anything then it should replace BETWEEN clause with 1=1

I will greatly appreciate your response.

Thanks.

 

[swood]

You can create a set of parameters that do this.

   <parameter name="parameter1" class="java.lang.String"/>
   <parameter name="parameter2" class="java.lang.String"/>
    <parameter name="queryExpression" class="java.lang.String">

        <defaultExpression>if both parameters entered, generate "columnName between ${parameter1} and ${parameter2}

else

generate "1=1"

</defaultExpression>

    </parameter>

<queryString>

...

where $P!{queryExpression}

</queryString>

 

Sherman

Jaspersoft

[HBK32]

swood
Wrote:
 

You can create a set of parameters that do this.    <parameter name="parameter1" class="java.lang.String"/>    <parameter name="parameter2" class="java.lang.String"/>     <parameter name="queryExpression" class="java.lang.String">         <defaultExpression>if both parameters entered, generate "columnName between ${parameter1} and ${parameter2} else generate "1=1" </defaultExpression>     </parameter> <queryString> ... where $P!{queryExpression} </queryString>   Sherman Jaspersoft

Hello Sherman,

Thanks a lot for the response.

Please correct me if understand wrong here.

Let me know following right or wrong. If its wrong can you plz correct it ?

<parameter name="param1" class="java.lang.String"/>
<parameter name="param2" class="java.lang.String"/>
<parameter name="queryExpression" class="java.lang.String">

// following defaultExpression is for the parameter queryExpression

<defaultExpression>

        ($P{param1} != null && $P{param}!=null) ? "Column_Name BETWEEN $P{param1} AND {param2}" : " 1 = 1 "

</defaultExpression>

What is the meaning of generate(i.e. generate "1=1") in your code ?

I will greatly appreciate your response.

Thanks.

[swood]

With an default expression like that, you can have a <queryString> like:

 

select col1, col2, col3 from table where $P!{queryExpression}

 

1=1 is essentially saying no select.

 

 

Sherman

Jaspersoft

[prasad23]

Hi,

    i am new to tyhe jasper server,can anybody help me ragarding this error while saving adhoc report using domain 

i am getting the following error

Illegal XML character: 0x1f

org.apache.xmlbeans.impl.piccolo.io.IllegalCharException: Illegal XML character: 0x1f

Is there anybody help me to solve the problem,very urgent

     Thanks in advance.