LOVs

[system]

Open your prm file, make a copy, rename the old one.old.
Open in a text editor and add this parameter to the text file in the first section, configuration.
I think this should work:-

<Parameter Name="MAX_INLIST_VALUES">256</Parameter>

Save, restart BO.

As I don’t know what flavour of DB2 you are running I cannot make an informed judgement on which one to change. I suggest changing DB2EN as a starting point. Experiment, just don’t forget to make copies of the .prm files first… 8)

---

Mak 1 (BOB member since 2005-01-06)

[system]

Good morning from Greece!!!

One more question:

What is the upper level for the MAX_INLIST_VALUES in BO XI R2?
In BO 5.1.7 is by default 99 kai if you manipulate the variable it goes up to 256.
Ho many in BO XI R2?

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Well, i found the answer for my previous question by myself and now i have a new one…

I have this certain report where i face the problem that i have over a thousand values in my LOV file and the users want to select them all…

Of course, as we all know (even i do), this thing cannot be done, because in BO 5.1.7 you can select 99 values, by default, and 256, maximum, if you mess with the MAX_INLIST_VALUES parameter.

My problem cannot be solved by BO as it is and the users insist on their demand. I tried to solve my problem by creating a new object in the Designer, grouping values as it shows in the example below:

 select 
case 
when getdate() between '2006-12-1' and '2006-12-31'  then 'Dec06'
when getdate() between '2006-10-1' and '2006-10-31'  then 'Oct06 '
when getdate() between '2006-08-1' and '2006-12-31'  then 'Aug06'
end 

The problem is that the values that i am trying to group are not date fields but VARCHAR and they look líke this:

'00023 VENDOR_NAME'

I haven’ t be able to find a way to group these values…
In fact, i haven’t even figured out which function to use, ‘getString’, ‘getObject’, none of them?

Any help will be realy, realy, realy appreciated…

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Try and give a better example of what you are trying to do.

You are trying to get the varchars into buckets, right?

What do you want to return from

'00023 VENDOR_NAME'

Please give some more sample data…

In fact, i haven’t even figured out which function to use, ‘getString’, ‘getObject’, none of them?

Not sure what you mean here?

---

Mak 1 (BOB member since 2005-01-06)

[system]

Let me try to explain…

I have made a Universe and some reports on that Universe.
The users who use the reports can see data only for their branch (we have set some restrictions on the Supervisor).
In each and everyone of the reports there is a multi prompt.
One of the prompts is the ‘‘Vendor Code/Name’’ and in the LOV file of this prompt there are over 1000 values.
The problem is that some users want to run the reports for less than 100 Vendor Codes and some of them want to run the reports for all the Vendor Codes, which is something you know very well that it cannot be done by BO…

In order to solve thiw problem, i tried to find a solution similar to the code that i posted before but i didn’ t have any luck…
Then i tried to do this:

I created an new object in Designer where ther was only one value, ‘‘All Vendors’’.
I made a prompt of this object, which had only one value in the LOV file.
I tried to run the reports like a restricted user. I chose ‘All Values’’ in the first prompt and the values that i wanted in the ‘‘Vendor Code/Name’’ prompt and it worked just fine…but it didn’ t when i tried to run the report as an unrestricted user, with all the Vendor Codes because i had again to insert all the values… the code of my SQL was the folowing:

SELECT
  TABLE1.DWAR_ACODE,
  TABLE1.DWAR_DESC,
  TABLE1.BAS_EPITOKIO,
  TABLE1.SPREAD,
  TABLE1.DWAR_EFDT,
  TABLE2.AGREEMENT_DESC,
  TABLE1.DWAR_LNFAC,
  TABLE3.PERIGRAFH_VENDOR
FROM
  TABLE1,
  TABLE2,
  TABLE3
WHERE
  ( TABLE2.AGREEMENT_TYPE=TABLE1.AGREEMENT_TYPE and TABLE2.VENDOR_CODE=TABLE1.VENDOR  )
  AND  ( TABLE3.VENDOR_CODE=TABLE2.VENDOR_CODE  )
  AND  (
  TABLE3.PERIFEREIA  IN  @variable('Prompt1')
  AND  TABLE3.PER_KAT_SYNERGAS  IN  @variable('Prompt2')
  AND  TABLE3.PER_KENTR_SYNERGAT  IN  @variable('Prompt3')
  AND  TABLE3.PERIGRAFH_VENDOR  IN  @variable('Prompt4')
  AND  TABLE2.AGREEMENT_DESC  IN  @variable('Prompt5')
  AND  TABLE1.DWAR_EFDT  BETWEEN  @variable('Prompt6') AND @variable('Prompt7')
  AND  TABLE1.DWAR_ACODE  IS NOT NULL  
  AND  'All Vendors'  =  @variable('Vendors All')
  )  

I tried to fix the problem changing the AND to OR…
It didn’ t work either…
The SQL code was the following:

SELECT
  TABLE1.DWAR_ACODE,
  TABLE1.DWAR_DESC,
  TABLE1.BAS_EPITOKIO,
  TABLE1.SPREAD,
  TABLE1.DWAR_EFDT,
  TABLE2.AGREEMENT_DESC,
  TABLE1.DWAR_LNFAC,
  TABLE3.PERIGRAFH_VENDOR
FROM
  TABLE1,
  TABLE2,
  TABLE3
WHERE
  ( TABLE2.AGREEMENT_TYPE=TABLE1.AGREEMENT_TYPE and TABLE2.VENDOR_CODE=TABLE1.VENDOR  )
  AND  ( TABLE3.VENDOR_CODE=TABLE2.VENDOR_CODE  )
  AND  (
  TABLE3.PERIFEREIA  IN  @variable('Prompt1')
  AND  TABLE3.PER_KAT_SYNERGAS  IN  @variable('Prompt2')
  AND  TABLE3.PER_KENTR_SYNERGAT  IN  @variable('Prompt3')
  AND  TABLE3.PERIGRAFH_VENDOR  IN  @variable('Prompt4')
  AND  TABLE2.AGREEMENT_DESC  IN  @variable('Prompt5')
  AND  TABLE1.DWAR_EFDT  BETWEEN  @variable('Prompt6') AND @variable('Prompt7')
  AND  TABLE1.DWAR_ACODE  IS NOT NULL  
  OR  'All Vendors'  =  @variable('Vendors All')
  )

I cannot thin anything else…
Any suggestions

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Do the codes proceeding your character string identify the vendor?

'00023 VENDOR_NAME'

If so, why don’t you substring this code and then convert it to a number and base your case stement on that to group your values…?

---

Mak 1 (BOB member since 2005-01-06)

[system]

Have you tried this idea, from the Designer FAQ? It wouls allow the users to enter the word ALL at the prompt to get all values.

---

MichaelWelter (BOB member since 2002-08-08)

[system]

@MichaelWelter

Have you tried this idea, from the Designer FAQ? It wouls allow the users to enter the word ALL at the prompt to get all values.

I read the solution Cindy Clayton suggests.
I tried to do it in the Designer but nothing seems to work ok…
I tried to insert the code in my object but it didn’t work…
Where should i insert the code?
Can you give me more specific details?

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

I entered the code in the ‘‘Where’’ box of the object that i want to prompt and it worked but the thing is that this code gives you just the opportunity to select all your values in the LOV file.

When i tested in a Test Universe, where the values in the LOV file are less than 99 it worked fine but when i tested in a Live Universe where the values in the LOV file are over 1000…nothing happened…It didn’ t even grayed the selected values…

Help…

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Keep in mind that you database, most likely, will not allow more than 1,000 values in a condition. Most databases are limited to 700 or fewer values in a condition.

---

MichaelWelter (BOB member since 2002-08-08)

[system]

How can i insert a new value in the LOV file, in order to make a reference to another object with this value?

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

@MichaelWelter

Have you tried this idea, from the Designer FAQ? It wouls allow the users to enter the word ALL at the prompt to get all values.

I tried the following code (i changed it to cover my needs)

(@Select(Vendor Mis Data\Perigrafh Vendor)  IN @Prompt('Select one or more Vendors','a','Vendor Mis Data\Perigrafh Vendor',multi, free))  OR ('*' in @Prompt('Select one or more Vendors,'a','Vendor Mis Data\Perigrafh Vendor',multi,free)) 

but i get the ‘‘UNV0023’’ error…

I put this code in the where clause of the object ‘‘Perigrafh Vendor’’.
Do i have to do something else?
What am i doing wrong?

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Well, it looks to me like too many parens. Try this:

@Select(Vendor Mis Data\Perigrafh Vendor)  IN @Prompt('Select one or more Vendors','a','Vendor Mis Data\Perigrafh Vendor',multi, free)  OR '*' in @Prompt('Select one or more Vendors','a','Vendor Mis Data\Perigrafh Vendor',multi, free) 

---

MichaelWelter (BOB member since 2002-08-08)

[system]

The code is parsing ok.

I ran my report and i click on the ‘‘Values’’ button for the prompt that i want to use the wildcard. The list opens and i see the values, one by one.
I click on the ‘‘Refresh’’ button and pops up the second prompt with the wildcard selected. I click ‘‘OK’’, on the prompt window, ‘‘OK’’ agaín in the list window and…nothing happens, no value is selected…

Any suggestions

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

When the first prompt opens, type a *, and click OK.

---

MichaelWelter (BOB member since 2002-08-08)

[system]

@MichaelWelter

When the first prompt opens, type a *, and click OK.

I 've done it…
Still nothing happens…
No value is selected…
It seems very strange to me that this code works fine for the others but not for me…
I must be doing something wrong, but what?
Any suggestions what should i do?

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Can you post the actual SQL in the statement, rather than the @Select?

---

MichaelWelter (BOB member since 2002-08-08)

[system]

The SQL statement of my report, before i tried the @Select funtion is the following:

SELECT
  TABLE1.DWAR_ACODE,
  TABLE1.DWAR_DESC,
  TABLE1.BAS_EPITOKIO,
  TABLE1.SPREAD,
  TABLE1.DWAR_EFDT,
  TABLE2.AGREEMENT_DESC,
  TABLE1.DWAR_LNFAC,
  TABLE3.PERIGRAFH_VENDOR
FROM
  TABLE1,
  TABLE2,
  TABLE3
WHERE
  ( TABLE2.AGREEMENT_TYPE=TABLE1.AGREEMENT_TYPE and TABLE2.VENDOR_CODE=TABLE1.VENDOR  )
  AND  ( TABLE3.VENDOR_CODE=TABLE2.VENDOR_CODE  )
  AND  (
  TABLE3.PERIFEREIA  IN  @variable('Prompt1')
  AND  TABLE3.PER_KAT_SYNERGAS  IN  @variable('Prompt2')
  AND  TABLE3.PER_KENTR_SYNERGAT  IN  @variable('Prompt3')
  AND  TABLE3.PERIGRAFH_VENDOR  IN  @variable('Prompt4')
  AND  TABLE2.AGREEMENT_DESC  IN  @variable('Prompt5')
  AND  TABLE1.DWAR_EFDT  BETWEEN  @variable('Prompt6') AND @variable('Prompt7')
  AND  TABLE1.DWAR_ACODE  IS NOT NULL  
   )  

The @Select statement that i used in the where clause of the specific LOV file is the following:

@Select(Vendor Mis Data\Perigrafh Vendor)  IN @Prompt('Select one or more Vendors','a','Vendor Mis Data\Perigrafh Vendor',multi, free)  OR '*' in @Prompt('Select one or more Vendors','a','Vendor Mis Data\Perigrafh Vendor',multi, free)

---

dapostolopoylos (BOB member since 2005-06-02)

[system]

Sorry, I guess I wasn’t clear. Open the object in Designer, and click on the >> button to the right of your @Select statement. Then click on the “Show object SQL” check box, and copy the rusults into a post, so we can see the real SQL being used.

---

MichaelWelter (BOB member since 2002-08-08)

[system]

There it is:

LIBRARY.VENDOR_MIS_DATA.PERIGRAFH_VENDOR  IN @Prompt('Vendor Code/Name','a','Library Vendor Mis Data\Perigrafh Vendor',multi, free)  OR 'All' in @Prompt('Vendor Code/Name','a','Library Vendor Mis Data\Perigrafh Vendor',multi, free)

---

dapostolopoylos (BOB member since 2005-06-02)