Ad Hoc

ad hoc

Adhoc editor slow

I am creating an adhoc view based on a domain. The query without filter brings in large amount of data and we are fine with the time it takes to load data. But while creating an adhoc view, the editor runs the query multiple times for adding columns, for grouping by a column, for adding a filter on a column. It takes really long time to create a filter and to group by a column even with sample data (vs. Full data) as the editor adds a group by clause. Using "No Data" is an option, but our users would probably end up trying the default option - "Sample Data".

natarajat's picture
Joined: Jan 7 2014 - 11:30am
Last seen: 1 day 21 hours ago



Few things to do here:

  1. The best one of course is a faster database - take a look at columnar database technology, this will give you a very fast boost! MySQL, SQL Server and now Postgres have columnar datastores available for them.
  2. There's the possiblity to work "in memory" instead of in-database as is the default mode. See the "Optimize Queries for Domain-based Reports" options documented here 
  3. You might consider the "no data mode" by default - I'm sure that toggling this requires some change in javascript or jsp somewhere in the server (not sure where!)
ernestoo's picture
Joined: Nov 29 2010 - 11:59am
Last seen: 18 hours 2 min ago

Facing the same problem. Looking into using in-memory database and RAM drives (not SSD) if the data fits in memory.

There's also the problem with cache not being updated when underlying data changes but this is another issue. :-(

hozawa's picture
Joined: Apr 24 2010 - 4:31pm
Last seen: 3 hours 42 min ago


Datetime filter in ad hoc view doesn't work


I have created a simple domain that gives access to a table with a datetime field. My data source is MS SQL and the date time format is yyyy-mm-dd hh:mm:ss 

I am trying to create a simple ad hoc view using that domain and I put the datetime field in the column. When I try to create a filter for the datetime it is created but nothing happens. 

When looking at the logs I see exception.


What am I doing wrong? 

guy.morgenstern's picture
Joined: Mar 23 2015 - 12:57pm
Last seen: 4 days 9 hours ago



@guy.morgenstern  Re: I think that the problem relates to the fact that Jasper is using timestamp type and I am using datetime type in my MS SQL Server. 

I think you are right here, you can change the default domain type mappings by changing them in /WEB-INF/applicationContext-semanticLayer.xml search in that file for the bean called "jdbcMetaConfiguration" you will see that it has a property (jdbc2JavaTypeMapping) that maps the DB types to the Java Types that jasper will use, make sure that the mapping is correct, I have not seen this issue with MS SQL server but it happens some times with some Oracle data types (you will see some of them commented out in the file).

Hope this helps.. either way @djohnson53 is also in the right path, this may be something that can be changed at the Domain level.

marianol's picture
Joined: Sep 13 2011 - 8:04am
Last seen: 19 hours 39 min ago


Use Data Staging with Jasper Studio Report


I have some reports created with Jasper Studio 6.0, in these Report  I have used a dataset to extract the data from DataSource,

In the Jasper Server, is possible create Ad Hoc View and Report with DATA STAGING functionallity to cache the data, Now , is possible to use the DATA STAGING functionallity also for the report created with Jasper studio..???


Thanks Max


mm972's picture
Joined: Jan 21 2015 - 2:58am
Last seen: 1 week 21 hours ago



Unfortunately that cannot not be done. Data staging is based off domain topic, which isn't part of jaspersoft studio.


nthapa's picture
Joined: Jan 24 2014 - 9:53am
Last seen: 1 week 15 hours ago

Thanks very Much

mm972 - 1 week 2 days ago



Report creation fails with ad hoc view using table with group on calculated field

I have an ad hoc view that has several calculated measures in the columns and a single calculated field in the group area (this is a table displaying totals data only). When I try to create a report using my ad hoc view, I'm getting the following error:

dwalton_1's picture
Joined: Feb 24 2015 - 10:12am
Last seen: 3 weeks 3 days ago


No answers yet


AdHoc Pie Chart Adding Datalabels (AWS)


using Jasperserver 5.6 on AWS.

I am trying to add DataLabels to the Piecharts in Ad-Hoc Editor For this I changed scripts/adhoc/highchart.datamapper.js as following


stegmann's picture
Joined: Jul 18 2013 - 6:50am
Last seen: 3 weeks 6 days ago


No answers yet


Adhoc view : Javascript error on using Date filter with 'in between'

Issue Description

When creating an ad hoc view with a date filter, the date filter gets added with equals clause, but when changed the option to "is between" it gives the following javascript error:

Calculated fields in Ad Hoc view



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.


brice.montandon's picture
Joined: Sep 24 2014 - 12:42am
Last seen: 1 month 6 days ago



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>

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.

bobtins's picture
Joined: Aug 17 2006 - 5:03pm
Last seen: 1 week 4 days ago

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.

brice.montandon - 1 month 2 weeks ago



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.montandon's picture
Joined: Sep 24 2014 - 12:42am
Last seen: 1 month 6 days ago

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.


brice.montandon's picture
Joined: Sep 24 2014 - 12:42am
Last seen: 1 month 6 days ago