java - 如何在不执行另一个查询的情况下获得图表中的不同计数？

Question

我正在使用 jaspersoft studio 6.2。我在汇总带中放置了一个条形图，如何将其设置为对值表达式中的某些字段进行计数？例如

我有这个查询/数据集:

select 'xx' as UsageDate, 'a' as ProductName, 1 as CustomerKey
    union all select 'xx' as UsageDate, 'b' as ProductName, 1 as CustomerKey
    union all select 'xx' as UsageDate, 'a' as ProductName, 2 as CustomerKey
    union all select 'yy'as UsageDate, 'a' as ProductName, 1 as CustomerKey
    union all select 'yy' as UsageDate, 'b' as ProductName, 3 as CustomerKey

我想要实现的是

select usagedate, productname, count(distinct customerkey) as val from (
    select 'xx' as UsageDate, 'a' as ProductName, 1 as CustomerKey
    union all select 'xx' as UsageDate, 'b' as ProductName, 1 as CustomerKey
    union all select 'xx' as UsageDate, 'a' as ProductName, 2 as CustomerKey
    union all select 'yy'as UsageDate, 'a' as ProductName, 1 as CustomerKey
    union all select 'yy' as UsageDate, 'b' as ProductName, 3 as CustomerKey    
    ) as t
    group by usagedate, productname

usagedate 是类别，productname 是系列。如何将图表中的值设置为 count(distinct customerkey)？我知道我可以使用第二个查询作为数据集，并将 val 字段设置为图表中的值，但我还需要在报告中显示详细信息，因此更喜欢仅一个查询/数据集来完成这一切。

这可能吗？

Answer 1

您的图表需要一个数据源，因此在子数据集中执行另一个查询无疑是最简单的方法。但是，如果您不想重新查询，另一个解决方案是使用报告脚本。

创建一个扩展 JRDefaultScriptlet 的类覆盖 afterDetailEval 从您的字段获取值，存储该值及其计数。

然后让您的 scriplet 返回 JRDataSource对于图表。

编辑:作为评论中要求的简单示例

脚本

public class CountScriptlet extends JRDefaultScriptlet {

    private static final String COUNT_FIELD = "fieldNameYouLikeToCount";

    //Map to hold our count and dataset
    private Map<String,CountResult> ds = new HashMap<String, CountResult>(); 

    @Override
    public void afterDetailEval() throws JRScriptletException
    {
        String key = (String)this.getFieldValue(COUNT_FIELD);
        CountResult cr = ds.get(key); //Check if we have it
        if (cr==null){
            cr = new CountResult(key); //No, then create it count = 0
            ds.put(key, cr);
        }
        cr.increment(); //Increment it (new 0-->1, old i-->i+1

    }   
    public JRBeanCollectionDataSource getDataSource(){
        return new JRBeanCollectionDataSource(ds.values()); //Return our class as datasource, if you need to sort'em use Collections
    }
}

保存计数的类

public class CountResult {

    private String description;
    private int count;

    public CountResult(String description) {
        this.description = description;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public int getCount() {
        return count;
    }
    public void setCount(int count) {
        this.count = count;
    }

    public void increment(){
        count++;
    }
}

告诉jrxml使用scriplet(您需要指明完整的类名，包括包名)

在jasperReport标签中设置scriptletClass="CountScriptlet"

定义子数据集

<subDataset name="countDatasource" uuid="f6b4337c-45f4-4fc6-909a-ffbbef3a1b2f">
    <field name="description" class="java.lang.String"/>
    <field name="count" class="java.lang.Integer"/>
</subDataset>

使用您喜欢的数据源(饼图、表格等)

<datasetRun subDataset="countDatasource" uuid="92579588-802b-4073-a5ee-79672c9b6e66">
    <dataSourceExpression><![CDATA[$P{REPORT_SCRIPTLET}.getDataSource()]]></dataSourceExpression>
</datasetRun>

唯一的限制是报告需要填充详细信息区域(完成计数)，因此您可以在 summary 区域中使用它，或者在 reportElement 上使用 evaluationTime="Report"