Parameters in Jasper Report Server are not being taken.

Let me explain. I'm trying to connect my application to Jasper's reporting server. I have only one report for now, because I'm still testing. I'm working on it together with Jaspersoft Studio. So far the way of creating reports with Jasper has seemed super pleasant, and the feature that comes with the REST API to integrate the jasper viewer with my application is great. Right now I would like you to help me with something strange that is happening to me:

I'm trying to create a dynamic report, with the simple condition that if the user does not send any information in any field of the filter, the query should display everything for that column. This is what I have so far:

SELECT .... COLUMNS
FROM .... TABLE WITH JOINS
WHERE P."CR_NUMBER" LIKE '%$P!{crNumber}%'
    AND P."NAME" LIKE '%$P!{partyName}%'
    AND D."PROV_DEC_REF_NUMBER" LIKE '%$P!{provRef}%'
    AND D."DEC_REF_NUMBER" LIKE '%$P!{reference}%'
    AND D."VERSION_RECT" LIKE '%P!{version}%'

When I run the report with some information in any parameter (crNumber for example) calling it from the url ( http://localhost:8080/jasperserver/flow.html_flowId=viewReportFlow&_flow... ) I expect to have a query like this:

SELECT .... COLUMNS
FROM .... TABLE WITH JOINS
WHERE P."CR_NUMBER" LIKE '%PA212%'
    AND P."NAME" LIKE '%%'
    AND D."PROV_DEC_REF_NUMBER" LIKE '%%'
    AND D."DEC_REF_NUMBER" LIKE '%%'
    AND D."VERSION_RECT" LIKE '%%'

This is how I have the defaultValueExpression for this parameter:

    <parameter name="crNumber" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{crNumber} == null || $P{crNumber}.isEmpty() ?  "" : $P{crNumber}]]></defaultValueExpression>
    </parameter>

Now when I call the Jasper Report Server URL and the viewer is displayed, the report is simply not taking the values that you pass in the URL (or is it ignoring it?) Previously, the query had it structured differently. Everything was being done with an additional parameter that stored all the where conditions. I think that previously it was a bit difficult for me to control this parameter since there are quite a few where conditions, so I decide to change it. As I had before it works but for some unknown reason, it stopped working at times. Every time when I checked the logs, the error it threw was "Invalid relational operator" (another reason why I decided to change how I prepared it dynamically).

Any suggestion about how I can overcome this problem?

P. D. : The report is working great when I preview it in Jasper Studio.

danielsl9.dp's picture
Joined: May 10 2021 - 8:35am
Last seen: 1 year 11 months ago

2 Answers:

Hi, i can't seem to see the paramaeter in your sql query ? you should add it in order to be able to filter the data 

yassinswag50's picture
Joined: Jul 20 2019 - 1:52am
Last seen: 2 years 2 months ago

Hi, You can try like below so you can able to generate the report with dynamic and optional data.

SELECT .... COLUMNS
FROM .... TABLE WITH JOINS
WHERE (P."CR_NUMBER" LIKE '%$P!{crNumber}%' or $P!{crNumber} is null)
    AND (P."NAME" LIKE '%$P!{partyName}%' or $P!{partyName} is null)
    AND (D."PROV_DEC_REF_NUMBER" LIKE '%$P!{provRef}%' or $P!{provRef} is null)
    AND (D."DEC_REF_NUMBER" LIKE '%$P!{reference}%' or $P!{reference} is null)
    AND (D."VERSION_RECT" LIKE '%P!{version}%' or P!{version} is null)

surima.tvs3's picture
Joined: Jun 13 2019 - 11:43pm
Last seen: 5 months 2 weeks ago
Feedback