ireport交叉表与通用列组？ [英] ireport crosstab with generic column group?

问题描述

我遇到 JasperReports 交叉表报告的问题。

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 | .......  | ...

创建交叉表时，我会做一个简单的查询 SELECT * FROM表
，然后在交叉表向导中，我将日期设置为行组。

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?

我需要将列组设置为出口柜台，不是出口价值？

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

如何在iReport中执行此操作？

How to do this in iReport?

以前，我一直在考虑

类似的东西：

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       |

但我仍然坚持使用 JasperReports 来实现查询。

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

输出报告应与postgreSQL交叉表查询相同。

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

推荐答案

创建交叉表

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

您的数据选择应为

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

rowGroup 将具有存储区表达式

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

和 columnGroup 将具有

<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.

这篇关于ireport交叉表与通用列组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！