Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Iterate resultset as columns instead of rows

  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Default Iterate resultset as columns instead of rows

    Hello Everyone,

    I'm trying to ajust the layout of my resultset, by default Pentaho iterates over a resultset and show them as rows, what i'm looking to achieve is to show them as columns, look at the images:

    What it does actually...
    Name:  1.jpg
Views: 37
Size:  19.2 KBName:  2.jpg
Views: 36
Size:  19.4 KBName:  3.jpg
Views: 34
Size:  18.6 KB

    What i'm trying to achieve...
    Name:  4.jpg
Views: 35
Size:  21.1 KB

    What i'm trying to do is to get the next resultset on a different column instead of a new row, any help would be greatly appreciated. Please let me know if you can't see the images.
    Regards,
    Last edited by tblancog; 05-04-2012 at 03:11 PM.

  2. #2
    Join Date
    Mar 2011
    Posts
    257

    Default

    you will either have to change your query (seperate selects on columns in a global select) or create a cube and use mdx to pivot your view. as far as i know there is no other way to do it.

  3. #3
    Join Date
    Apr 2012
    Posts
    3

    Default

    Oh really? you see, i have a mysql connection and a huge database i can't change my query right now, my client needs to access the report via Pentaho server, is it possible to create a cube and mdx as you say? any good lectures on how to do this? I don't know how to do that.
    Regards,

  4. #4

    Default

    I am presuming that the number of "columns" is fixed. Just change your SQL query. It should not affect the way the data is stored, and will not affect you client in any way. Here is a simple example:

    source data (regionID, sales):
    abc123, 120
    abc123, 80
    abc123, 150
    abc124, 120
    abc124, 200
    abc125, 400
    abc126, 75

    your current query:
    select regionID, sum(sales) from <table> group by customerID

    result:
    abc123, 350
    abc124, 320
    abc125, 400
    abc126, 75


    but if you rewrite your query like this:
    select if(regionID= 'abc123',sales,0) as abc123Sales, if(regionID= 'abc124',sales,0) as abc124Sales, if(regionID= 'abc125',sales,0) as abc125Sales, if(regionID= 'abc126',sales,0) as abc126Sales from <table>;

    (this will show you every row, which will make teh query clear to you).

    The final query is:

    select sum(if(regionID= 'abc123',sales,0)) as abc123Sales, sum(if(regionID= 'abc124',sales,0)) as abc124Sales, sum(if(regionID= 'abc125',sales,0)) as abc125Sales, sum(if(regionID= 'abc126',sales,0)) as abc126Sales from <table>;

    your data will look like this:

    result:
    abc123Sales | abc124Sales | abc126Sales | abc126Sales
    350 | 320 | 400 | 75

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.