pentahopentaho-cde

pentaho: Error processing component : how to set parameter for piechart?

I want to make a dynamic piechart (that depends on date parameter) on dashboard on CDE. First, here's an example of my BD:

    ID    date       category
   ----------------------------
    1   2019-01-01    positive
    2   2019-01-01    positive
    3   2019-01-01    negative
    4   2019-01-01     zero
    1   2019-02-01    positive
    2   2019-02-01    zero
    3   2019-02-01    negative

On datasource panel, I added a query below:

select category, count(category) from table where date=${date} group by category;

Then on component panel, I did : enter image description here

On Datasource panel, I set the parameter in the same way: enter image description here

But the piechart doesn't show and the error message is : Error processing component If I replace the parameter ${month} with a value like '2019-01-01', the pie chart does show without problem.

I followed multiple tutorials but can't resolve my problem.

Is it because of date format ?

This is how I set date parameter on datasource panel:I also tried with Date instead of String... enter image description here

Solution

  • My experience is that treating CDE parameters as strings and then converting them to date in the SQL query works best and gives you the most control.

    MySQL example: 

    where STR_TO_DATE(${DATE_FROM}, '%d-%m-%Y') = MyDateColumn

    In my case, I needed to show "dd-mm-yyyy" format in the dashboard and internationalization is rather broken in CDE, so I went with string parameters and the above contains the MySQL date format for it. If you use a different DB, look up the date conversion function in the docs.