Jump to content
Changes to the Jaspersoft community edition download ×

Using $V variables in query


CaPsLoCkEr

Recommended Posts

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? 

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

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()

Link to comment
Share on other sites

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() 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...