Default behaviour of OBIEE to display 256 records in prompt for USER INPUT : Choice List. Then you click it will bring 256 more. If your data set is constrained with other columns or column itself comes from huge table it will load for endless time.
If you need to do this, edit the instanceconfig.xml file. This file is located at …
Add the MaxDropDownValues parameter to the file. If you do not already have a Prompts section, add it, and then add MaxDropDownValues between the Prompts tags. And make sure that all this is between the ServerInstancetags which will already be there in the file.
Your config file will look something like this …
Of the 500 LOVs in my prompt, 15 records have special characters (ä, ö, ü).
1) When I try to select all values in the prompt it shows around 256 values then later if I click 'More' it shows 'Loading' and keep showing the same for hours.
2) The session log shows all 500 values retrieved. so does this mean the prompt is not able to render all values.
3) When i run a ad-hoc analysis I see all 500 values retrieved with special characters as is..
4) While in prompt out of the 256 values displayed on the prompt, which ever record has special character (ä, ö, ü) is replaced with ??? in the prompts selection window.
5) I have other column prompts which when clicked on more shows 1000+ (all records) in the selection pane.
Is this something to do with Order by Clause which causes performance degradation? Any workaround?
Please have a look at this.
1)Check whether your prompt column is constrained with other columns in the dashboard prompt
2)make sure that it is not pulled from a fact table
3)If both of the above statement is good in your case , query the data base with "Select distinct column from table and not down how much time it is taking to retrieve all values.
4)I don't think that first 256 comes faster and next 256 is very slow. Clear all your cache and execute it again. if it is slow it will be slower for first 256 as well.
5)Check for any complex SQL written in Prompt select list or default value box.
This much only i could think of now.
Mark if it helped you