Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Several charts with different queries in the same report, help!

  1. #1
    Join Date
    Feb 2007
    Posts
    29

    Default Several charts with different queries in the same report, help!

    Hi , I need to display several charts in the same page but with a different query for each, so a different dataset. Is it possible to do it with Pentaho Design Studio?
    Thanks a lot.

  2. #2
    Join Date
    Dec 2005
    Posts
    531

    Default

    Hi,

    you could have a look at the Dashboard examples. Each item (chart report etc.) is based on a different action sequence. But to implement a dashboard you need to create a new JSP page and call you action sequences from there. You can have a look at :

    Pentaho Dashboard Buildinghttp://downloads.sourceforge.net/pen...6&big_mirror=0
    or
    Pentaho AJAX Guidehttp://downloads.sourceforge.net/pen...3&big_mirror=0

    You could also experiment with the new subreport features that are part of JFree Report 0.8.8 and Pentaho 1.5.3 I believe, but they are not supported by the report designer, yet.

    Hope that helped.

    All the best,
    Ingo

  3. #3
    Join Date
    Feb 2007
    Posts
    29

    Default

    Hey thanks a lot, that is exactly what I did, but 35 charts in the same page=> 35 xaction files, but it works anyway.
    I have another problem : do you know how to create a stacked chart bar?
    Cheers.

  4. #4
    Join Date
    Dec 2005
    Posts
    531

    Default Stacked bar chart

    Hi,

    you should be able to set a chart attribute in the bar chart definition xml part. I think it is called: <is-stacked>true</is-stacked>

  5. #5
    Join Date
    Feb 2007
    Posts
    29

    Default

    I tried to use it but I did not get what I want.

    Indeed, with the following query
    select type1,
    type2,
    count(call_req.id) "NB"
    from
    call_req, ctct
    where
    call_req.group_id = ctct.id
    group by type1,type2,
    order by type1,type2

    and the property <is-stacked>true</is-stacked> I have a chart (see image1) which displays only NB splitted by type1.

    But what I want is NB splitted by type 1 AND type2 (see image 2).
    Can you help me please?

    Thanks a lot.
    Attached Images Attached Images   

  6. #6
    Join Date
    Dec 2005
    Posts
    531

    Default

    Hi,

    try changing your query. Something like this should work for sure.

    Select
    type2,
    count all I,
    count all R, ...

    Basically you need one domain column (type2) and one count column per type1.

    All the best,
    Ingo

  7. #7
    Join Date
    Feb 2007
    Posts
    29

    Default

    Hey it works!!! Thank you so much to answer so fast, you're the best.

    Best Regards.

  8. #8
    Join Date
    Feb 2007
    Posts
    29

    Default

    Another little question do you know if it is possible to have the figures on the stacked bar as in image2?
    Thanks a lot.

    Tanny

  9. #9
    Join Date
    Feb 2006
    Posts
    10

    Question stackbar dataset for dynamic data

    Quote Originally Posted by ingo.klose View Post
    Hi,

    try changing your query. Something like this should work for sure.

    Select
    type2,
    count all I,
    count all R, ...

    Basically you need one domain column (type2) and one count column per type1.

    All the best,
    Ingo
    How do you manage your solution if you don't know much items are in type1?

    Is it possible to build a crosstab with a report engine and use the result as a dataset for the stackbar chart?

    Anyone knows a nice solution?

    Thanks.

    XS

  10. #10
    Join Date
    Dec 2005
    Posts
    531

    Default Dynamically construct SQL statement

    Hi,

    you can use a SQLLookupRule and a JavascriptRule action to construct the query dynamically.

    There is a tech tip that describes the concept in detail and how you can use it to create reports:

    http://wiki.pentaho.org/display/COM/...port%2C+Part+1
    http://wiki.pentaho.org/display/COM/...port%2C+Part+2

    But in short you need to do the following in your action sequence:

    1. Get all Items in type1 with an SQLLookup query (output poarameter = query_resultset in this example)
    2. Use the result set in a JavascriptRule to construct the query and pass it on as output parameter

    Code:
    xTabQry =" SELECT type 2";
    for (var i=0; i <query_resultset.getRowCount(); i++) {
      
      //Here goes the syntax for the count using query_resultset.getValueAt(i,0) 
      // e.g xTabQry +=" sum(if(type1 = '" + query_resultset.getValueAt(i,0) +"',1,0)) as metric_" + i;
    
    }
    xTabQry +=" FROM ....."
    3. Use xTabQry as query string in the actual SQLLookupRule that gets the data for the chart.

    All the best,
    Ingo

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.