使用Order by expression在Crosstab排序列/行组中进行排序 [英] Sorting in Crosstab sort column/row group with Order by expression
本文介绍了使用Order by expression在Crosstab排序列/行组中进行排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我知道这里有一个类似的线程，但这对我不起作用。
I know there is a similar thread here already, but that doesn't work for me.
背景： Jaspersoft Studio 6
我有这样的数据集：
在此数据集上创建了这样的交叉表：
Created a crosstab like this on this dataset:
如何将列组排序为prod c，prod a，prod b而不是current？
How to sort the column group to prod c, prod a, prod b instead of current?
我尝试将此表达式按列表的排序顺序排列。
I tried put this expression in Order by expression of the column group.
$F{product}.equals("prod c")? 1 : $F{product}.equals("prod a")? 2:3
但它说
编辑：添加源代码，这是没有排序的版本，它运行正常，没有错误但当然没有排序。
edit: added source, this is the version without sorting and it runs fine without error but without sorting of course.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0 -->
<!-- 2016-06-08T14:14:48 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="test3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0a3345e5-7859-4366-9ccf-b215cf60a3b0">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="vha"/>
<style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<queryString>
<![CDATA[select 'channel a' as "sales channel", 'prod a' as product, 1 as amount
union all select 'channel a' as salesChannel, 'prod b' as product, 1 as amount
union all select 'channel a' as salesChannel, 'prod c' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod a' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod b' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod c' as product, 1 as amount]]>
</queryString>
<field name="sales channel" class="java.lang.String"/>
<field name="product" class="java.lang.String"/>
<field name="amount" class="java.lang.Integer"/>
<background>
<band splitType="Stretch"/>
</background>
<summary>
<band height="224" splitType="Stretch">
<crosstab>
<reportElement x="91" y="24" width="459" height="200" uuid="66af64fd-5d36-48a9-9356-f1c2cd55f6a0">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
</reportElement>
<rowGroup name="sales channel1" width="60" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{sales channel}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="27883596-a403-4b9a-a3d5-d18adced1ec1"/>
<textFieldExpression><![CDATA[$V{sales channel1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7e48fc9b-d619-4b04-8b50-4fbd6174509e"/>
<text><![CDATA[Total sales channel1]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="product1" height="20" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{product}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="81d54f8e-11ab-4841-9aef-ef224899c337"/>
<textFieldExpression><![CDATA[$V{product1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="0925adf9-e1d5-4b68-a7c5-0650b7e72721"/>
<text><![CDATA[Total product1]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="amount_MEASURE1" class="java.lang.Integer" calculation="Count">
<measureExpression><![CDATA[$F{amount}]]></measureExpression>
</measure>
<crosstabCell width="60" height="20">
<cellContents mode="Opaque" style="Crosstab_CD">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="cdbc8bf6-e55f-4e69-9505-d40d01870add"/>
<textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" columnTotalGroup="product1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="3543f652-7dee-43fd-84ba-514613b8ac03"/>
<textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="sales channel1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="9657bd6f-c862-4462-b88f-6ea0e049a3ad"/>
<textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="sales channel1" columnTotalGroup="product1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="8311db87-f185-447a-b692-ce878b7fdff5"/>
<textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>
推荐答案
如上所述@ tobi6在他的帖子中你可以使用 orderByExpression 中的em>度量（您可以另外使用带有$ V {}表达式的 Buckets ，但这可能很难）
As mentioned @tobi6 in his post you can use Measures in orderByExpression (you can additionaly use Buckets with $V{} expression but it may be a hard way)
在您的情况下，最简单的方法是使用排序规则添加新的度量。
In your case the simplest way is to add new Measure with sorting rules.
<measure name="productForSort" class="java.lang.Integer">
<measureExpression><![CDATA[$F{product}.equals("prod b") ? 1 : $F{product}.equals("prod c") ? 2 : 3]]></measureExpression>
</measure>
之后你可以在 orderByExpression中使用 Measure 表达式：
After that you can use this Measure in orderByExpression expression:
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{product}]]></bucketExpression>
<orderByExpression><![CDATA[$V{productForSort}]]></orderByExpression>
</bucket>
对于我的测试 csv 数据源：
sales channel, product, amount
channel a, prod c, 5
channel a, prod a, 1
channel b, prod a, 3
channel a, prod b, 1
channel b, prod b, 1
结果是这样的：
如您所见，产品b首先出现。
Notes ：
问题类似（重复）一个非常好的帖子 Jasper使用comparatorExpression报告交叉表排序。您可以在这篇文章中找到更多信息。
Question is similar (duplicates) a really great post Jasper Reports crosstab sorting with comparatorExpression. You can find more information in this post.
我的答案与相似解决方案 @PetterFriberg。但在这种情况下，我们可以省略 Comparator的使用
My answer is similar as the solution by @PetterFriberg. But in this case we can ommit the using of Comparator
另外两个答案也很好，特别是@AndreasDietrich的R& D
Another two answers are also good, specially the R&D by @AndreasDietrich
这篇关于使用Order by expression在Crosstab排序列/行组中进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
