CODEWITHSUNDEEP
postgresqljasper-reports
kimdasuncion12
kimdasuncion12

Reputation: 349

How print Horizontally on ireport dynamically while printing on next page when reached column limit for single page?

I think the the question can be reworded properly please kindly edit if necessary. I've also checked other questions and answers over the internet but it didn't help.

Below is what I'm trying to achieve, basically I want the columns to display horizontally; and if they reach lets say 3 columns(name) it will start on another page. These columns have subcolumns below. I've already tried setting the Print Order to horizontal and set the columns to 3. However its showing the unexpected output.

enter image description here

This my table structure(These have thousands of records). I've also tried to turn this into array but it doesnt work. How can I achieve the ouput above on the report? If you can provide documents or links about this is really helpful. Im using Postgres and ireport 3.7.6.

 date |  name  
------+--------
    1 | Name 1
    2 | Name 1
    3 | Name 1
    4 | Name 1
    5 | Name 1
    6 | Name 1
    7 | Name 1
    8 | Name 1
    9 | Name 1
   10 | Name 1
    1 | Name 2
    2 | Name 2
    3 | Name 2
    4 | Name 2
    5 | Name 2
    6 | Name 2
    7 | Name 2
    8 | Name 2
    9 | Name 2
   10 | Name 2
    1 | Name 3
    2 | Name 3
    3 | Name 3
    4 | Name 3
    5 | Name 3
    6 | Name 3
    7 | Name 3
    8 | Name 3
    9 | Name 3
   10 | Name 3
(30 rows)

Upvotes: 0

Views: 138

Answers (1)

Jim Jones
Jim Jones

Reputation: 19693

I'm not really familiar with ireport, but from the PostgreSQL perspective, I believe you're looking for crosstab. Bellow an example:

(If you haven't installed the extension yet, just execute this command)

CREATE EXTENSION tablefunc

Considering the following table (I believe it's close to your structure):

CREATE TEMPORARY TABLE t (id INT, name TEXT,val INT);

And the following values ...

db=#INSERT INTO t VALUES (1,'Name1',10),
                     (2,'Name1',20),
                     (3,'Name1',80),
                     (1,'Name2',30),
                     (2,'Name2',52),
                     (3,'Name2',40);

db=# SELECT * FROM t;

 id | name  | val 
----+-------+-----
  1 | Name1 |  10
  2 | Name1 |  20
  3 | Name1 |  80
  1 | Name2 |  30
  2 | Name2 |  52
  3 | Name2 |  40
(6 Zeilen)

... you can use crosstab to display your results horizontally:

db=# SELECT * 
     FROM crosstab( 'SELECT name,id,val FROM t') 
     AS j(name text, val1 int, val2 int, val3 int);

 name  | val1 | val2 | val3 
-------+------+------+------
 Name1 |   10 |   20 |   80
 Name2 |   30 |   52 |   40
(2 Zeilen)

Upvotes: 1

Related Questions