using $V variables in SQL queries

[al_ryder]

Hi,

I'm trying to use a variable in a sql query.  I'm wondering if that's possible?

For example to do something like: select * from stored_procedure1($V{parameter});

The reason I need a variable is that the 'parameter' variable's expression calls a user defined java function which generates some data.

I've displayed the value of the variable using a textfield and it seems fine, but when I use it in a query it spits the dummy, looking at the database logs I can see that iReports is trying to pass the value "$V{parameter}" to the database instead of substituting the value of the variable.

 

Thanks

Al

 

 

 

Post Edited by al_ryder at 05/10/2010 08:16

[slow]

if you use parameter, then in your statement you can use the special syntax $P!{name} to pass sql code...

so your query can be:

select * from stored_procedure $P!{parameter}

with the help of  tests i made,  i think you can't use variable in your sql statament... but only parameter with $P or $P! syntax...

but... here it's a simple solution:
you can create a main report that calculates a variable, passing its value to a subreport in a parameter that you use in your subreport statement

example:

suppose you have the table MONTHS
num|name
1  |January     
2  |Febraury     
3  |March     
4  |April     
...|....

and you want to report a month name by passing a condition with the variable $V{condition} = "WHERE num=1"
where the month number is result of calculus


then... create a report Master... that has a query "select 1 from dual" (dummy query for oracle)
set a parameter Integer named MONTH
set a variable String named MONTH_CONDITION = " WHERE num="+$P{MONTH}.toString()

create a subreport Subreport, with the query "select * from month $P!{CONDITION}"
and set a parameter CONDITION, string type.

link the two reports and in the subreport parameters definition set CONDITION <== MONTH_CONDITION

in this way, your variable, in the master report could be anything... but the subreport don't view its value, masked by it's own parameter...
 

it works perfectly in my test :)

send me a feedback if you try it...

 

 

____________________________________
if it works... give me KARMA points please!    : ) 
____________________________________
 

Post Edited by slow at 05/10/2010 13:19

Post Edited by slow at 05/10/2010 14:49

[dk1]

Thank you slow, that works perfectly.

[cashshurley]

 This worked perfectly.  Thank you!  I was having a little trouble because I wasn't sure how to use the date as a string.

 

here's the variable, "firstDayofLastMonth" type java.util.Date:

 

(

    $P{cal}.set($F{runDate}.getYear()+1900, $F{runDate}.getMonth(), $F{runDate}.getDate()) ||

    $P{cal}.add(Calendar.MONTH, -1) ||

    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)

)

? null : $P{cal}.getTime()

 

which I passed to a $P{startDate} in the subreport as a string.  Worked like a champ!

FYI I was using date calculations found in this very excellent article:

 

http://type-exit.org/adventures-with-open-source-bi/2010/06/date-calculation-in-ireport/

 

cheers

cash

 

Post Edited by cashshurley at 08/02/2012 23:51