@Prompt values limited to 100

building semantic layer
1

Dear BO Experts

I have a BO 4.1 unx universe, and in a report I do a @prompt

I noticed that if I put more than 100 values in this prompt, it crashes
(a query filter object contains too many values for the object to be used in a query filter)

If I take the query and run in my HANA DB, I can enter even more than 100 values, so it seems it’s not a DB restriction but universe…

Do you know in a unx where this can be tuned?

Thanks a lot for you help in advance!

:nopity:

Victor_BCN :es: (BOB member since 2009-09-15)

2

It will not crash. Just now I tried it is working fine. Please try by creating a new report

surya_sudheer :india: (BOB member since 2006-11-01)

3

I can assure you it crashes :smiley:

I just made a new report and crashes.

also tried in the .unx going to the semantic layer (.blx) and in the Properties tab, click on Parameters and added the MAX_INLIST_VALUES = -1 , also tried with 5000 and it doesnt work this way :frowning:

Im not sure if something in CMC has to be tuned as well…?

:nopity:

Victor_BCN :es: (BOB member since 2009-09-15)

4

every database has different limitations on how many values can be included with the in operator. oracle i believe is 100. this is a limitation of the db not the unx

erik.stenson :us: (BOB member since 2012-07-30)

5

Yes, I agree

But the point is the HANA DB it’s running on has no problems with more thatn 100 values

I took the query and entered like 300 values, query ran fine.

But when it’s launched from the report, it crashes.

This means the limitation is in the unx or in the BOE itself…any property of its servers I guess

Victor_BCN :es: (BOB member since 2009-09-15)

6

As far I know there is no limitation in BO for the prompt values. You can check the properties of the prompt or you can check with your BO Admin

surya_sudheer :india: (BOB member since 2006-11-01)

7

I can tell you there is a limitation :slight_smile:

The funny thing is if I take the query with 110 values I was running directly on HANA, and if paste it into the query report…it runs good :smiley:

So, when report has the hardcoded query in it, and runs directly in the DB, it’s fine

:nopity:

Victor_BCN :es: (BOB member since 2009-09-15)

8

Try setting MAX_INLIST_VALUE = -1

This should utilise the database limit if any

jemstar :ireland: (BOB member since 2006-03-30)

9

Hello, thanks for your answer.

That was my 1st try :frowning:

In Business Layer/Parameters I entered this value but still doesnt work :frowning:

Victor_BCN :es: (BOB member since 2009-09-15)

10

Hello, thanks for your answer.

That was my 1st try :frowning:

In Business Layer/Parameters I entered this value but still doesnt work :frowning:

Victor_BCN :es: (BOB member since 2009-09-15)

11

Did you re-start the connection server?

jemstar :ireland: (BOB member since 2006-03-30)

12

Hi Jemstar

ummm I didnt restart anything…what do you mean exactly?

to restart the BOE server?

Victor_BCN :es: (BOB member since 2009-09-15)

13

Go into the CMC
Servers
Connection Server should be listed, right click re-start

Or reboot the BOE server which will re-start all the servers

jemstar :ireland: (BOB member since 2006-03-30)

14

Hello!

In CMC Servers/Connectivity Servers I restarted the ConnectionServer and the ConnectionServer32

But still cannot make it work :frowning:

When more than 99 values, crashes :frowning:

I attach a pic of my universe parameters, do you see anything wrong?

Thanks a lot!
PicUniverseParameters.jpg

Victor_BCN :es: (BOB member since 2009-09-15)