Search code examples
sqllistparameterspentahopentaho-report-designer

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.

Solution

  • 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.
    Eg: 

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

    or

        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.