Start date and end date parameters passing??

[sawandj]

hi all,

i am stuck in this problem from 2 days.. can any one help me on this..

i have create a report with two parameters $P{start} and $P{end} for start date and end date.

when i enter bot the dates it works.. when i do not enter any date or dont fill any 1 of the the.. the report will not work..

displays no "DOCUMENTS HAS EMPTY PAGES"

i tried to use this query but still it does not work..  the query is used+

  select  *  from tblTimeSheet

where TSSubmitDate >=($P{start} or $P{start} is null)

             and TSSubmitDate <=($P{end} or $P{end} is null) order by TSSubmitDate;

i even tried out the query select  *  from tblTimeSheet where $P!{date}; where $P{date} is a 3rd token in which i have edited the expression with

$P{start} == null || $P{end} == null ? "0=0" : "TSSubmitDate between $P{start} and $P{end}

but reports are not properly getting generated..

can any 1 help me on this.. its urgent

thanks..

[mrcaffeine]

can you post the relevant parts of the JRXML (parameter definition and query)?

[cbarlow3]

I have three possible solutions for you:

1. I think your first approach is essentially right except that you have parens in a strange place.  Should read:  where ($P{start} is null or TSSubmitDate>=$P{start}) and ($P{end} is null or TSSubmitDate<=$P{end}) and...

2. Or you could use COALESCE() (or the equivalent in your language, if you don't mind hardcoding standard dates that are earlier and later than will ever be stored in your database, such as     where TSSubmitDate between COALESCE($P{start}, '1800-01-01') and COALESCE($P{end}, '2999-12-31')

3. If your flavor of SQL supports common table expressions (CTEs--uses keyword "WITH") and you're going to want to interpret blank entries as some sort of calculated default value that you might use repeatedly in your query, you could do something like this:

WITH DefaultDates (StartDate, EndDate) AS
(
SELECT
  COALESCE($P{StartDate}, x.EndDate - DAY(x.EndDate) Days + 1 Days) AS STARTDATE,
  x.EndDate AS ENDDATE
FROM
(
SELECT
  CASE
    WHEN $P{EndDate} IS NOT NULL THEN $P{EndDate}
    WHEN DAY(ENV.POSTING_DATE)>10 THEN ENV.POSTING_DATE
    ELSE ENV.POSTING_DATE - DAY(ENV.POSTING_DATE) Days
  END AS EndDate
FROM
  CORE.ENV AS ENV
) x
)

SELECT
  ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
  COALESCE(PERSON.LAST_NAME, '') || COALESCE(',' || PERSON.FIRST_NAME, '') AS PERSON_LAST_FIRST
FROM
  CORE.ACCOUNT AS ACCOUNT INNER JOIN
  CORE.PERSON AS PERSON ON
    ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL INNER JOIN
  DefaultDates ON
    DefaultDates.StartDate IS NOT NULL
WHERE
  ACCOUNT.OPEN_DATE BETWEEN DefaultDates.StartDate AND DefaultDates.EndDate

In this example, I convert their entered $P{StartDate} and $P{EndDate} into reusable fields DefaultDates.StartDate and DefaultDates.EndDate that I know will not be null.  Furthermore, I've done some calculations so that if both inputs are null, the date range is either the entire month to date or (if the current date--represented in our database by ENV.POSTING_DATE--is in the first ten days of the month) the entire previous month.

Hope these examples help out.

Carl

[butlerc]

Using the token is the way to go on this.

I created a Jrxml file for you, this works in my oracle database.  You can massage the dates into whatever pattern you want.

copy the below into the xml of a blank report.

 

[butlerc]

<?xml version="1.0" encoding="UTF-8"?>

$P{StartDate} == null || $P{EndDate} == null ? "AND 0=0" :</p><p> "AND createdate between '" + new SimpleDateFormat("dd-MMM-yyyy").format( $P{StartDate}) + "' and ' " + new SimpleDateFormat("dd-MMM-yyyy").format( $P{EndDate} ) +"'"

select createdate from KPI.CB_REGISTRATION_MV_EC</p><p> where 0=0</p><p> $P!{DateToken}

CREATEDATE

$F{CREATEDATE}