2

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.

Sean Branchaw
  • 597
  • 1
  • 5
  • 21
AS91
  • 527
  • 7
  • 18

1 Answers1

1

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.

Marlon Abeykoon
  • 11,927
  • 4
  • 54
  • 75
  • I have a situation where the firstname and lastname of the player are in different columns. If now I want to pass these two fields together as the list in parameters, how do you suggest I do this? For parameter: `select distinct concat(fname, ' ', lname) AS player_name from player_table` Main Query: `select fname, lname from player_table where concat(fname, ' ',lname) IN (${player_name})` Output is 1 row of 1 player. I get desired result if I input names directly as: `IN('Adam Smith', 'Chris Lawson','Jake Taylor')` But not with the parameter, Cld u suggest a fix plz? – AS91 Nov 10 '15 at 03:44