pentaho cde include/exclude where clause in sql query

Question

I am building a Dashboard using Pentaho CDE, in which a table will show data from a query like this :

select * from types where id = ${id}

Now i get the id from url parameter like this

id = Dashboards.getQueryParameter('id');

If I dont give the id in url, the table shows nothing as id='' doesn't match anything. But what I want to do is, if I dont give id it will exclude the where clause and show a result of query like

select * from types

How to achieve this in pentaho CDE ?

Answer 1

You can use two datasources and choose one during the Pre Execution phase of a table. The datasource of a dashboard's component (table, chart) is represented by a property dataAccessId.

You can set the dataAccessId using JavaScript code inside a component like this: this.chartDefinition.dataAccessId = {datasource name}.

1) Datasource sql_with_id:

select * from types where id = ${id}

2) Datasource = sql_no_id:

select * from types

Table's Pre Execution code:

function f() {
   var id = Dashboards.getQueryParameter('id');
   if (id && id !== "") {
      this.chartDefinition.dataAccessId = "sql_with_id";
   } else {
      this.chartDefinition.dataAccessId = "sql_no_id";
   }
} 

Answer 2

How about creating a custom javascript parameter (say, 'where_clause') a bit like this:

id = Dashboards.getQueryParameter('id');
if (id != null)
    where_clause = 'id = ' + id;
else
    where_clause = '1=1';
 return where_clause;

And then build your query:

select * from types where ${where_clause}