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?
1 Answer:
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()
Hello CaPsLoCkEr, please share the logic/expressions used for creating 2 variables: 1, firstDayOfLastMonth and 2, lastDayOfLastMonth.
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()