Search code examples
jasper-reportscrosstab

ireport crosstab with generic column group?

I have a problem with JasperReports crosstab report.

For example, I have data that currently is in this format:

| Date      | salesman      | area      | outlet    |
-----------------------------------------------------
| Date A    | Salesman A    | Area A    | Outlet A  |
| Date A    | Salesman A    | Area A    | Outlet B  |
| Date A    | Salesman A    | Area A    | Outlet C  |
| Date B    | Salesman B    | Area A    | Outlet A  |
| Date B    | Salesman B    | Area A    | Outlet D  |

I need to output the data in this format:

Date    | Salesman      | Area      | Outlet 1 | Outlet 2 | Outlet 3 | etc
--------------------------------------------------------------------------
Date A  | Salesman A    | Area A    | Outlet A | Outlet B | Outlet C | ...
Date B  | Salesman B    | Area A    | Outlet A | Outlet D | .......  | ...

When creating a crosstab, i do a simple query SELECT * FROM table and then on crosstab wizard, i have set date as row group.

What should i set in column group?

Crosstab column group

I need to set the column group as outlet counter, not the outlet value? and measure value is outlet.

How to do this in iReport?

previously, i've thinking about using postgreSQL crosstab query.

something like:

SELECT * FROM crosstabN(
    'SELECT salesman::text, date::date AS visit_date, outlet::text
    FROM table'
)

The output for above is in this format:

salesman      | category_1     | category_2     | category_3
------------------------------------------------------------
Salesman A    | Outlet A       | Outlet B       |
Salesman B    | Outlet A       | Outlet C       |

but i'm stuck on implement the query with JasperReports.

The output report should be the same like postgreSQL crosstab query.

Solution

  • To create a cross tab

    salesman      | category_1     | category_2     | category_3
------------------------------------------------------------
Salesman A    | Outlet A       | Outlet B       |
Salesman B    | Outlet A       | Outlet C       |

    Your data selection should be

    salesman   |  category    |   value
-------------------------------------
Salesman A |  category_1  | Outlet A
Salesman B |  category_1  | Outlet A
Salesman A |  category_2  | Outlet B
Salesman B |  category_2  | Outlet C

    The rowGroup will have bucket expression

    <bucket class="java.lang.String">
    <bucketExpression><![CDATA[$F{salesman}]]></bucketExpression>
</bucket>

    and the columnGroup will have

    <bucket class="java.lang.String">
    <bucketExpression><![CDATA[$F{category}]]></bucketExpression>
</bucket>

    Hence you should not try to get crosstab structure from database but flat structure where the name of the crosstab column is one column of your result.