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.
2 Answers:
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)