BusinessObjects Board

Using BOE 4.3 SP2 - Web Intelligence - Using InList and 1000 Limitation

building reports
#1

Hello All,

I am using BOE 4.3 SP2 Web Intelligence.

I have a query with a prompt which is referring another object from a different query to look up the values using In List operator. I know that there is a limitation of 1000 for Inlist. Does anyone have any work arounds to resolve this issue?

  • Prompt name in list (Object from a different query)

Any help is appreciated.

#2

I believe the 1000 limit is related to the database not 4.3 At least that is a limitation of oracle. You’ll need to be a little more creative and re-design your query or have a merge dimension with filter to accomplish this.

1 Like
#3

Welcome to B :mrgreen: B Kalyani

There are limits within Web Intelligence for how many characters can be in a prompt value. It was more than 1000, but I don’t remember what it was.

If you have a large list of items, this isn’t the best way to accomplish it.

The best option would be to have a view built in your database so that the filtering takes place on the database server rather than during your report processing.

2 Likes
#4

Hello Kalyani,

We had encountered this issue. Please check following conditions:
a) Does this is object is derived from Universe? If yes, in the data foundation layer, there will be parameter called Max_inlist values which would be 1000 as a default. Kindly change that value to -1.
b) For every Databse, the size of an SQLquery
also effects. Suppose the values which are coming are lengthy characters/Texts and when you submit the query into DB, it will not be able to accept and produce the results
Suppose for Teradata, the database cannot accept more than 1.5MB(set by DBA) file

Let me know if it works

Thank you
Jagadeesh Chandrakumar

#5

Hi, thank you for your response. Can you please provide little bit more details o how we can achieve this using merge dimension?

Thanks in advance.

#6

Hi Jagadeesh,

I checked and it’s already set to -1.

Thanks,
Kalyani.

#7

The 1,000 item inlist limit is a function of the backend database, i.e. Oracle. It’s not a limitation coming from WebI.

Instead of using inlist, you can try replicating the criteria from you “main” query as a subquery in your “inlist” query. Or you can break your main query into multiple queries that return less data and use the responses from both as as “OR” in your inlist query. Performance may not be great with either option.

#8
  1. Is the other query based on the same universe? If so, can you use the subquery to achieve the same result?
  2. Sometimes the best way to achieve the desired result is to “clash” the data from the two queries and use report based filters.

regards

James