Calculated fields in Ad Hoc view

[brice.montandon]

Hi,

 

I'm trying to create a calculated field in an Ad Hoc view.

I'm using Jasper Report Sever 6.0.1.

 

I'm not trying to create a calculated measure but a calculated field.

 

So in my Ad Hoc view, I am selecting "Create calculated field".

In Formula I'm tiping : Mid("Account Id",1,4)

where "Account Id" return a string made of numbers.

I click on validate button and reached a "Validation successful!" message.

So I save it.

 

Once I'm trying to use it in the Ad Hoc view it raises message :

"An error occured while performing the previous request".

I really don't know what happens.

 

I've noticed that if I created by using feature "Create Calculated Measure" it works better.

I can integrate it as a measure but it's not working as I want cause it is not a measure but a dimension.

 

Any idea on how it should work ?

 

Thanks in advance.

Brice

 

 

[brice.montandon]

After some more investigations I've retrieved the full error message.

Here is the interesting part :

Error Message

org.postgresql.util.PSQLException: ERROR: function SUBSTR does not exist (Hint: use SUBSTRING instead)

 

We are using Jasper in Amazon and our data source is stored in a Redshift DB.

Recomendations for Redshift access is to use PostgreSql JDBC driver.

In Jasper Mid function is mapped to SUBSTR PostgreSql function, when in Redshift SUBSTR doesn't exist and should be repaced by SUBSTRING, exactly what I got in error message.

 

Now I will have to look for the function template file dedicated to Postgresql on Jasper Server and change function definition.

I will try to comment this ticket if it works.

 

Brice

[brice.montandon]

Here is the solution.

 

In file applicationContext-semanticLayer.xml located on the server, all functios are mapped.

They can be generic or specific for a given technology.

We added a specific translation for Mid for PostgreSql technology.

 

[bobtins]

The definition of the Mid function is in the form of a Groovy language snippet which is part of the SQLGenerator bean.

This definition is in WEB-INF/applicationContext-semanticLayer.xml.

There is a defaultSQLGenerator bean which defines the Mid function this way:

                <entry key="Mid">                    <value>"substr(" + sqlArgs[0] + ", " + sqlArgs[1] + (sqlArgs.size() == 3 ? (", " + sqlArgs[2]) : "") + ")"</value>                </entry>[/code]

If you only need to get Redshift working for domains, then you can change this default definition so it uses "substring" instead of "substr".

If you need to get other data sources working, then this config change may affect the Mid function for other datasources, and you would have to configure a Redshift-specific SQLGenerator. Try changing the default and see how that works.

[brice.montandon]

exactly what I did.

Change the PostgreSql-specific SQLGenerator.

PostgreSql is the driver used for Redshift, and PostgreSql accepts both SUBSTR and SUBSTRING syntax.

 

Thank you for your answer.

Regards,

Brice