nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Filters. Not adequate for large data? What then?

akk2

Hi, I'm trying to make a report which queries a view containing millions of rows. There are basically two fields I need to filter on, one is an ID, the second is a time. So I've encountered two problems. 
 
1) It seems like the filter is only applied after the query is run and all the data is returned, which in my case, never resolves and throws out of memory. Is this normal? If so, then I guess that filter is inadequate in this case, which leaves parameters.
 
2) But the param cannot take multiple values... So if you want to pass more than one ID, you have to code a script to modify the SQL query to include the parameter manually?
 
It just seems weird as both of these seem rather basic for a reporting tool... How could passing multiple parameter values never be implemented in Birt? What is the use of having filter which loads results in memory that will never be used because they will be filtered anyhow? I guess maybe filters for subreport when the dataset data is already in memory but has to be used as a subset?
 
Thanks!

Find more posts tagged with

Comments

Matthew L.

When using a "List Box" type parameter, you have the option to "Allow Multiple Values".
This allows the end user to select multiple parameter values by CTRL+Click multiple values listed in the parameter page.
 
To use the multi-value parameter in a query so that you do not have to return unwanted data and filter it on the report side, you can add a single line replace statement in the data set's beforeOpen script which will join the array/object values of the parameter into a comma separated value for a queries IN () function.
 
See the attached example which includes both a method for multiple 'String' values as well as multiple 'Integer' values.

akk2

Thanks! That's definitely what I'll be using. 
 
But is my comprehension of the filter correct? The filter is applied after querying the database and loading all the results in memory? If so, I guess in the majority of cases it is subobtimal to use filters for receiving & filtering data received through parameters, as as mentioned, it just loads data which will be filtered anyhow; so wastes resources for absolutely no benefit... Or am I missing something? When should you use dataset filters?

Matthew L.

To my understanding the filter is applied after the query runs so all result data is in memory, unless you are using a "JDBC Database Connection for Query Builder" data source. Which I believe you can enable push-down filters on the data set filter which will apply the data set filters to the query for the database side.
 
Other reasons I can see for using a filter on the data set side is for computed columns. You could create a computed column on the unfiltered returned data, and apply a data set filter for the computed column only.

jar

You should make seperate queries to fill the ID and TIME parameters. Do not use the final query.
Assuming the ID's are not unique in the multii million table.

akk2

Thanks Matthew!
 
Jar: Not sure I understand. Query could be for instance: select a, b, c from view123 where id in ('%listManagedByScript%') and xtime > ? and xtime < ?
 
Using Matthew's sample report technique, the id is populated given the script altering the query. The datetime parameters are both configured as parameters in the dataset and so are replaced automatically in the query. What do you mean making different queries to fill ID and time and not using the final query?