Hitachi Vantara Pentaho Community Forums
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Order by on bar chart with out touching the query in CDE

  1. #1
    Join Date
    Feb 2013
    Posts
    530

    Default Order by on bar chart with out touching the query in CDE

    Hi forum,

    Is there any functionality available with in CDE to make the bars on bar chart in order by with out touching the SQL query ?


    Thanks,
    Sadakar

  2. #2
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi,

    by default, dimensions of valueType String, or Any (CCC dimensions' concepts), which are necessarily discrete, maintain the order of values, as they are read from the data source.
    So, if the data source comes sorted in a similar manner to that that is represented graphically, you should get what you expect.
    For example, by default, the "category" role -- the base-axis in categorical charts, x-axis when orientation is vertical -- shows category values in the order they were read from the data source. If each category is provided in a different row in the data source, and the rows are sorted by category, then you should see categories ordered from left to right.

    You have to beware of null values. If you have multiple series, and in the first series only has the second category with a non-null value, and the null valued rows are not supplied, then the second category will end up always appearing before the first category, because, from the above rule, it showed up first in the data source.

    There is also a known issue, when you using multi-charts and data sources with null values, where the category order within each small chart may show up in a different order.
    EDIT (2014-04-11): This issue has been fixed.

    Any of these problems may be mitigated by specifying a "comparer" for the discrete dimension. This forces using a certain order, independent of the reading order.
    For a categorical chart, sort the "category" dimension by specifying a "comparer" for it in the component's "postFetch" handler:
    Code:
    function() {
        this.chartDefinition.dimensions = {
            "category": {
                comparer: function(a, b) {
                    return a === b ? 0 :
                             a > b     ? 1 :
                             -1;
                }
            }
        };
    }
    Last edited by duarte.leao; 04-10-2014 at 07:29 PM.

  3. #3
    Join Date
    Feb 2013
    Posts
    530

    Default

    Thank you for your replay. but I'm unable to sort the bars on second chart.. Plz have a look at the image below.
    issues.png

    These charts are using single query.
    Query : select x,y,z from table_name group by x order by 2 ASC,3 ASC
    Result set :
    x y z
    abc 10 3
    pqr 20 1
    mnc 30 9

    and etc.

    When I preview the dashboard, the first chart only ordering the charts but not the second one ?

    I have tried with the below code as it is in the postFetch of second chart component only.(not in the postFetch of 1st chart) but unable to sort the bars on 2nd chart


    function() { this.chartDefinition.dimensions = { "category": { comparer: function(a, b) { return a === b ? 0 : a > b ? 1 : -1; } } }; }

    Where Am I going wrong ? Could you give me a right way to get the solution ?


    Thanks,
    Sadakar

  4. #4
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi,

    I think I finally understand what you want to do. You wanna order by the value, not the category...

    There's no CCC configuration for doing that.
    You'll have to specify the CDE chart component's "postFetch" handler and manually sort the provided CDA dataset:
    Code:
    function(cdaData) {
        // JIC, create a copy
        cdaData = {
            metadata: cdaData.metadata,
            resultset:  cdaData.resultset.slice()
        };
        
        // Sort the resultset by column "valueIndex"
        var valueIndex = 2;
        var compareAsc = function(a, b) { return a === b ? 0 : a > b ? 1 : -1; };
    
        cdaData.resultset.sort(function(rowa, rowb) {
            // NOTE: nulls will be converted to 0.
            // You might need to fix this, if it is relevant to you.
            return compareAsc(+rowa[valueIndex], +rowb[valueIndex]);
        });
    
        return cdaData;
    }

  5. #5
    Join Date
    Feb 2013
    Posts
    530

    Default

    Awesome...!!!!

    Many Many Many thanks Sir, Duarte.leao

    Worked successfully


    Thank you

  6. #6
    Join Date
    Feb 2013
    Posts
    530

    Default

    After writting code in postFetch expression the data is sorting.. Now how can I dynamically restrict the number of bars on charts ?

    Before writing postFetch code I can restrict the bars by passing a number.. i.e., for instance in the query i used limit 10 where 10 is a parameter.

    If I use the same limit clause in the query I can see the sorted data on the bars but not continues order ... It's ordered by only but random order...

  7. #7
    Join Date
    Mar 2012
    Posts
    645

    Default

    Quote Originally Posted by sadakar View Post
    Now how can I dynamically restrict the number of bars on charts ?

    Before writing postFetch code I can restrict the bars by passing a number.. i.e., for instance in the query i used limit 10 where 10 is a parameter.

    If I use the same limit clause in the query I can see the sorted data on the bars but not continues order ... It's ordered by only but random order...
    Sorry, I can't understand the exact problem.
    When you add the limit clause, then the code in "postFetch", that orders by value column, does not work?
    Can you explain this better? Perhaps with an image of the resulting bar chart and a preview of the data you are receiving?

  8. #8
    Join Date
    Feb 2013
    Posts
    530

    Default

    Query eg:
    select a, b, c from table group by 1 order by 2 limit 6
    Result set eg:
    data.png
    chart output with out having limit in the query and having the second chart postFetch code.
    charts.png

    When I use the limit in the query data is sorting on the charts but random values sorting.. I've to see the same category names for 1st bar, 2nd bar, 3rd bar and etc.
    i.e., when I use limit in the query dynamically category names are not coming same for both the bar charts.

    i.e., i) I have to restrict the number of bars for both charts with the same category names.
    for ex: in the input control if I give 10 , 10 bars should appear and if I select 3 bars 3 bars should appear. the category names should be same for both the charts.


    Hope You understood my statements .. If not let me know in replay what are the inputs do I need to provide ?


    Sadakar

  9. #9
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi,

    if you want to ensure that both charts receive the same set of categories (rows in the CDA resultset), and be able to specify the LIMIT clause in the Query as well, you need to order by "category", in the Query ("order by 1").

    Then both of the charts must re-sort the rows, in the "postFetch" handler. Chart 1 sorts by the "value1" column and chart 2 sorts by the "value2" column.

  10. #10
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi..Very urget please.. waiting for your post..

    I tried with order by 1.

    The categories are displaying in order by .. but the I need the bars also to be displayed in order.

    some duplicated values are missing on the chart also...

    Plz. help me

    Thanks,

Tags for this Thread

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.