Hitachi Vantara Pentaho Community Forums
Results 1 to 23 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.
    Name:  issues.png
Views: 604
Size:  4.6 KB

    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:
    Name:  data.png
Views: 498
Size:  3.0 KB
    chart output with out having limit in the query and having the second chart postFetch code.
    Name:  charts.png
Views: 503
Size:  3.9 KB

    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,

  11. #11
    Join Date
    Feb 2013
    Posts
    530

    Default

    Is there any way to limit the number of sorted records using inside PostFretch where the number is coming from external parameter ?
    Parameter name : NO.Of.Limits=10 on the dashboard.

    This parameter values hits the metadata in the PostFetch code with sorting and have to give the first 10 values on bar chart.

    Is it possible to limit the number of sorted values using parameter value and with PostFetch code ?


    Thanks
    Sadakar

  12. #12
    Join Date
    Mar 2012
    Posts
    645

    Default

    Quote Originally Posted by sadakar View 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.
    Did you place the code I gave you before, for sorting by value, on both charts' postFetch? Did you also configure the variable "valueIndex" in each? If yes, then both charts should show bars ordered by value.

    Quote Originally Posted by sadakar View Post
    some duplicated values are missing on the chart also...
    You mean that you have more than one row in the resultset for the same category? And you would still want to see them as separate bars?
    CCC groups rows having the same category (and series), sums their values and shows a single bar. You could provide different category values and then format them to show the same text.

  13. #13
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi..
    Firstly :
    I used the code for second chart(Which displays data for 1& 3 columns) in postFetch and in the query used order by 2 for 1st chart sorting( I'm having 3 columns ).
    This way is absolutely working fine.. i.e., I can see the sorting of all the bars.

    Secondly :
    I did what you said just now post. i.e., used the code with different function names and configured valueIndex 1 & 2 respectively for chart 1 and chart 2.
    It is also working fine.

    Now, the problem is :
    When I use limit in the query the bars are coming in sorted order only for the two charts...
    But, for example:
    Resultset
    100
    69
    54
    54
    23
    39
    20
    20
    14
    10
    and etc
    But on the bars few values are not displaying on the chart
    the output of the bars is like as follows
    100
    54
    54
    23
    39
    20
    14
    10

    ** 69 missed
    ** 20 has missed ( categories are different but few are having same value in columns)
    i.e., all are distinct categories on first column and the categories may have same values like 20 and 54 are there in the example(no duplicate of categories are there in the query result set).

    these all is happening when I pass a number in the query as limit.

    So now I do not want to use any limit through the query .


    I'll create a text box on the dashboard and will enter some number for eg: 5 then , 5 sorted bars on both the charts will have to appear.
    for eg: if I enter 17 in the text box , 17 bars should appear on the bar charts.
    This I have to get with out losing any data and order of bars.

    How to use this No.Of.Limits parameter dynamically using scripting with out losing any values using scripting ?
    Is it achievable using any code in postFetch ?

    Thank you
    Last edited by sadakar; 08-27-2013 at 10:21 AM.

  14. #14
    Join Date
    Mar 2012
    Posts
    645

    Default

    Let's see if I've got it now.

    I thought your query received the LIMIT value directly through a parameter. That way you wouldn't need additional "postFetch" code to filter the resultset.

    If you want to do the LIMIT in the "postFetch" handler, you can. Combine the filtering code with that that sorts by value.

    Place this in "postFetch":
    Code:
    function(cdaData) {
        var categIndex = 0;
        var valueIndex = 2;    
        
        // Obtain the LIMIT parameter value
        // TODO: replace with actual parameter name
        var limit = +Dashboards.getParameterValue("limit"); // null to 0
        if(isNaN(limit)) { limit = 0; }
    
        // JIC, create a copy of the cdaData
        var resultset = cdaData.resultset.slice();
    
        cdaData = {
            metadata: cdaData.metadata,
            resultset:  resultset
        };
        
        var compareAsc = function(a, b) { return a === b ? 0 : a > b ? 1 : -1; };
        
        // Extract the first "limit" rows, when sorted by category
        if(limit > 0) {
            // Sort the resultset by column "category" (only needed if the original resultset isn't already sorted by category)
            resultset.sort(function(rowa, rowb) {
                return compareAsc(rowa[categIndex], rowb[categIndex]);
            });
            
            // Discard all rows beyond "limit"
            resultset.splice(limit, resultset.length - limit + 1);
        }
    
    
        // Sort the filtered resultset by column "valueIndex"
        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;
    }
    You should also set both chart components to listen to the "limit" parameter.
    Last edited by duarte.leao; 08-27-2013 at 10:47 AM. Reason: formatting

  15. #15
    Join Date
    Feb 2013
    Posts
    530

    Default

    Sorting is working fine..
    limit is working fine.

    limited the rows... code is working fine.

    My result set is :
    limit=5 in the query
    the query output is below
    Column C
    12
    39
    56
    56
    78
    On the chart the values are different but sorted and limited
    Column C
    874
    841
    170
    138
    119

    above values are part of the result set only.. but I have show the exact values that are coming from query outuput on the charts when limits

    observe the resultset when I take limit as 24( all the values from my query)
    columnC
    12
    39
    56
    56
    78
    78
    84
    119
    138
    170
    185
    192
    200
    208
    231
    279
    284
    419
    533
    841
    874
    1513
    1630
    1732

    I hope you understand my problem after applying the code given by you just now.


    Thanks,
    Sadakar
    Last edited by sadakar; 08-27-2013 at 11:19 AM.

  16. #16
    Join Date
    Mar 2012
    Posts
    645

    Default

    Quote Originally Posted by sadakar View Post
    // Extract the first "limit" rows, when sorted by category
    ...
    I need to sort by B for chart 1
    & C for chart 2
    The bold formatted comment only says that, when you extract the first "limit" rows, you have to have them previously sorted by "category", so that you get the same, for example, "top 10" categories on both charts.

    After extracting the "top 10", you sort the rows again, but by the specified valueIndex column.

  17. #17
    Join Date
    Feb 2013
    Posts
    530

    Default

    Over all the code which you have given is working fine.

    In simple words,

    When I test with limit 5 using query and input control on the dashboard.

    The values are not matching. It is picking up random values and sorting and limiting.

    Values should be when I limit 5
    Bar values = query output

  18. #18
    Join Date
    Mar 2012
    Posts
    645

    Default

    There must be something there that I'm missing.
    If you want, we can continue this issue in IRC.
    Server: irc.freenode.net
    Channel: ##pentaho
    My user is: DuarteLeao

  19. #19
    Join Date
    Feb 2013
    Posts
    530

    Default

    Okay sure... Thanks for replying for all my queries..

    last but not the least for this issue ?

    have a look at the code you given

    return compareAsc(rowa[categIndex], rowb[categIndex]);

    & afte few lines

    return compareAsc(+rowa[valueIndex], +rowb[valueIndex]);


    I didn't get with this in my case ? Can you explain in detail about this as a last post in this issue ?


    Thanks,
    Sadakar

  20. #20
    Join Date
    Mar 2012
    Posts
    645

    Default

    We really should continue this in IRC, or this thread will end up having no meaning, to other users.

    Anyway, the JavaScript unary operator "+" tries to convert its (at right) argument to a JS number. When the conversion fails, the result is the JS number «NaN». Use «isNaN(foo)» to detect it.
    If the argument evaluates to a string, and that string is "numeric", the operation will succeed.
    If the argument evaluates to null, it will be converted to 0.

    You can also convert something to a number by using: «Number(foo)» or «parseFloat(foo)». The later form even tolerates the presence of non-numeric prefixes or sufixes (ex: "100%" will return 100).

  21. #21
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi.. I found there is no issue with code given by you.
    I just done few changes. have a look at @ the code.

    function f2(cdaData) {
    var categIndex = 0;
    var valueIndex = 2;

    // Obtain the param_no_of_end_points parameter value
    // TODO: replace with actual parameter name
    var param_no_of_end_points = +Dashboards.getParameterValue("param_no_of_end_points"); // null to 0
    if(isNaN(param_no_of_end_points)) { param_no_of_end_points = 0; }

    // JIC, create a copy of the cdaData
    var resultset = cdaData.resultset.slice();

    cdaData = {
    metadata: cdaData.metadata,
    resultset: resultset
    };

    var compareAsc = function(a, b) { return a === b ? 0 : a < b ? 1 : -1; };


    // Sort the filtered resultset by column "valueIndex"
    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]);
    });


    // Extract the first "param_no_of_end_points" rows, when sorted by category
    if(param_no_of_end_points > 0) {

    //fruits.reverse();
    // Discard all rows beyond "param_no_of_end_points"
    resultset.splice(param_no_of_end_points, resultset.length);
    cdaData.resultset = resultset;
    }

    return cdaData;
    }


    After this code my chart is

    Name:  issue.png
Views: 259
Size:  1.3 KB

    Where can I change the above given code to get the chart below(just an example) ?
    Name:  issue2.png
Views: 257
Size:  5.3 KB


    Thank you

  22. #22
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi Sir, there is not at all bug with the code given by.. I solved it.
    Here is the code.

    function f1(cdaData) {
    var categIndex = 0;
    var valueIndex = 1;

    var param_no_of_end_points = +Dashboards.getParameterValue("param_no_of_end_points");

    if(isNaN(param_no_of_end_points))
    {
    param_no_of_end_points = 0;
    }

    var resultset = cdaData.resultset.slice();


    var compareAsc = function(a, b)
    {
    return a === b ? 0 : a > b ? 1 : -1;
    };
    resultset.sort(
    function(rowa, rowb)
    {
    return compareAsc(+rowa[valueIndex],+rowb[valueIndex]);
    }
    );

    if(param_no_of_end_points > 0)
    {
    resultset.splice(0,resultset.length-param_no_of_end_points,0);

    //resultset.splice(-param_no_of_end_points,resultset.length);
    cdaData.resultset = resultset;
    }
    cdaData = {
    metadata: cdaData.metadata,
    resultset: resultset
    };
    return cdaData;
    }

  23. #23

    Default

    Show!!!

    Thanks guys

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 - 2019 Hitachi Vantara Corporation. All Rights Reserved.