I was recently building a demo for our website and I needed to create a drop-down list of products for my user to select from a report. I couldn’t use the built-in drop-down list feature as my list was not optimized (The table has over 70K records!). Additionally, I wanted to create the drop-down list to also display a number next to each product that served as a counter indicating how popular that product was in the data set. In the end, the user will see a list of products, along with a number that indicates how many times that product appeared in the result set.
My first step was to determine what table my query was going to be built over (The Sales History table (DMSDP100)), and to determine the field used in my drop-down list (Product Number (PRDNO)).
Since I knew the built-in drop-down list would not work for my scenario, I decided to create a Web 2.0 Option List. I plan on overriding the SQL statement, so I’m not going to worry too much about which table I will select. However, I need to be sure to select 2 fields of the same type [ For instance if your drop-down list is going to contain two character fields, be sure to select 2 character fields]. I’ll Select 1 of them as a sequence key and build the application. Next, I’ll head over to Application Properties and click the “SQL Statement” tab. This is the SQL statement I used for my override:
select PRDNO , trim(PRDNO) || ‘(‘ ||sum(1)||’)’ from MRCWORKLIB.DMSDP100 group by PRDNO order by PRDNO
Notice that I am grouping my statement by PRDNO, ordering by the same to ensure the list appears in the correct order. I am also utilizing a sum(1) statement that will give me a running total for each product number.
Back under Program Options, I selected the first field listed for the “list_value” option, and select the second field listed for the “list_description” option. Click OK.
When I added my external drop-down to the report, it looked like this:
Ultimately this means that Product “A1000” had 3,295 items associated to it. This information may be quite valuable for my user when selecting items on the drop-down list!