Dynamic SQL in Report Designer

• Open Pentaho Report Designer
•  
•  
• Create a new parameter 'text box', call it 'query'.
• Add a data set 'Advanced' -> 'JDBC(Custom)'
• Select the database connection (in our example SampleData)
• Under the 'Structure' tab, click on 'Master Report' and then in 'Attributes' define the query name using formula

• Under the formula define the following expression:

  =[query]

• Done! Now the query that the user enters into the 'query' parameter box, will be executed against the specified connection.
• To show the results of the query, you will need to define the fields on the report. The user will need to name the selected values according to the fields available on the report. This can be done by using 'SELECT … AS FIELD FROM ...' if FIELD has been defined on the report.

download All Done from here
Download