CODEWITHSUNDEEP
datasourcepentahoprepared-statement
Vladimir Grishutin
Vladimir Grishutin

Reputation: 33

Pentaho parametrized datasource

I am trying to build some parametrized data source (sql query over jndi). query of my data source is:

SELECT ${param_interval}(dim_date.date), count(docs_fact.id) as docs_count
FROM rel_docs_dates
left join docs_fact on rel_docs_dates.doc_id = docs_fact.id
left join dim_date on rel_docs_dates.date_id = dim_date.id

Parametr ${param_interval} can get two values: MONTH and DAY, and as i checked it got the correct values. But when i am trying to make preview of my dashboard i get warning "error processing component".

Notice that this query (see bellow) works ok.

SELECT MONTH(dim_date.date), count(docs_fact.id) as docs_count, ${param_interval} as tmp_fiel
FROM rel_docs_dates
left join docs_fact on rel_docs_dates.doc_id = docs_fact.id
left join dim_date on rel_docs_dates.date_id = dim_date.id

Can somebody tell me where is mistake? Or (may be) this way to use parameters in data source is not supported?

Upvotes: 0

Views: 951

Answers (1)

Vladimir Grishutin
Vladimir Grishutin

Reputation: 33

finaly i found decision. it isnt what i would like to have but it works and it the most important thing.

i rewrite my query with 'case' constraction and, it is important, i changed type of my parametr from string to numeric (string doesnt work :( ). now my query looks like this:

    SELECT 
    case ${param_interval}
    when 1 then MONTH(dim_date.date)
    when 2 then DAY(dim_date.date)
    end
    ,count(docs_fact.id) as fact_count
    FROM rel_docs_dates
    left join docs_fact on rel_docs_dates.doc_id = docs_fact.id
    left join dim_date on rel_docs_dates.date_id = dim_date.id
    where  dim_date.date > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL ${param_period}  MONTH))
     AND dim_date.date < LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 0 MONTH)) 
    group by
    case ${param_interval}
    when 1 then MONTH(dim_date.date)
    when 2 then DAY(dim_date.date)
    end
    order by YEAR(dim_date.date), MONTH(dim_date.date)

may be it will help somebody else.

Upvotes: 1

Related Questions