Use a Calculated Aggregate in HighCharts JaspserStudio

[craig.linderoth]

I need to find a way to use a calculated aggregates as the series value in HighCharts. I have found the solution for Crosstabs, but there doesn't seem to be the same type of solution for HighCharts.

For example, a heatmap needs to show the avg price for a product by store. The calculation should be SUM(SALE_PRICE) OVER (PARTITION BY PRODUCT,STORE) / SUM(ORDER_QTY) OVER (PARTITION BY PRODUCT,STORE). I can get the two sum values correct, create a hidden measure with these fields where the calculation is set to sum. But I can't find anyway to get the rate calculated correctly. Any measure defined in a high chart doesn't have the standard properties to set it's evaluation time. If I create three variables, the sum isn't happening at the correct intersection.

In crosstabs, I created the two sum measures for each intersection, and then in the cell display I did the TOTAL_PRICE/TOTAL_QTY as the display value. I am wondering if there is a way to do this same kind of behavior for a series in a high chart. I have the two metrics and have been trying to find a way to supersede the data.value property of a displayed series with this rate calculation but to no avail.

<multiAxisMeasure name="td" class="java.lang.Double" calculation="Sum">

<labelExpression><![CDATA["td"]]></labelExpression>

<valueExpression><![CDATA[$F{Fact.QUANTITY}]]></valueExpression>

</multiAxisMeasure>

<multiAxisMeasure name="cd" class="java.lang.Double" calculation="Sum">

<labelExpression><![CDATA["cd"]]></labelExpression>

<valueExpression><![CDATA[$F{Fact.SALE_PRICE}]]></valueExpression>

</multiAxisMeasure>

</multiAxisData>

<hc:series name="Percent Booked">

<hc:contributor name="SeriesProperty">

<hc:contributorProperty name="data.value" valueType="Bucket">

<hc:valueExpression><![CDATA["function(){return this.point.cd/this.point.td }"]]></hc:valueExpression>

</hc:contributorProperty>

</hc:contributor>

<hc:contributor name="SeriesItemProperty">

<hc:contributorProperty name="td" valueType="Measure" value="td"/>

<hc:contributorProperty name="cd" valueType="Measure" value="cd"/>

</hc:contributor>

</hc:series>

<hc:series name="td" visible="false"/>

<hc:series name="cd" visible="false"/>