Jump to content

Ad Hoc Dataset Row Limit is erroneously applied to timestamp filters


Recommended Posts

I have run into a serious issue which may be a showstopper. Please let me know if I am missing something here.
The Ad Hoc Dataset Row Limit determines the maximum number of records that can be returned in a report. However, if you create a new ad hoc view with a crosstab and create a filter on a timestamp field, and you change the operator on the filter from "equals" to "is between", then the Ad Hoc Dataset Row Limit is applied to the timestamp field in the source table. So for instance, in my example, I have a new ad hoc view with no columns or rows added to the crosstab. I create a filter on a timestamp field and change the operator from equals to is between. Then this query is executed in our Redshift cluster:
select "timestamp" from "public"."table" group by "timestamp" order by "timestamp" limit 2001
Please note the following:
* the Ad Hoc Filter List of Values Row Limit is set to 1000
* the Ad Hoc Dataset Row Limit is set to 2000
Why is the Ad Hoc Dataset Row Limit being applied in that query which is automatically generated instead of the Ad Hoc Filter List of Values Row Limit?
This is a serious problem, because users of the system need to be able to schedule the export of large recordsets (250,000 - 500,000), so in production, we would have the Ad Hoc Dataset Row set much higher than 2000. If the Ad Hoc Dataset Row is set to 500,000 and you repeat the above steps I took, then the server becomes non-responsive due to the fact that it runs this query:
select "timestamp" from "public"."table" group by "timestamp" order by "timestamp" limit 500001
Is there a fix for this or some kind of workaround? If not, this might be a show stopper :(
Here is how the issue manifests itself and is experienced by one of the business users in our organization:
There is a significant problem with the use of the timestamp filtering,  and now  I cannot:
1. Create new Reports from Views I"ve already created.
2. Create new Views and Reports  
In order to control the results and not time anything out, we established a best practice to set-up views using "equals" on the created_timestamp Pre-filter (or Filter).  However, once the view is ready to be a Report, we need something (anything) else because:
A. "equals" does not offer the option of relative time (days, weeks, months)
B. You cannot change anything except the actual timestamp parameter when scheduling a report. 
What I mean by this is that you cannot change the filter items (i.e from "Equals" to "Is between") when scheduling a report. You can only change the parameters (i.e the date options) within the filter set by the View. 
How To Re-create:
1. When trying to change a View's filter from "equals" to anything else. an empty Error Dialog box appears and "Equals" remains the filer.
2. When trying to create a new View I followed these steps:
1. Added four selections: Account, created_timestamp, provider, audits
2. Pre-Filter using created_timestamp "is between" (using a minute of time)
3. Selected "Table" 
When the View editing screen loads, it "thinks" and eventually sends an empty Error response pop-up. You can close it, only to find the created_timestamp filter is "stuck" showing you "is between" but not allowing the calendar lines to show, thus not allowing you to adjust the parameters.


Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic


While I'm still waiting for a response on the row limit implementation. I did want to recommend one option that will resolve this issue.

As you are describing the need for report generation, you can use our Jaspersoft Studio Pro (Included with your AWS hourly license) which will allow you to build pixel perfect reports with extensive charting capabilities and advanced filters. You will then publish these reports to the server and they can be scheduled and shared with your users.

You can also use them as Topics which are canned queries loaded to memory in our ad-hoc desinger, only this time, they will be pre-filtered by your query.

Let me know if you need help getting your Jaspersoft Studio copy or have any follow up quesitons.



Link to comment
Share on other sites

This issue of the ad hoc row limit being incorrectly applied in queries to populate filter values ALSO affects the filters in REPORT parameters (as well as views as originally stated). Originally I thought the issue only affected views, but this is not the case (thus making using reports on a server with a large ad hoc row limit nearly unusable due to the large queries that occur to populate parameter dropdowns when using reports).
Link to comment
Share on other sites

  • 2 months later...
  • 6 months later...

Quiero USAR Varias Consultas en mi informe. Pero El Informe principal de Que Me permite Solo Una consulta director, embargo de pecado, encontrado Una Manera De Tener Varias Consultas en mi informe y Que se REALIZA Por subdataset en iReport. Pero no estoy en las Condiciones de acceder a este Los Campos de mi subdataset En Mi principal-Informe. ¿Como hacer la ESO?


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