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

Thread: specifying columns for pie chart in CDE

  1. #1
    Join Date
    Dec 2012
    Posts
    9

    Default specifying columns for pie chart in CDE

    I need to create a pie chart that includes selected columns from my data set.
    From my sql dataset, I am getting 4 columns. I need to specify the columns to pick for the pie chart in pentaho CDE. How could I do that?

  2. #2
    Join Date
    Jan 2013
    Posts
    135

    Default

    Hi vipin.goyal,
    you have to set Output options in data sources for the needed query. Index is the number of column, which should be displayed. I mean, it starts with 0 ...so if you add 1, the 2 column values will be displayed.
    Regards.

  3. #3
    Join Date
    Dec 2012
    Posts
    9

    Default

    Hi timful83,
    Thanks for your reply. I guess my questions is not properly explained earlier.
    I have a query resulting into 4 columns. 3 columns are to be used for bullet chart and 2 for pie chart. Is there a way to specify column name or index at component level for the respective charts.

  4. #4
    Join Date
    Jan 2013
    Posts
    135

    Default

    Hi vipin.goyal,
    now I can understand. I was looking for that but I switch to 2 different datasources with same SQL query and different columns. I know, it is not optimal but it works. Maybe someone has an idea how to make it better.

  5. #5
    Join Date
    Mar 2012
    Posts
    642

    Default

    Can you give an example of such a resultset and of how it would map to each of the charts?

  6. #6
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi Forum,
    I need to create 2 bar charts from single query result set of 3 columns.. Is it possible in CDE ?
    1,2,3
    1,2 ----> Chart1
    1,3------> Chart 2

    1 is on X-axis and 2,3 are Y axis respectively ?
    Can some one please share your knowledge ??

    Thanks,
    Sadakar

  7. #7
    Join Date
    Mar 2012
    Posts
    642

    Default

    Hi,

    please be aware that specifying the same data source in two components does not make them fetch a single copy of the data set, at the client-side. However, you will benefit from CDA cache.

    There are basically two ways of selecting the columns to be used in a chart:

    1. Use the "postFetch" handler to change the data set, before it is given to the chart
    2. Use the CCC chart's readers to select desired columns


    Option 1 should be avoided at all costs, cause it requires you to know and hold to the specific format and intricacies of the data source format.

    Option 2 is the preferable way.

    There's one catch though. The bullet chart is one of those charts whose update to CCC2 was minimal; it doesn't yet support user defined readers.
    If, like you stated on your first message, you'd like that all three columns be supplied to the Bullet chart, then you'll have no problem, cause there's no configuration needed for the bullet chart. If, otherwise, only one of the value columns is to be supplied, then option 1 must be used.

    Most every other chart supports Option 1.

    Assume a data set, with no Series, and the following layout:

    | Category | Value1 | Value2 |


    Bullet Chart
    In the postFetch handler, do:
    Code:
    function (cdaData) {
        var valueColIndex = 2;
    
        // Remove metadata column
        cdaData.metadata.splice(valueColIndex, 1/*remove count*/);
        
        // Remove resultset column, from each row
        cdaData.resultset.forEach(function(row) {
            row.splice(valueColIndex, 1/*remove count*/);
        });
    
        // Return modified cda data set
        return cdaData;
    }
    Bar Chart
    Specify the following CDE properties:

    • crosstabMode = false
    • seriesInRows = false -- the default


    And, in the preExecution handler:
    Code:
    function() {
        this.chartDefinition.readers = [
            // First column is the category dimension
            // Could also be written as: {names: 'category', indexes: 0},
            'category',
        
            // Nth column is the value dimension
            {names: 'value', indexes: 1 /* Value1 */},
    
            // Ignore any other columns (prevent auto/ reading for other roles)
            {indexes: 2 /* Value2 */}
        ];
    }
    If, on the other hand, you'd like to also read Value2, but only to show it in the tooltip, for example, then, just read it into a some other dimension name (not "value2"!).
    It won't be automatically bound to the "value" visual role and it will show in the tooltip:
    Code:
    function() {
        // This line will do, as every column is being consumed, in order.
        this.chartDefinition.readers = ['category, value, businessNameOfValue2'];
    }

  8. #8
    Join Date
    Feb 2013
    Posts
    530

    Default

    Thanks a lot duarte.leao... You saved my life for the day
    Now,I'm able to use Single query with 3 columns result set.
    1&2 are on panel 1
    1&3 are on panel 2.


    Once again thank you a lot

  9. #9
    Join Date
    Feb 2013
    Posts
    530

    Default

    I used this code in pre excution

    Bar Chart -1(Pre Execution)
    function() {
    this.chartDefinition.readers = [
    {names: 'category', indexes: 0 },
    {names: 'value', indexes: 1 }
    ];
    }

    Bar Chart -2(Pre-Execution)
    function() {
    this.chartDefinition.readers = [
    {names: 'category', indexes: 0 },
    {names: 'value', indexes: 2 }
    ];
    }

    It is working absolutely fine.. but again a small issue..
    On the second chart value is coming as shown in figure for categories along with Y-axis... What is the probable cause for it ? How to overcome this ??

    issue.png

    Thank you

  10. #10
    Join Date
    Mar 2012
    Posts
    642

    Default

    I think that what's happening is that the second column (Value1) is being interpreted as a second category column.

    To prevent that the Value1 column is automatically read as an additional category dimension (named "category2"), you should, either:

    Prevent reading of the column
    Code:
    function() {
        this.chartDefinition.readers = [
            {names: 'category', indexes: 0 },
            
            // The following prevents column 1 from being read, as we explicitly map it to no dimension...
            {indexes: 1}, 
    
            {names: 'value', indexes: 2 }
        ];
    }
    Read it, but name it not like a category
    Read it into a dimension whose name is not like "category2", "category3", etc.:
    Code:
    function() {
        this.chartDefinition.readers = ["category, myOtherMeasure, value"];
    }
    This way it won't automatically bind to the "category" role. By default, the "category" role is bound to by every dimension whose name starts with "category", optionally followed by a number.

    Read it, but explicitly bind the "category" role
    to the first "category" dimension, by specifying the chart option:
    • categoryRole = "category"


    By default, it would be as if «categoryRole = "category, category2"» had been specified.

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 - 2017 Pentaho Corporation. All Rights Reserved.