Paramteter Question

[sjoerd.jump]

Hello, I have a question about paramaters:

What i want to do is extract the MONTH from a date. and use this in a query.

So far I did the following:

Creacte parameter1
Set -> java.util.date

In query: 

select datefrom tablewhere extract(year from date) = extract(year from current_date) and extract(month from date) = extract(month from $P{parameter1})[/code]

 

Date in my databse is formatted as yyy-MM-dd

 

What am i doing wrong? Can someone help?

 

thanks

 

sjoerd

[augarte]

Hi, 

I think when you use parameters in a query you need to use the following notation: $P!{parameter1}. At least, in the reports I've done till now I have used parameters as mentioned. So you should set your query as follows: 

SELECT DATE

FROM TABLE

WHERE EXTRACT(YEAR FROM DATE) = EXTRACT (YEAR FROM CURREN_DATE) AND EXTRACT (MONTH FROM DATE) = EXTRACT (MONTH FROM $P!{parameter1})

Hope this helps.

Regards

[sjoerd.jump]

hmm, thats not working.

 

Have tried to shorten the query (since im only intresed in a month) to

 

where $P!{parameter1} = extract (month from datum)

 

If I subsitute $P(parameter1) for '5' than i see the data in my table im looking for.

 

I have set parameter1 to java.lang.double since thats the format of an extracted part of a timestamp.

[sjoerd.jump]

Solved it: 

I was using a crosstab and never thought of passing the Parameter to the crosstab!
So, in crosstab data, go to Parameters. Add parameter with the value of the parameter you have set up in the main report.

[Ankur Gupta]

Hi sjoerd.jmp,

 What I understood here is that you want to get the Month and Year , of the date which user enters so what you can do is ,

Let user enter the date let it be $P{DATE},

Then make two more parameters suppose $P{MONTH} and $P{YEAR} but , remove the Use as prompt option for these two. 

set the default expression for  $P{MONTH} as $P{DATE}.getMonth()

and   $P{YEAR} as ($P{DATE}.getYear() +1900)   [This 1900 is added for returning the year as this returns the number of year in this current century]

Then in the SQL you can directly use 

WHERE EXTRACT(YEAR FROM DATE) =$P{YEAR} AND EXTRACT (MONTH FROM DATE) = $P{MONTH}

 

Hope that above explanation helps.

Thanks,

Ankur

[Thomas Zimmer]

that's not correct at least; $!P{...} is used for String Parameters only and tells the executor not to create inverted commas around your string at execution time.

[sjoerd.jump]

thanks, thats it indeed :)

The problem was that i tried to send the parameter to a crosstab and had to add the parameter to the list of the crosstab.

 

Do you happen to know how to export the entire report including sub-datasets to jasperserver?