Ad Hoc Dataset Row Limit is erroneously applied to timestamp filters

[manny_1]

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.

 

[abroitman]

Hi Manny,

I'm part of our AWS team and I will follow up with our tehnical support to clarify this issue and see if we can work around the behavior you are experiencing.

Thank you,

Assaf

[abroitman]

Manny,

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.

Thanks,

Assaf

[manny_1]

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

[biamazon]

Hi. I'm facing the exact same issue which is definitely a show stopper. Please let me know if we have a solution/workaround for this.

[criztiandiaz]

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?

Gracias!