Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: use sql query in Timeplot

  1. #1

    Default use sql query in Timeplot

    hi all
    I try to use sql query with 2 cols( date, number), but it didn't work
    Then I check the timelinefeeder.xaction, I found

    <pivot_column>1</pivot_column>
    <measures_column>3</measures_column>
    <sort_by_column>2</sort_by_column>


    But I don't know by example what are the pivot_column and the sort_by_column correspondant
    Since there's no pivot_col in my query, I delete <pivot_column> and <sort_by_column> , but the error showed they are necessary.
    Finally, I delete the whole <action-definition> of this , the timeplot showed.



    Regard,
    Chun
    Last edited by frankslin; 08-19-2009 at 05:13 AM.

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

    Default

    Hum... All you should need to do is copy the timelinefeeder.xaction.sql to overwrite the one that comes by default. Then it should just work
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  3. #3

    Default

    hi Pedro

    I've already overwite the file.......

    In the timelinefeeder.xaction.sql there's a

    <component-name>ResultSetCrosstabComponent</component-name>

    in this component, it definit this

    <pivot_column>1</pivot_column>
    <measures_column>3</measures_column>
    <sort_by_column>2</sort_by_column>

  4. #4
    Join Date
    Feb 2009
    Posts
    321

    Default

    hi, I have the same problem I cant generate a "timeplot" ... I try with 3 colunms
    my datasource:

    select
    idfecha (string),
    ,id_tie_fecha (date)
    ,qty ( number)

    but doesn´t work

    anyone can help me?
    thanks in advance

  5. #5
    Join Date
    Nov 2009
    Posts
    28

    Default

    Hi!
    Here's a url describing the ResultsetCrossbar-component.
    http://wiki.pentaho.com/display/Serv...ltset+Crosstab

    But I think the information here isn't correct case I still couldn't get it to work.
    So after some testing I got it to work.
    The SQL needs to have three columns (used by the ResultSetCrosstabComponent) which doesn't correspond to the information on the url.
    Col 1: the pivot as stated.
    Col 2: the date (I hade to do a DATE() since I for some reasons got a ".0" att the end of the timestamp)
    Col 3: the measurement

    You may use a column for pivot but observe that it will not display unless you add more values for columns in the metalayer.

    I used this SQL in the component sample to try it against the SampleData.
    Code:
     
     var query = "select 'CUSTOMERNUMBER', DATE(PAYMENTDATE),  AMOUNT from payments";
    If you try it out I in the component sample you will get a script-error after you've pressed the "Try me"-button. But just stop the script and refresh the page and the result will diplay. Don't know why this happens but at least it works

    Br,
    Andreas

  6. #6
    Join Date
    Feb 2009
    Posts
    321

    Default

    hi.. I taked the script


    var query = "select 'CUSTOMERNUMBER', DATE(PAYMENTDATE), AMOUNT from payments";
    but when I replace in the sample dont work any more...

    anyway I got this in a cdf editor for a timeplot

    NametimePlot_testTitleTimePlotListeners[]Datasourcefor_timePlotExecute at startTrueHtmlObjectPanel_1Pre Execution
    ...
    Post Execution
    ...
    Tooltip
    ...
    Columns names["importe_facturado"]Height - Width - Show ValuesTrueHide Zero ValuesFalseShows dosTrueShows a color gradient on the plotTrue

    in my datasource I got... id_tie_anio (string), id_tie_fecha (date), importe_facturado (numeric)

    SELECT
    cast(id_tie_anio as char) id_tie_anio
    ,id_tie_fecha
    , round(sum(imp_itemtotal)) importe_facturado
    FROM ft_detalle_ventas f,lk_tiempo tie, lk_producto pro
    where f.id_tie_fecha_emision = tie.id_fecha
    and pro.id_pro_producto = f.id_pro_producto
    and tie.id_tie_fecha between STR_TO_DATE('2006-01-01','%Y-%m-%d') and STR_TO_DATE('2009-12-01','%Y-%m-%d')
    group by tie.id_tie_fecha
    order by id_tie_anio asc

    what I doing wrong?

    anyway andreaT
    thanks for all post

  7. #7
    Join Date
    Nov 2009
    Posts
    28

    Default

    Strange... it works for me in the sample (at least after pushing F5).
    And I also got it working in Dashboard Editor.

    I've attached my file if you want to try it.

    Do you get any errormsg with your sql?

    one thing you need to keep in mind is that if "id_tie_anio" contains for example 10 different unique strings then you need to have 10 columns defined to show them all. That's because ResultSetCrosstabComponent creates new colums in the resultset for each unique values in column 1.
    So if you only define one column and that column after ResultSetCrosstabComponent don't have any values you won't see anything.

    Hehe, regarding all my posts I can just say I got too much time on my hands
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2009
    Posts
    321

    Default

    mmmmmmmm doesn´t work... mmmm must be a component, please attach your timelinefeeder.xaction please..... another question I run with java 5.0... is ok ? I think yes..but tell me

    thanks Andrea ... I´m not very good in dashboards, is very new for me... my background is only PDI and reporting...

    regard
    Hernan

  9. #9
    Join Date
    Nov 2009
    Posts
    28

    Default

    OK, here's my xaction-file. I've havn't modified it at all so it's strange.
    My Java:
    java version "1.6.0_17"
    Java(TM) SE Runtime Environment (build 1.6.0_17-b04)


    Br,
    Andreas
    Attached Files Attached Files

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.