What is the preferred method of building a dynamic LOV for a prompt page?

My current way of doing things causes relatively long waits for the user.

Step 1. Query table to summarise a field into a LOV
waiting...
Step 2. User chooses from LOV and filters da
waiting....
Step 3. User sees report.

------------------------------
Simon S
------------------------------

#CognosAnalyticswithWatson

The answer you're looking for is almost totally dependent on the size and structure of the table from which you are extracting the LOV.

It's not always feasible, but avoiding tables altogether can be helpful. I have several reports where I generate a picklist of dates, either the last ten years, or the last 36 months, using custom SQL and database functions to calculate the list.

If you have to extract the values from a table, however, using a SELECT DISTINCT against a column that has been indexed will give you the quickest result, and you may do better with using custom SQL rather than going through a framework.

------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------

Thanks for the response.

------------------------------
Simon S
------------------------------

Original Message:
Sent: Mon September 20, 2021 02:53 PM
From: Michael Walter
Subject: Performative prompt pages

The answer you're looking for is almost totally dependent on the size and structure of the table from which you are extracting the LOV.

It's not always feasible, but avoiding tables altogether can be helpful. I have several reports where I generate a picklist of dates, either the last ten years, or the last 36 months, using custom SQL and database functions to calculate the list.

If you have to extract the values from a table, however, using a SELECT DISTINCT against a column that has been indexed will give you the quickest result, and you may do better with using custom SQL rather than going through a framework.

------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------