Using $V variables in query

[CaPsLoCkEr]

Hello

 

I want to use a $V variable in my query. As i have already found out this does not work. So I created a parameter that refers to the variable but unfortunately my output is null. 

This is how i'am trying to create my query:

I have 2 variables: 1, firstDayOfLastMonth and 2, lastDayOfLastMonth

And I have 2 parameters: 1, start_date and 2, end_date. In the parameters start_date the default value expression is $V{firstDayOfLastMonth} and in end_date it is $V{lastDayOfLastMonth}.

My query is:

 

SELECT
count(*) 'Total Alarms',
from alarminfo
where
 alarminfo.set_time>=$P{start_date} AND alarminfo.set_time<$P{end_date}

 

There are no compiling errors, but somehow everything stays NULL 

 

Does anyone have an idea what I am doing wrong? 

[santhosh.devadurga]

Hello CaPsLoCkEr,  please share the logic/expressions used for creating 2 variables: 1, firstDayOfLastMonth and 2, lastDayOfLastMonth. 

[CaPsLoCkEr]

Hello Santhosh:

firstdayoflastmonth: 

(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()

 

 

lastdayoflastmonth:

(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()

[joseng62]

Why can't you just use the logic in the parameter ? Should work. 

Also you could try and use net.sf.jasperreports.types.date.DateRange to getStart() or getEnd() of a given date

Example: 
Day = new net.sf.jasperreports.types.date.DateRangeBuilder("DAY-1").toDateRange()
Then  StartDay = $P{Day}.getStart()         (java.util.Date)
           EndDay = $P{Day}.getEnd()