I have a multiselect or parameter with a static list of values that displays in a listbox. The autoprompt adds a 'Select All' item in the listbox, which is great. However, there are some records where the value of the column I parameterized is NULL (MISSING). I want to see those records when the user leaves the parameter selection as 'Select All', but that is not the result. How can I get 'Select All' to also return records where the value is NULL?

Erik

This message has been edited. Last edited by: Kerry,

What value is passed to the WHERE clause if the user selects 'Select All'? It should work if the value passed is 'FOC_NONE'. FOC_NONE will cause the WHERE clause to be completely ignored, which should result in all records being retrieved (including NULLs).

Good question.

The 'Select All' option is added automatically as a result of the variable type being set to 'Multiselect OR'. I don't know, but I assume FOC_NONE is what gets passed. Is there a way for me to tell if that's the case?

It appears that the 'Select All' does not pass FOC_NONE, because I manually added an 'ALL' option with the value set to FOC_NONE. When I run the report and select the 'Select All' item, I get all records that have a value set. When I select the 'ALL' item, I get all records that have a value set and all records that are NULL.

Unfortunately, I want the list to be multiselect, so if I leave the manually added item with the FOC_NONE value it will be confusing to the user to have a 'Select All' item that doesn't work and an 'ALL' item that does work as expected. Any suggestions?

First put

-SET &ECHO = ALL ;

at the top of your report program. Then, after you run the report, right click on the report output and select 'View Source' from context menu. Scroll to the bottom of the new window and you should see the code that was actually used to generate the report, including the WHERE clause.

After you determine the value passed by 'Select All', use a Dialogue Manager -SET statement at the top of your report program to replace it with 'FOC_NONE' for the ampersand variable passed by your listbox. Something like this:

-SET &LISTBOXVAR = IF &LISTBOXVAR EQ '<Select All value>' THEN 'FOC_NONE' ELSE &LISTBOXVAR ;

Here is what it returns:
WHERE KRONOS_EXCEPTIONS.KRONOS_EXCEPTIONS.EXCEPTION_OUT EQ 'E' OR 'L' OR 'U' OR 'A' OR 'G' OR 'I' OR 'S';
My guess is that it effectively does the same thing as if I had manually selecte each item in the list, rather than not selecting any.
I also have a Multiselect OR on another parameter that is dynamic, not static. It behaves differently in that it returns FOC_NONE when I pick the 'Select All' option for it. These kind of inconsistencies in WebFOCUS drive me nutts. I'm understanding the problem better, but I still don't know what I'm going to do about it.

Here are a couple of things you could try.

1. What happens if you add your own 'Select All' option to the listbox with a value of FOC_NONE? Does it replace the 'Select All' option created by AutoPrompt or does the listbox contain two entries for 'Select All'?

2. What should happen if the user multi-selects all discreet values from the listbox instead of selecting 'Select All'? If the resulting report should show null values too, then put the following statement at the top of your report program (use the actual variable name populated by your listbox in place of &LISTBOXVAR). Of course if the order of your listbox options changes, or you add or remove options, this statement will also need to be modified.

-SET &LISTBOXVAR = IF &LISTBOXVAR EQ '''E'' OR ''L'' OR ''U'' OR ''A'' OR ''G'' OR ''I'' OR ''S'''
-                  THEN 'FOC_NONE' ELSE &LISTBOXVAR ;

3. Finally, you could add 'Missing' to the listbox as an option, with a value of, say, 'X'. Then add these statements to the top of your report program. Again, you will need to replace all occurrences of &LISTBOXVAR with the actual name of your listbox variable. This code looks for occurrences of 'X' in the variable string, and, if it finds one, appends an 'OR...IS MISSING' argument to the end of the string.

-SET &LISTBOXVAR = IF &LISTBOXVAR CONTAINS '''X'''
-                  THEN '(' | &LISTBOXVAR | ')' | ' OR ( KRONOS_EXCEPTIONS.KRONOS_EXCEPTIONS.EXCEPTION_OUT IS MISSING )'
-                  ELSE &LISTBOXVAR ;