AS91 AS91 - 10 months ago 130
SQL Question

Pentaho Report Designer: Passing list of values as parameters to report

Report Objective: Performance analysis of players by statistics comparison

Desired Report Layout

Please refer to the layout and read on. The player names need to be added from a list which could be anything like entry text box, multicheck box, dropdown etc. If I select Jake Tyler in this list, the report should refresh and show me the statistics from him. Then when I select Adam Smith, he should show up as the next entry below Jake with his respective stats.

I know how to pass individual players as parameters in the query using Pentaho parameters and tagging them in the condition using SQL as:

'where PlayerName = ${playername}'

But I need to know how to pass multiple player names in a similar fashion to generate this report using multivalue String parameters.

Can you please please guide me on how to do this? I have heard things like x-actions which could work but I don't know how to use that. I am sure this will help a lot of people who are trying to achieve something similar which might seem complex to them.

Answer Source

You can simply use where PlayerName IN (${playername}). The list should be correctly passed from the parameter to query. and the parameter also should get data correctly.

    select 'Jake Taylor' as pn
    select 'Adam Smith' as pn
    select 'Chris Lawson' as pn


    select distinct column_name from table_name

this can be sent to parameter (in Add parameter window) and your main query can be prepared as I explained above using IN

NB: You can use only Display types like: Multi value list, Multi selection box etc. Not drop downs which pass only single value.