Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Date parameter doesn't work

  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Default Date parameter doesn't work

    I am hoping someone can help me. I am not IT savvy.

    I have a SQL database and I am trying to use CDE to analysis the data. The data are all financial transaction with date at which each transaction was posted, and it is stored as a date. But when I building the dashboard and specify in my CDA query that the "Posting_date" field is a "date" it doesn't work. When I pick a date, it returns zero items. But when I set the parameter as a "string" I am able to pick a date on my dashboard and it gives me all transaction that matches that string.

    What I need is be able to get CDA to understand that it a date, and therefore when I pick a date range, it should give all data that falls in that date range.

    I've been up all night trying to get it to work and hope you can't help


    Thank you very much

  2. #2
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    A date parameter, for CDE, is a string.

    Change your query to handle strings, doing a cast to date or something like it
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  3. #3
    Join Date
    Jul 2012
    Posts
    4

    Default

    thanks very much. this is what i have... very new to this whole thing. what do i do

    below are my queries:

    I want to use the first query as my date or year picker, and the second query should display the data that falls within the selected date range on the dashboard. how do i go about it?

    1.
    SELECT DISTINCT posted_date AS Year
    FROM financial_trxn
    ORDER BY posted_date

    2.
    SELECT g.glcode_value AS "Account", c.coa_name AS "Name",
    SUM(case when f.debit_credit_flag = 0
    then f.posted_amount * 1 else f.posted_amount * 0 end) Debit,
    SUM(case when f.debit_credit_flag = 1 then f.posted_amount * 1
    else f.posted_amount * 0 end) Credit,
    (SUM(case when f.debit_credit_flag = 0
    then f.posted_amount * 1 else f.posted_amount * 0 end) -
    SUM(case when f.debit_credit_flag = 1 then f.posted_amount * 1
    else f.posted_amount * 0 end)) "Closing Balance"
    FROM financial_trxn f, gl_code g, coa c
    WHERE f.glcode_id = g.glcode_id
    AND g.glcode_id = c.glcode_id
    AND f.posted_date = ${year}
    GROUP BY g.glcode_value
    ORDER BY g.glcode_value

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.