Jump to content
Changes to the Jaspersoft community edition download ×

Using SQL in DomEL for calculated fields


lsigalat

Recommended Posts

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


Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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

 

 

 

 

Link to comment
Share on other sites

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 SELECT... ?

 

Regards, Liran

Link to comment
Share on other sites

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

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...