Ad Hoc

Synonyms: 
Adhoc
adhoc
ad hoc

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: 2 days 2 hours ago

Answers:

No answers yet

Pages

AdHoc Pie Chart Adding Datalabels (AWS)

Hello,

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: 1 day 11 hours ago

Answers:

No answers yet

Pages

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

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.

 

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

Answers:

0

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>

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
95
Joined: Aug 17 2006 - 5:03pm
Last seen: 2 weeks 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.
Regards,
Brice

brice.montandon - 2 weeks 4 days ago

Pages

0

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's picture
Joined: Sep 24 2014 - 12:42am
Last seen: 1 week 3 days ago
0

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 week 3 days ago

Pages

Jasper Ad Hoc view looks fine, But report based on the view looks weird.

I created the above Ad Hoc view, a pretty basic one. And it looks fine. But as soon as I made a report out of the view, it looked like the following:

 

What is possibly going wrong here, and how can I fix this?

 

Thank you! :)

vivek.o.todi's picture
Joined: May 21 2014 - 1:18am
Last seen: 2 weeks 1 day ago

Answers:

0

It may have to do with the Adhoc Template that you are using to generate the report, are you using  Actual Size or one that defines a specific page width?

If you need more info loot at the  JasperReports Server Admin Guide 5.5 section 7.8.4 Ad Hoc Templates and Report Generators.

marianol's picture
2046
Joined: Sep 13 2011 - 8:04am
Last seen: 3 days 4 hours ago

Hi, thanks for the information. Could you explain more about what is Actual Size and one that defines a specific page width?

vivek.o.todi - 2 weeks 5 days ago

Pages

Pages

Adding Custom Summary / Aggregate Function to AdHoc

Hello,

I am using Jasperserver 5.6 on AWS.

Is there a way to add a custom sql function as an aggregation / summary function to the context menu of a measure in the adhoc. Without using calculated fields/measures in adhoc?

(like this http://community.jaspersoft.com/wiki/adding-standard-deviation-ad-hoc , but this example does not work in 5.6 version)

Thanks a lot for your help,

Philipp

 

 

stegmann's picture
Joined: Jul 18 2013 - 6:50am
Last seen: 1 day 11 hours ago

Answers:

No answers yet

Pages

Ad Hoc Domain parameters

Hello colleagues!

I've been creating Ad hoc domain and trying to use derived table because I have a specific query. Can I provide some kind of placeholders in query as parameters while creating domain? For example, here we have a query: select * from table_x where y = ?. How can I provide parameter for this "?" so that I can use it and pass it in View or Report component further?

Thank you!

john.doe1991's picture
Joined: Oct 10 2013 - 11:57pm
Last seen: 3 weeks 3 days ago

Answers:

1

You can use profile attribute or user attribute. Those could be defined at server level, user level or tenant level.

https://community.jaspersoft.com/documentation/jasperreports-server-user...

HTH

nthapa's picture
434
Joined: Jan 24 2014 - 9:53am
Last seen: 2 days 9 hours ago

I'm sorry. I didn't really get from this doc how can I access user attributes from query in Derived Table tab?

john.doe1991 - 3 weeks 5 days ago

Pages

0

Seems that we should use domain design XML file. Otherwise, what expression should I use to access current logged in user from that XML?

john.doe1991's picture
Joined: Oct 10 2013 - 11:57pm
Last seen: 3 weeks 3 days ago
0

This might be more clear:

https://community.jaspersoft.com/documentation/jasperreports-server-ulti...

You can also add server level attributes from Repository ->root->System properties ->GlobalPropertiesList ->Edit (after right clicking) and make the required changes.

To access those variables, you can directly use  {attribute('attributeName')}  on your sql.

nthapa's picture
434
Joined: Jan 24 2014 - 9:53am
Last seen: 2 days 9 hours ago

Pages

Ad Hoc Report Not Returning All Data

When viewing an ad hoc report in JRS, only the first 36 pages are being returned.  By default, the input controls are all set to "is one of", with the "NONE" option selected to bring back everything that the view/domain finds.  If I switch the filters to "ALL" while viewing the report, all pages (approx. 1500) are returned.  Since this report is used against several sets of data, is there a way to display all the data on the report while having the input controls set to "NONE"?

majst2005's picture
Joined: Oct 28 2011 - 11:02am
Last seen: 1 month 11 hours ago

Answers:

No answers yet

Pages

Jaspersoft Pro Ad Hoc Domain/OLAP View (Mondrian) reports limitation?

Hi

I try an OLAP view with a Mondrian schema in an Ad Hoc report, but it seems that there is no totals functionality. Is this the case or I miss something?

Using an Ad Hoc report with a Domain you can see totals in rows and columns. However you cannot set up hierarchies and custom ordering in fields (e.g name column – ordinal column)?

So it seems that there is some limitations in Jasper pro edition as the best is to be able in both Domain and Mondrian Ad Hoc reports to have the same functionality (totals, ordering, etc).

Is this possible?

Thanks

bidesigner's picture
Joined: Jun 16 2014 - 1:45am
Last seen: 3 weeks 2 days ago

Answers:

No answers yet

Pages

Pages

Feedback
randomness