Using SQL in DomEL for calculated fields

0

Hi, 

I'm new to Jaspersoft and want to use SQL for a calculated field on JasperReports Server if that is possible, something similar to examples I've seen for iReport where a parameter is passed to the query, such as:

SELECT * FROM Address WHERE city = $P{customerId}

Is it possible at all? If so, what is the exact syntax? I'm afraid section 8.2 in the JasperReports Server User Guide hasn't been very helpful and I couldn't find any examples.

I tried the the simplest SQL queries, even ones without a parameter and I always get the response of: "Cannot evaluate expression".

Thanks

lsigalat's picture
Joined: Dec 13 2013 - 1:17pm
Last seen: 6 years 10 months ago

3 Answers:

0

Hi,

it is completely possible using parameters directly in the SQL, it is just few things you need to take care of:

1. The parameter compared should be matched in terms of datatype as wells as the content with the database field matched in the where clause, as I can see in the SQL code you have mentioned you have compared city = $P{customerId} ,  so the city as well as the parameter customer_id should be of similar datatype and should contain consistent data i.e. if the field is integer then the parameter compared should also be of integer type . The type for the parameter could be set in the properties of the parameter

 

2. Please check, before using the parameter in the SQL query it should have a default value, i.e it should be initialized. For instance if the parameter is of type Integer then the default value should be given as 0 or in case of string can be " ", because for the query to be correct it needs to have values in the SQL ehich are by default not null so else it will produce an error.

 

Hope that this helps.

Thanks,
Ankur Gupta

 

 

 

 

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 2 weeks 14 hours ago
0

Hi,

Listen if you are using this code to generate the jasper report then it'll run but if you want to run it in oracle then it'll not work.

for oracle we are using SELECT * FROM Address WHERE city = bnd_customerId

and for jasper report it'll be SELECT * FROM Address WHERE city = $P{customerId}

swati.mohanty's picture
Joined: Oct 30 2013 - 12:10am
Last seen: 6 years 4 months ago

Hi,

Thanks for the responses.
I'm using Amazon Redshift as the DB. I have an even more basic question.
For some reason any SQL query I use in the calculated field of JasperReports Server (even ones with no parameters) return the "Cannot evaluate expression" response.
Do I need to encapsulate the SQL query with something like <query> SELECT... </query>?

Regards, Liran

lsigalat - 6 years 10 months ago

Hi,

I have a table called public_hotels_keywords with a date field in it called currentdate and want to perform something like:
select MAX(public_hotels_keywords.currentdate) from public_hotels_keywords

Even if I remove the 'MAX' function I still get the same problem of "Cannot evaluate expression"

Thanks

lsigalat - 6 years 10 months ago
0

for example???

 

swati.mohanty's picture
Joined: Oct 30 2013 - 12:10am
Last seen: 6 years 4 months ago
Feedback