There is another way of going about this if you want the users to be able to select any supplier_name that exists in the DB, but only return the ending data if it exists in your final query (Query1). If that's the case, let me know and there is a similar, but slightly different setup in the Select & Search Prompt.
Original Message:
Sent: Tue February 21, 2023 05:08 PM
From: Logan Whitaker
Subject: Limiting a prompt to entries containing characters typed in
Here's one way of doing so. I assume your underlying query is filtered on something so that you don't want to return all data in the DB. So you may want to limit your prompt to only the values for which there is data in your query to be returned. I'll use an example where a report has all orders in the last 30 days by supplier name and we want to allow the user to type in any part of a supplier name; select the names that are available in the data; then commit the prompt to return only that supplier's orders.
Let's say Query1 has two columns: supplier_name and order_number. Query1 has the data you want to fitler upon and display after the prompt is commited. Create Query2 using the same filters you use to generate the list for Query1, but only include columns for supplier_name so that Query2 is pulling a distinct list of supplier names for that same list of orders in Query1.
Now on Query1, put a filter on it saying supplier_name in ?test_prompt?. Now go to your prompt page and add a Select & Search Prompt. Choose "use existing parameter" and select "test_prompt" that you created earlier (it'll be in the drop down list). On the next page of the pop-up, uncheck "create new query". Now in the properties panel for the Select & Search Prompt, go to Query and select Query2. Set "use value" to your supplier_name as well as for "display value". Can even sort by this too if you want.
Now when you run the report you'll get the prompt like this to choose from the supplier_name values available in your underlying data. This prompt is pulling the supplier_name values from Query2.
When the prompt is committed it will pass your selected supplier_name values to Query1 which generates your report page.
------------------------------
Logan Whitaker
Original Message:
Sent: Sun October 02, 2022 08:45 AM
From: Stephan Quintelier
Subject: Limiting a prompt to entries containing characters typed in
Hi All,
I'm stuck ☹, I need an expert, …
I'm looking for a prompt that displays items in a list, based on one (or more) characters, entered in a prompt box (which is on the same page).
For example typing "lf" will display all products with "lf" in the name (Golf, …), so that the user can select one, which can then be passed to a list.
I found the custom control "HTML data list with text filter (11.1.7 and above)" in the Accelerator Catalog, but to get it to work I need the configuration and I can't find the sample on our system.
Can anyone send me a sample configuration for this custom control? Or is there another way to solve "limiting a prompt to entries containing characters typed in"?
I've also tried to use the "Custom JavaScript in Cognos – Searchable Checkbox List" from Paul Mendelson in the article Custom JavaScript in Cognos - Searchable Checkbox List - PMsquare, but the prompt is very slow. When typing in a character, I first get a javascript error and then the character displays (after a long time).
Can anyone help me out?
Thank you VERY much for your answer,
Stephan
------------------------------
Stephan Quintelier
------------------------------