ewallace Posted March 22, 2017 Share Posted March 22, 2017 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_timeorder by to_date(sample_date_time, 'yyyymmddhh24:mi') Link to comment Share on other sites More sharing options...
ewallace Posted March 22, 2017 Author Share Posted March 22, 2017 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 More sharing options...
ewallace Posted March 22, 2017 Author Share Posted March 22, 2017 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_timeorder 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now