Prompt LOVs -- refresh or not?

building reports webintelligence
1

The logic that WebI uses to determine whether or not a prompt LOV will be populated when the prompt panel is opened has been a mystery to me for a long time. I knew that the logic changed between XIr2 and XI3 but I could never nail down exactly what would cause it to display or not display.

I knew that the “Automatic Refresh Before Use” (hereinafter “ARBU”) setting had an effect, but the result was sometimes contradictory to what its name implied.

I spent some time on it this morning, and came up with the following rules (based on XI3.1 SP3.1):

Behavior when prompt panel is opened:

            ||                                              |
            || Prompt is not Optional, and                  | Prompt is Optional, OR has
            || does not have "Keep Last Values" set         | "Keep Last Values" set
            ||                                              |
==========================================================================================================
            ||                                              |
 ARBU on     || List is refreshed from db (and cached)       | List is empty
            ||                                              |
-----------------------------------------------------------------------------------------------------------
            ||                                              |
 ARBU off    || List is loaded from LOV cache if available,  | List is loaded from LOV cache if available,
            || otherwise refreshed from db (and cached)     | otherwise list is empty
            ||                                              |

[list][:5a37d5dbeb]In all cases above, the “Refresh Values” button can be hit to force a refresh of the LOV from the database.
[:5a37d5dbeb]If ARBU is ON, then no .lov file is created in the server cache.
[:5a37d5dbeb]If ARBU is OFF, then an .lov file is created in the server cache (data[cms]\storage\universes) whenever the prompt is refreshed from the db, whether initiated automatically or by “Refresh Values”.
[:5a37d5dbeb]The “ARBU Off” scenarios above assume that the “List of Values Cache” is enabled on the WebI server.
[:5a37d5dbeb]The universe object setting “Export with Universe” does produce an .lov file with the universe; however these files are never used for WebI.
[:5a37d5dbeb]The above applies to View Mode. There is a bug in XI3.1, at least through SP3.1, that causes prompts in Modify Mode to refresh every time.[/list]
I’m interested to hear if anyone else has experienced behavior different than what I have, or if it’s changed again in BI4.

Joe

joepeters :us: (BOB member since 2002-08-29)

2

Hi, Joe;

I haven’t done this level of analysis in 4.0. However, I have observed that, most of the time, LOVs are empty when the prompt comes up, forcing the user to hit the refresh button to see values. From my experience, most users find this annoying, and would prefer to have the list always populated with data. So, I submitted an idea on the SAP Idea Place for this to be changed. The last response in the idea is as follows:

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Thanks, Michael, I thought I had remembered seeing something about that.

So the lists never pre-populate? So there’s no more LOV cache?

That makes no sense. If anything, it’s a band-aid fix in lieu of a proper strategic solution.

joepeters :us: (BOB member since 2002-08-29)

4

There is cache, but the lists don’t load with them unless the user hits the refresh button. At least, that’s been my observation. And I agree. Having a strategy that annoys users is not a good strategy.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

Hi Joe,

one additional observation to your list from above:

left side: Prompt is not Optional, and does not have “Keep Last Values” set and does not have a default value

right side: … same + OR has a default value defined

i.e. if you have a prompt like

@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)

the list will not pre-populate automatically but just use the default value(s).

this behavior sort of makes sense to me… however my users want something different :wink:

so we would need to pre-populate the cache somehow AND switch ARBU off? does not sound very practical to me. Also maybe it’s not a good idea to do that for ALL reports.

concerning your remark:

maybe we can make it a feature? (it’s probably Java related?)

cheers
MU :mrsbob:

Miss Universe :de: (BOB member since 2010-02-16)

6

I am on 4.1 SP6.

What I’ve discovered is that the LOVs don’t auto populate if the parameter is set to ‘Optional’. This is annoying but at least the behavior is known.

mcliffordgoo :us: (BOB member since 2003-02-13)

7

Hey guys,

I’ve a little problem related to this topic - hopefully somebody could help me out.

I’ve some objectss having a very long LOV. So I unchecked the “Automatic refresh before use” option in the Universe for these objects. When I use this object as a query filter and hit “List of Values” the list is empty as it should be. However, when I set it as a promt, it still refreshes automatically. The problem is the refresh of the valuation list takes up to 5 minutes, I don’t want users to wait that long each time they refressh the report.

Any ideas?

stefan :de: (BOB member since 2008-05-30)

8

@Stefan:

Some Options:

  • Make sure the table column on which the LOV is based, has a DB index.
  • Or create a single column table just for your LOV, which just has the distinct values. Then base your LOV on that special table.

Andreas :de: (BOB member since 2002-06-20)