iReport - Optional between date parameters

0

Hi all,

I've got a simple SQL query that I'm trying to get optional between date parameters working for. 

At the moment, I've got this working:

SQL-wise it should simply just be:

  1. WHERE START_DATE BETWEEN START_DATE AND END_DATE 

Jaspersoft-wise the input controls need to be optional

  • WHERE (START_DATE >= $P{REPORT_START_DATE} OR $P{REPORT_START_DATE} IS NULL
  • AND START_DATE <=  $P{REPORT_END_DATE} OR $P{REPORT_END_DATE} IS NULL)

Problem is, whilst I'm getting no errors when both the REPORT_START_DATE and REPORT_END_DATE are populated I'm seeing start dates that are greater than the report end date?

Edit: 

Adding AND ($X{BETWEEN, CODE_START_DATE,  $P{1.REPORT_START_DATE}, $P{2.REPORT_END_DATE}}) to the end of the SQL Script gives me an "Unrecognized SQL ecape 'Between'" error. :( 

justin.brown11's picture
Joined: Apr 5 2017 - 6:16am
Last seen: 3 weeks 6 days ago

@Sad H - Can you elaborate? 

Adding "!" Just to the $P's just gives me errors and went breaking it down to just:
(D552M.CODE_START_DATE >= $P!{1.REPORT_START_DATE} AND D552M.CODE_START_DATE <= $P!{2.REPORT_END_DATE})

Doesn't help either..

justin.brown11 - 1 month 18 hours ago

2 Answers:

0

FYI

For anyone who stumbles across this. The fix is relatively simply, Just treat each line seperately.

  • WHERE (START_DATE >= $P{REPORT_START_DATE} OR $P{REPORT_START_DATE} IS NULL)
  • AND (START_DATE <=  $P{REPORT_END_DATE} OR $P{REPORT_END_DATE} IS NULL)

 

justin.brown11's picture
Joined: Apr 5 2017 - 6:16am
Last seen: 3 weeks 6 days ago
-1

$P!{REPORT_START_DATE} now it is handled at report side, do this for all the optional parameters.

Sad H's picture
77
Joined: Sep 8 2020 - 5:58am
Last seen: 3 weeks 4 hours ago
Feedback