Hitachi Vantara Pentaho Community Forums
Results 1 to 18 of 18

Thread: Sorting in Pentaho Report Designer 3.5

  1. #1

    Default Sorting in Pentaho Report Designer 3.5

    Hello,

    Is it possible to create interactive sorting in pentaho reports?

    My requirement is that when user clicks on label of table (in detail header) it should sort ascending/descending order.

    Regards,
    Vishwesh

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    It is possible to some degree.

    For that, you need to have a report with a parameter that controls the sorting and a computed query (along with the "Advanced SQL" datasource or the "Advanced MDX" datasource (PRD-3.6!)) to change the query to the given sort-order.

    As second step you then also have to add links to the header-fields (style: html::href) that link to the report itself along with passing the correct sort-order parameter to the report.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default Explanation in detail

    Hello Taqua,
    Thanks for ur response. I will elaborate what u r saying.. correct me if I am wrong..

    1. I have to create 1 parameter for e.g. Order_Param.
    2. Its label will be "Sort By: ".
    3. And we have to pass this parameter in the SQL query using ${Order_Param}.
    for e.g SELECT * FROM TEST_TABLE ORDER BY ${Order_Param}
    This is what I understood from ur 1st step.

    But I have not understood Second Step yet??

    Also can I use PRD 3.6 as a 'Community Version' (i.e. not enterprise version) ??

    Regards,
    Vishwesh

  4. #4
    Join Date
    Mar 2008
    Posts
    364

    Default

    Quote Originally Posted by Vishwesh View Post
    Also can I use PRD 3.6 as a 'Community Version' (i.e. not enterprise version) ??
    of course you can. but as community user you have to check out the sources and build it yourself (svn://source.pentaho.org/pentaho-reporting/tools/report-designer/branches/3.6.0-GA
    ) as it is not available as a package from sourceforge yet (or you could take the continous integration build which is a snapshot > 3.6)

  5. #5

    Default

    Thanks...

  6. #6
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Actually, PRD-3.6 is available from the jfreereport project on sourceforge.

    The biserver-3.5.2 will also be available as soon as it is really built (we are all waiting on it), and hopefully then prd-3.6 will either be made available in the pentaho-sourceforge project or the PRD will be removed from there (anything is better than having a old version sitting around).

    (Usually patch-releases like biserver-3.5.2 would not be released as public build, but this time thanks to integrating 3.6 of reporting, this was to big to be left out.)
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Sep 2010
    Posts
    6

    Default

    Hi

    I'm also interested in the interactive sort function.
    and i understand the methodology for creating the parameter and passing the field name into the query.
    However i do not understand how we would be able to add the sort asc or desc through this method.
    Please help further.

    Regards

  8. #8
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Have a look at the Demo from Michael Tarrallo, it explains everything and even gives a screencast. Just follow to steps:

    http://www.sherito.org/2010/11/demos...n-pentaho.html
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  9. #9

    Default Dynamic sort order

    This is a little tricky, but not outrageous. I will try to put the steps here as best I can. For this example, use this data set:


    DROP TABLE IF EXISTS `prd_Dynamic_test`;
    CREATE TABLE `prd_Dynamic_test` (
    `productID` int(11) DEFAULT NULL,
    `Region` int(11) DEFAULT NULL,
    `ManufacturerID` int(11) DEFAULT NULL,
    `UnitPrice` decimal(6,2) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


    INSERT INTO `prd_Dynamic_test` VALUES (1,101,1001,'20.21'),(1,102,1001,'22.21'),(2,101,1002,'23.88'),(2,102,1002,'19.88'),(3,101,1003,'117.11'),(3,102,1003,'101.99');

    • create a report
    • Under DataSet, click "table" (this will be your sort options)
      • click new query and enter these values for ID/Value
        • Region/Region
        • ManufacturerID/ManufacturerID
        • UnitPrice/UnitPrice
      • before saving, uncheck "use first row as header"
    • Go to Parameters section, and add new parameter
      • give it the name p_sort_by
      • give it any label you want
      • make it a STRING value type
      • For Display Type, pick Radio Button (you can pick others, too)
      • For Query, select the Query you created above
      • pick the corresponding value and display names
    • Next choose "Advanced > JDBC Custom" as your data source, and connect to whatever DB you want
    • click the Structure Tab, and select "Master Report"
      • In the tabs below, select Attributes, in the query group of attributes
        • put this in the name VALUE column:
          • select * from prd_Dynamic_test limit 1;
            • (this entry simply gives the report an understanding of what column names will be available)
        • in the FORMULA column, click the + sign and enter this:
          • ="select * from prd_Dynamic_test order by " & [p_sort_by]

    Thats pretty much it.

  10. #10
    Join Date
    Jul 2012
    Posts
    20

    Default

    The link to Michael's webex is dead. Anybody out there with an example of how to make ORDER BY ${prmSortBy} work?
    thx

  11. #11
    Join Date
    Apr 2012
    Posts
    253

    Default

    It only works with the advanced JDBC. He moved the webex to a different blog post. Adhoc 2.0. But I have done it with the description already posted here by sfrattura. One caveat, if you have a large complex query against multiple tables, the select blah limit 1; isn't going to do you any good. Instead you can go ahead and 'install the query' as you would normally (jdbc, cda, jndi, mon, etc) and then fetch the result columns, layout your report, then simply move the query into the Master Report query field as described below (then delete the previous datasource). Since I'm using oracle I just put 'select 1 from dual;' in the main query string field.

    Actually the select blah will work, it's just ugly.
    Last edited by flamierd; 11-15-2012 at 04:07 PM.

  12. #12
    Join Date
    Jan 2014
    Posts
    9

    Default

    Quote Originally Posted by Taqua View Post
    Have a look at the Demo from Michael Tarrallo, it explains everything and even gives a screencast. Just follow to steps:

    http://www.sherito.org/2010/11/demos...n-pentaho.html


    The Michael Tarrallo link is not working. I need the same interactive sorting thing. Can somebody please give proper steps and explain...

    Regards,
    Ritesh

  13. #13
    Join Date
    Jan 2014
    Posts
    9

    Default

    Hi,

    I have a JDBC Database connection used for my reports. Please somebody guide me on how i can apply interactive sorting on the column names in my report. Help me with this please..

    Thank You in advance.
    Ritesh.

  14. #14

    Default

    HI All,

    is this sorting feature can we implement in 5.0.1 pentaho report desinger community edition.is it possible to do sorting on labels.please suggest me a way how to achieve this functionality.

    Thanks
    phani.

  15. #15
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Sorting is in 5.2. Why not upgrade?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  16. #16

    Default

    Hi Phani,

    If you have some restriction to upgrade to 5.2 , then you can use external Java script ,I'll try to provide you a sample .prpt file but need some time as I have done this long before on 3.9.

    Thanks

  17. #17

    Default

    Hi,

    Thank you for the reply please post the sample .prpt file that really helpful to me and mean while i have one doubt in sorting 5.2 community edition.

    I tried in pentaho 5.2 report designer community version.i found one property called "autosort" in "master report" tab when i make it true i didn't found any difference .please explain me how this autosort functionality will work.

    Thanks in advance.

    phani.

  18. #18
    Join Date
    Apr 2012
    Posts
    253

    Default

    I'd also like to see how the sorting works in 5.2. Looking over the datasource properties, component properties and other misc items, I don't see an obvious method of hooking up sorting.

    I'm 'guessing' that it's going to be a script call you hook up onclick, though that would also mean handling your own 'appearance' state in a script...which is going to be a pita.

    Maybe the sorting only functions in crosstab reports? Just shooting around in the dark. I saw some new classes at github, but as for exploring them blindly...that's going to be a big timesink.

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.