Auto-Complete Multi Select Query for Parameters in JasperServer

[david.garza]

Hi!

I have a multi-select query parameter in JasperServer which performs a query against a database of 500,000+ unique records returning a single value for the user to select one or more. I simply cannot query for 500k records each time the report is open. Is there another option such as a auto-complete as your type in the value whereby performing a where lookup on the value column and all of the visible columns?

Thanks!

[mlopez_1]

Only if you can clasify these records in some way, then create a query that depends on another query, or querys, and its input control for that query/s.

Mariano

[david.garza]

Mariano,

Thank you for the response. So if I understand you currently, you are suggesting that I create cascading parameters setup. In my case, I would create two inputs:

Loan # (single text entry field)
Multi-Select Query using Loan # entered as a SQL like filter parameter

For the multi-select query, I would return no results if Loan # is empty otherwise I would return TOP 20 (e.g. SELET TOP 20 LoanNumber...) which dynamically updates as the user types in a loan number. I would use the selected value(s) of the Mutli-Select query in report query rather than the "filtering" Loan #.

Does that sound accurate?
 

[mlopez_1]

That's right, even you can control, in your second query, when first parameter is large than X characters and if not return empty.

Mariano