CODEWITHSUNDEEP
sqlpentahopentaho-report-designer
Bruno Gama
Bruno Gama

Reputation: 11

Pentaho Report Designer (PRD): Use parameter in select clause

In the report I'm working with, I need to display information of four columns of a database table. The first three columns of the table are SEX, AGE and NAME. The other N columns (N being like 100!) are questions, with every line of the table meaning that person's answer to that question:

SEX | AGE | NAME | Q1 | Q2 | Q3 | ... | Q100

In my report, I need to show four of these columns, where the first three are always the same and the fourth column varies according to the option selected by the user:

SEX | AGE | NAME | <QUESTION_COLUMN>

So far I've created a dropdown parameter (filled with "Q1", "Q2", "Q3", etc) where the user can select the question he wants to compare. I've tried to use the value of the selected option (for instance, "Q1") in the SELECT clause of my report query, without success:

SELECT sex, age, name, ${QUESTION} FROM user_answers

Pentaho Report Designer doesn't show any errors with that, it simply doesn't show any values for the question column (the other columns - sex, age and name - always return their values)

So, I would like your know:

  1. Can I do this? I mean, use parameters in the SELECT clause?
  2. Is there any other way have this "wildcard" column according to a parameter?

Thanks in advance! Bruno Gama

Upvotes: 1

Views: 8193

Answers (2)

MNessa
MNessa

Reputation: 48

I am using SQL server as database. This problem solves like this :

execute('SELECT sex, age, name, '+${QUESTION}+' as Q1 FROM user_answers')

Please note that ${QUESTION} must be a column name of user_answers. In this example I used a text box parameter name QUESTION where column name is given as input. You may need other coding if input parameter is not text box.

Upvotes: 1

Charels_xie
Charels_xie

Reputation: 11

you can use the pentaho report design to design.

  1. First,you must bulid the param "QUESTION"on the paramers eg: SELECT question FROM user_ansewers order by XXXX

  2. you can use the sql

    SELECT sex, age, name,question FROM user_answers where question= ${QUESTION}

  3. last ,you can see the "drop down" to realized the choose

Upvotes: 1

Related Questions