Hide/Delete null Series in HTML5 Charts

[gwatt]

Hi there,

I cannot for the life of me figure out how to get rid of the null series that is created in my line chart. It comes out of the following query:

SELECT v.flight_log_id, c.date, v.aircraft_sn , v.drone_nameFROM calendar cLEFT JOIN v_reporting v ON c.date = date_trunc('day',v.flight_datetime) AND v.company_id = $P{company} WHERE c.date BETWEEN $P{from} AND NOW()[/code]

and I'm defining the series expression as $F{aircraft_sn}. The problem is that in a given period there will be blank rows (hence the join w/ the calendar table, to get a full x-axis), but then Highcharts/JS thinks that there's an additional aircraft with an SN of null.

I need to do it as a series expression since there are varying numbers of aircraft per tenant.

Thanks,

Grahame

    <property name="com.jaspersoft.studio.unit." value="pixel"/>    <property name="com.jaspersoft.studio.unit.pageHeight" value="inch"/>    <property name="com.jaspersoft.studio.unit.pageWidth" value="inch"/>    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>    <parameter name="from" class="java.sql.Date" isForPrompting="false" evaluationTime="Early">        <defaultValueExpression><![CDATA[EDATE(NOW(), -1)]]></defaultValueExpression>    </parameter>    <parameter name="company" class="java.lang.Integer" evaluationTime="Early"/>    <queryString language="SQL">        <![CDATA[sELECT v.flight_log_id, c.date, v.aircraft_sn , v.drone_nameFROM calendar cLEFT JOIN v_reporting v ON c.date = date_trunc('day',v.flight_datetime) AND v.company_id = $P{company} WHERE c.date BETWEEN $P{from} AND NOW()]]>    </queryString>    <field name="flight_log_id" class="java.lang.Long">        <property name="com.jaspersoft.studio.field.label" value="flight_log_id"/>    </field>    <field name="date" class="java.sql.Date">        <property name="com.jaspersoft.studio.field.label" value="date"/>    </field>    <field name="aircraft_sn" class="java.lang.String">        <property name="com.jaspersoft.studio.field.label" value="aircraft_sn"/>    </field>    <field name="drone_name" class="java.lang.String">        <property name="com.jaspersoft.studio.field.label" value="drone_name"/>    </field>    <variable name="acsn" class="java.lang.String">        <variableExpression><![CDATA[($F{aircraft_sn}!=null?$F{aircraft_sn}:"")]]></variableExpression>    </variable>    <group name="aircraft_sn">        <groupExpression><![CDATA[$F{aircraft_sn}]]></groupExpression>    </group>    <title>        <band height="252" splitType="Stretch">            <property name="com.jaspersoft.studio.unit.height" value="inch"/>            <componentElement>                <reportElement x="0" y="0" width="468" height="252" uuid="6805107e-7c5d-433d-841b-2e7cbb33f603">                    <property name="com.jaspersoft.studio.unit.width" value="inch"/>                    <property name="com.jaspersoft.studio.unit.height" value="inch"/>                </reportElement>                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="TimeSeriesLine" evaluationTime="Report">                    <hc:chartSetting name="default">                        <hc:chartProperty name="title.text" value="Drone Utilization"/>                        <hc:chartProperty name="credits.enabled" value="false"/>                        <hc:chartProperty name="credits.href" value=""/>                        <hc:chartProperty name="credits.text" value=""/>                        <hc:chartProperty name="chart.zoomType" value="xy"/>                        <hc:chartProperty name="xAxis.dateTimeLabelFormats.month" value="%b '%y"/>                        <hc:chartProperty name="_showDataPoints" value="<not set>"/>                        <hc:chartProperty name="com.jaspersoft.studio.highcharts.dataconfiguration.simple" value="true"/>                        <hc:chartProperty name="plotOptions.line.marker.enabled" value="<not set>"/>                    </hc:chartSetting>                    <hc:chartSetting name="yAxis">                        <hc:chartProperty name="_jrAxisIndex" value="0"/>                        <hc:chartProperty name="title.text" value="Flights"/>                    </hc:chartSetting>                    <multiAxisData>                        <multiAxisDataset/>                        <dataAxis axis="Rows">                            <axisLevel name="Date">                                <labelExpression><![CDATA["Level Label expression"]]></labelExpression>                                <axisLevelBucket class="java.lang.Comparable">                                    <bucketExpression><![CDATA[$F{date}]]></bucketExpression>                                    <labelExpression><![CDATA[DATEFORMAT($F{date},"MMM dd")]]></labelExpression>                                </axisLevelBucket>                            </axisLevel>                        </dataAxis>                        <dataAxis axis="Columns">                            <axisLevel name="ACSN">                                <labelExpression><![CDATA[]]></labelExpression>                                <axisLevelBucket class="java.lang.Comparable">                                    <bucketExpression><![CDATA[$F{aircraft_sn}!=null]]></bucketExpression>                                    <labelExpression><![CDATA["S/N ..." + RIGHT($F{aircraft_sn}, 4)]]></labelExpression>                                    <bucketProperty name="plotOptions.series.visible"><![CDATA[($F{aircraft_sn}==null?false:true)]]></bucketProperty>                                </axisLevelBucket>                            </axisLevel>                        </dataAxis>                        <multiAxisMeasure name="Flights" class="java.lang.Number" calculation="DistinctCount">                            <labelExpression><![CDATA[" "]]></labelExpression>                            <valueExpression><![CDATA[$F{flight_log_id}]]></valueExpression>                        </multiAxisMeasure>                    </multiAxisData>                    <hc:series name="Flights"/>                </hc:chart>            </componentElement>        </band>    </title></jasperReport>[/code]