Jump to content
Changes to the Jaspersoft community edition download ×

how to do a line or bar chart with two Y axis values


ewallace

Recommended Posts

I'm trying to put in two values for each side of the chart.. Anyone know how to do that? I need it to show the 4 fields, Volume EL, Speed EL, Volume GP, and Volume EL and then I need it to have Speed on one side and then Volume on the other side. Query is below if it helps.

 

select sample_date_time,
       sum(case
             when lane_type = 'EL' then
              volume
           end) volume_el,
       sum(case
             when lane_type = 'EL' then
              speed
           end) speed_el,
       sum(case
             when lane_type = 'GP' then
              volume
           end) volume_gp,
       sum(case
             when lane_type = 'GP' then
              speed
          end) speed_gp
  from (select calendar_date || time_1_hour sample_date_time,
               lane_type,
               sum(volume) volume,
               avg(speed) speed
          from dph_dashboard.raw_traffic_view
         where station_id in (7,8,9,10,11,12,19,20,21,22,23,24,31,32,371,33,34,35,41,42,43,44,45,46,53,54,55,56,57,58,64,65,66,67,68,69,75,76,77,78,79,80,81,87,88,89,90,91,92,99,100,374,101,102,103,109,110,111,112,113,114,121,122,375,123,124,125,131,132,376,133,134,135,141,142,143,144,145,151,152,153,154,155,161,162,163,164,165)
           and sample_date >= '01-feb-2017'
           and sample_date <= '28-feb-2017'
           and day_of_week_number in (2,3,4,5,6)
           and to_date(time_5_minute, 'hh24:mi') >=  to_date('05:00', 'hh24:mi')  --time from
           and to_date(time_5_minute, 'hh24:mi') <=  to_date('20:00', 'hh24:mi')   --time to
         group by calendar_date || time_1_hour, lane_type)
where to_date(sample_date_time, 'yyyymmddhh24:mi') >= '01-feb-2017'
   and to_date(sample_date_time, 'yyyymmddhh24:mi') < '28-feb-2017'
group by sample_date_time
order by to_date(sample_date_time, 'yyyymmddhh24:mi')
Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

I found this.. Very helpful! http://community.jaspersoft.com/blog/playing-advanced-properties-dual-axis-high-chart-japser-studio-6x-or-later-how-control-y-axes

 

but now I'm getting an error.. 

org.mozilla.javascript.JavaScriptException: Error: Highcharts error #18: www.highcharts.com/errors/18 (com/jaspersoft/jasperreports/highcharts/charts/render/scripts/highcharts-4.2.1.bbox.js#151)

Link to comment
Share on other sites

Here's the code.. if anyone can shed some light on why I keep getting the error even though I'm pretty sure that I followed all the steps correctly..

 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2017-03-22T14:12:34 -->
<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="TRRawTrafficReport" pageWidth="1500" pageHeight="595" orientation="Landscape" columnWidth="1460" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="747d0b6c-ba6e-4dfb-b058-00bd82fc1ae0">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="ALMDDP02 Oracle"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="ireport.jasperserver.url" value="http://10.200.5.177:8080/jasperserver-pro/services/repository/"/>
<property name="ireport.jasperserver.user" value="jasperadmin|organization1"/>
<property name="ireport.jasperserver.reportUnit" value="/reports/TRRawTrafficReport"/>
<queryString>
<![CDATA[select sample_date_time,
       sum(case
             when lane_type = 'EL' then
              volume
           end) volume_el,
       sum(case
             when lane_type = 'EL' then
              speed
           end) speed_el,
       sum(case
             when lane_type = 'GP' then
              volume
           end) volume_gp,
       sum(case
             when lane_type = 'GP' then
              speed
          end) speed_gp
  from (select calendar_date || time_1_hour sample_date_time,
               lane_type,
               sum(volume) volume,
               avg(speed) speed
          from dph_dashboard.raw_traffic_view
         where station_id in (7,8,9,10,11,12,19,20,21,22,23,24,31,32,371,33,34,35,41,42,43,44,45,46,53,54,55,56,57,58,64,65,66,67,68,69,75,76,77,78,79,80,81,87,88,89,90,91,92,99,100,374,101,102,103,109,110,111,112,113,114,121,122,375,123,124,125,131,132,376,133,134,135,141,142,143,144,145,151,152,153,154,155,161,162,163,164,165)
           and sample_date >= '01-feb-2017'
           and sample_date <= '28-feb-2017'
           and day_of_week_number in (2,3,4,5,6)
           and to_date(time_5_minute, 'hh24:mi') >=  to_date('05:00', 'hh24:mi')  --time from
           and to_date(time_5_minute, 'hh24:mi') <=  to_date('20:00', 'hh24:mi')   --time to
         group by calendar_date || time_1_hour, lane_type)
where to_date(sample_date_time, 'yyyymmddhh24:mi') >= '01-feb-2017'
   and to_date(sample_date_time, 'yyyymmddhh24:mi') < '28-feb-2017'
group by sample_date_time
order by to_date(sample_date_time, 'yyyymmddhh24:mi')]]>
</queryString>
<field name="SAMPLE_DATE_TIME" class="java.lang.String"/>
<field name="VOLUME_EL" class="java.math.BigDecimal"/>
<field name="SPEED_EL" class="java.math.BigDecimal"/>
<field name="VOLUME_GP" class="java.math.BigDecimal"/>
<field name="SPEED_GP" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="97" splitType="Stretch">
<staticText>
<reportElement x="515" y="14" width="430" height="50" uuid="cdf80557-db47-41fe-a430-be32be6c39e0"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="24"/>
</textElement>
<text><![CDATA[T&R Raw Traffic Report]]></text>
</staticText>
<image>
<reportElement key="" mode="Opaque" x="0" y="0" width="190" height="69" uuid="b1031222-7fac-4d0c-a3f5-003adc97f43c"/>
<imageExpression><![CDATA["C:\Users\ewallace\Desktop\Logo.png"]]></imageExpression>
</image>
<image>
<reportElement x="1356" y="0" width="104" height="90" uuid="94873d30-acf5-4f57-9c6f-ae43fe7f85c8"/>
<imageExpression><![CDATA["C:\Users\ewallace\Desktop\3-6-2017 12-21-02 PM.png"]]></imageExpression>
</image>
</band>
</title>
<pageHeader>
<band height="169" splitType="Stretch"/>
</pageHeader>
<summary>
<band height="554" splitType="Stretch">
<componentElement>
<reportElement x="22" y="14" width="1420" height="519" uuid="041fd0e6-01a8-4f56-b6f2-0cbccfba7f80"/>
<hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="StackedSpline">
<hc:chartSetting name="default">
<hc:chartProperty name="title.text" value=""/>
<hc:chartProperty name="credits.enabled" value="false"/>
<hc:chartProperty name="credits.href" value=""/>
<hc:chartProperty name="credits.text" value=""/>
<hc:chartProperty name="yAxis.title.text" value=""/>
<hc:chartProperty name="chart.zoomType" value="x"/>
<hc:chartProperty name="chart.showAxes" value="true"/>
</hc:chartSetting>
<hc:chartSetting name="yAxis">
<hc:chartProperty name="_jrAxisIndex" value="0"/>
<hc:chartProperty name="id" value=""id0""/>
<hc:chartProperty name="min" value="0"/>
<hc:chartProperty name="title.text" value=""Volume""/>
</hc:chartSetting>
<hc:chartSetting name="yAxis">
<hc:chartProperty name="_jrAxisIndex" value="1"/>
<hc:chartProperty name="id" value=""id1""/>
<hc:chartProperty name="linkedTo" value="0"/>
<hc:chartProperty name="min" value="0"/>
<hc:chartProperty name="title.text" value=""Speed""/>
</hc:chartSetting>
<multiAxisData>
<multiAxisDataset/>
<dataAxis axis="Rows">
<axisLevel name="Date">
<labelExpression><![CDATA[]]></labelExpression>
<axisLevelBucket class="java.sql.Date">
<bucketExpression><![CDATA[$F{SAMPLE_DATE_TIME}]]></bucketExpression>
</axisLevelBucket>
</axisLevel>
</dataAxis>
<dataAxis axis="Columns"/>
<multiAxisMeasure name="Volume EL" class="java.math.BigDecimal" calculation="Nothing">
<labelExpression><![CDATA["Volume EL"]]></labelExpression>
<valueExpression><![CDATA[$F{VOLUME_EL}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Volume GP" class="java.math.BigDecimal" calculation="Nothing">
<labelExpression><![CDATA["Volume GP"]]></labelExpression>
<valueExpression><![CDATA[$F{VOLUME_GP}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Speed EL" class="java.math.BigDecimal" calculation="Nothing">
<labelExpression><![CDATA["Speed EL"]]></labelExpression>
<valueExpression><![CDATA[$F{SPEED_EL}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="Speed GP" class="java.math.BigDecimal" calculation="Nothing">
<labelExpression><![CDATA["Speed GP"]]></labelExpression>
<valueExpression><![CDATA[$F{SPEED_GP}]]></valueExpression>
</multiAxisMeasure>
</multiAxisData>
<hc:series name="Volume EL">
<hc:contributor name="SeriesProperty">
<hc:contributorProperty name="yAxis" valueType="Constant" value="id0"/>
</hc:contributor>
</hc:series>
<hc:series name="Volume GP">
<hc:contributor name="SeriesProperty">
<hc:contributorProperty name="yAxis" valueType="Constant" value="id0"/>
</hc:contributor>
</hc:series>
<hc:series name="Speed EL">
<hc:contributor name="SeriesProperty">
<hc:contributorProperty name="yAxis" valueType="Constant" value="id1"/>
</hc:contributor>
</hc:series>
<hc:series name="Speed GP">
<hc:contributor name="SeriesProperty">
<hc:contributorProperty name="yAxis" valueType="Constant" value="id1"/>
</hc:contributor>
</hc:series>
</hc:chart>
</componentElement>
</band>
</summary>
</jasperReport>
 
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...