Display data for multiple report parameter values in one table

[ndyaanzr]

Hello,

I'm facing an issue with a report parameter called item_id, which is of string data type. I currently have two item_id values, payItem1 and payItem2. Each of these item_ids corresponds to different data, but they use the same dataset.

Is there a way to input both item_ids into the report parameter so that the report displays all data for both item_ids in a single table? The table should display the data as shown in the example below.

[hvbtup]

AFAIK this can only be solved with scripting, and it doesn't support eg. selecting multiple values from a list in the parameter form.
I'll sketch the idea:

Enter your values comma-separated, for example: payItem1,payItem2.
In the beforeOpen event of your data set:

use the JS split method to split your CSV list into a JS array.
Given that you access a JDBC dataset, you probably want to generate an IN clause.
You can modify the SQL query text using this.queryText to generate something like
where t.item_id in ('payItem1', 'payItem2')
based on the array.

In order to avoid pitfalls caused by result set caching, your query should contain a dummy clause using the report parameter as a DS parameter, e.g. where ? is not null.

Be warned that this opens the door for SQL injections, so filter and escape your parameter input carefully!

[speckyspooky]

Additional hint according to the answer of Henning you can split the values in SQL too.
It is dependent on your database on MS SQL you can use function "STRING_SPLIT", on Oracle you can use a CTE, on PostgreSQL you can use "SPLIT_PART" to get the values of a string divided by on a specific delimitor.
So you can avoid to create the WHERE-clause on script level.

[ndyaanzr]

I'm working with a scripted data source. Can I still apply the idea you mentioned? If so, how can I do it? Sorry, I'm not very experienced with this.

[hvbtup]

Of course. But if you are using a scripted data source, it is up to you how your DS uses its parameters.