How to get count distinct in my chart without executing another query?

I am using jaspersoft studio 6.2. I have put a bar chart in summary band, how can I set it to count distinct of some field in the value expression? e.g.

I have this query/dataset:

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

What I want to achieve is

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 being category, productname being series. How can I set the value in the chart to be count(distinct customerkey)? I know I can use second query as dataset, and set the val field as the value in the chart, but I will also need to display details in the report so prefer just one query/dataset to do it all.

Is this possible?

Solution

You need a datasource for your chart, so undoubtedly executing another query in subdatset is the easiest approach. However if you do not like to re-query another solution is to use a report scriptlet.

Create a class that extends the JRDefaultScriptlet override the afterDetailEval get the value from your field, store the value and the count of it.

Then let your scriplet return the JRDataSource for the chart.

EDIT: As simple example as asked for in comment

The scriplet

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

The class that holds the count

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++;
    }
}

Tell jrxml to use scriplet (you need to indicate full class name including package name)

In jasperReport tag set scriptletClass="CountScriptlet"

Define the subdataset

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

Use the datasource where you like (piechart, table ecc.)

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

The only limitation is that the report need to have filled the detail band (finished the count), so you can use it in summary band or on reportElement with evaluationTime="Report"