user3298072 user3298072 - 2 years ago 405
SQL Question

How to create Dynamic SQL in Pentaho Report Designer?

I have a requirement where columns of the SQL will be generated based some selection, these columns are coming from

dimension but there is no metric ID present in Fact table.
SQL would be something like below:

SELECT Location_Nbr, Day_Nbr, (Column_List- Coming from some other query)
FROM (Table_Name - Coming from same query which is providing Columns to be selected)

I tried creating the SQL query but I get an error saying there is not a business relation between these tables. Is there any other way to achieve this?

Answer Source

To use Dynamic SQL in Pentaho Report Designer, you need to use

custom jdbc connection in data source

then in master report query name, select message format

type your dynamic query in message Pattern

$(dynamic_data) as 'data' from table_name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download