ewallace

April 26, 2017

The issue is not limiting the user to specific date ranges. The user needsto be able to input dates they want, but unable to exceed 90 days in the start/end date ranges.

April 25, 2017

Is there a way to set constraint on date range parameters to be unable to exceed 90 days?

April 6, 2017

I'm trying to get two date ranges to show up with the word "AND" in the middle of it if both fields are not null. I can't have the end date showing up with the "and" if the beginning date is null. I've tried a few things.

Here's one:
($P{p_trip_entry_begin} != null) ? $P{p_trip_entry_begin} : ($P{p_trip_entry_end} != null) ? ($P{p_trip_entry_begin} != null + " AND " + $P{p_trip_entry_end} != null) : ""

Another one I tried was doing a print when express on the text field of " AND " when the end date is not null, but i'm not sure if there is a way to tell it to print the AND if BOTH are not null.. is there a way?

March 31, 2017

Two of three of my fields are not showing up in my custom template export. The field that is showing up is the group header with "GroupField" text field. Other two - one text field (in detail band named "Field") and one static field (in column header named Label). I have changed the band height and made it higher, but nothing has worked. I followed the documentation on how to create a custom template and everything.

March 29, 2017

In the database, the field rt.calendar_date is VARCHAR2. The alias/field, 'hours' seems to work with the parameter, but the alias/field, 'datess' does not. I haven't been able to figure out why. I have tried so many different things and different ways, changing data type of the field and parameters, and so on, but nothing works. I've tried to do $X{BETWEEN, datess, StartDate, EndDate}, but no success. Anyone know why one works, but other doesn't?

Query below:

select

to_date(rt.calendar_date||rt.TIME_5_MINUTE, 'yyyymmddhh24:mi') sample_date_time

, rt.lane_type

, sum(rt.volume) volume

, avg(rt.speed) speed

, s.direction

, s.segment_desc

, s.station_desc

, rt.calendar_date

, to_date(rt.calendar_date, 'yyyy/mm/dd hh24:mi:ss') datess

, to_char(to_date(rt.calendar_date, 'yyyy/mm/dd hh24:mi:ss'), 'HH:MI AM') hours

, to_char(TO_DATE(rt.calendar_date, 'yyyy/mm/dd') , 'DAY') calender_date_day

from dph_dashboard.raw_traffic_view rt

JOIN dph_dashboard.STATIONS S ON rt.STATION_ID = s.STATION_ID

where s.station_id = rt.station_id

and rt.day_of_week_number = 2

AND to_date(rt.CALENDAR_DATE, 'yyyymmddhh24:mi') >= '01-feb-2017'

and to_date(rt.CALENDAR_DATE, 'yyyymmddhh24:mi') < '28-feb-2017'

and ( $P{StartDate} is null or $P{StartDate} = datess)

and ($X{BETWEEN, hours, TimeFrom, TimeTo})

and ($X{IN, calender_date_day, DaysOfWeek} )

and ($X{IN, direction, Direction})

and ($X{IN, segment_desc, Segment})

and ($X{IN, station_desc, Station})

group by to_date(rt.calendar_date||rt.TIME_5_MINUTE, 'yyyymmddhh24:mi'),

to_char(to_date(rt.calendar_date, 'yyyy/mm/dd hh24:mi:ss'), 'HH:MI AM'),

to_char(TO_DATE(rt.calendar_date, 'yyyy/mm/dd') , 'DAY'),

rt.lane_type, s.station_desc, s.direction, s.segment_desc, rt.calendar_date,

to_date(rt.calendar_date, 'yyyy/mm/dd hh24:mi:ss')

March 23, 2017

My report needs the following 3 parameters added to it:

Time From = specific time of day(s) the output starts (not to be confused with Start Date)

Time To = specific time of day(s) the output ends (not to be confused with End Date)

Time Interval = Summerized times (15 mins, 30 mins, Hour, Day, Week)

But I am not sure how.. Is a variable needed? Or is there a way to do this with parameters? I'm a bit lost on how to word this question, and even more lost on how to get this done. Query is below.

select sample_date_time, station_desc,

to_char(TO_DATE(calendar_date, 'yyyy/mm/dd') , 'DAY') calender_date_day,

direction, segment_desc,

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 rt.calendar_date || rt.time_1_hour sample_date_time, rt.calendar_date,

rt.lane_type,

sum(rt.volume) volume,

avg(rt.speed) speed,

s.direction,

s.segment_desc,

s.station_desc

from dph_dashboard.raw_traffic_view rt,

dph_dashboard.STATIONS S

where s.station_id = rt.station_id

and rt.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 rt.sample_date >= '01-feb-2017'

and rt.sample_date <= '28-feb-2017'

and rt.day_of_week_number in (2,3,4,5,6)

and to_date(rt.time_5_minute, 'hh24:mi') >= to_date('05:00', 'hh24:mi') --time from

and to_date(rt.time_5_minute, 'hh24:mi') <= to_date('20:00', 'hh24:mi') --time to

group by calendar_date || time_1_hour, lane_type, s.station_desc, calendar_date, s.direction, s.segment_desc)

where to_date(sample_date_time, 'yyyymmddhh24:mi') >= '01-feb-2017'

and to_date(sample_date_time, 'yyyymmddhh24:mi') < '28-feb-2017'

and ( $P{StartDate} is null or $P{StartDate} <= calendar_date )

and ( $P{EndDate} is null or $P{EndDate} >= calendar_date )

and ( $X{IN, calender_date_day, DaysOfWeek} )

and ($X{IN, direction, Direction})

and ($X{IN, segment_desc, Segment})

and ($X{IN, station_desc, Station})

group by sample_date_time, station_desc, calendar_date, direction, segment_desc

order by to_date(sample_date_time, 'yyyymmddhh24:mi')

March 23, 2017

I found the issue, it was something to do with the type of the field that was clashing with the parameter.

March 23, 2017

My query works in the database platform (SQL Developer is what I use.), but when I plug it in jasper, it complains about "Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string", and I can't figure out why. It was working fine before I put in the 2nd table, along with the station field. Any ideas?

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,

station_desc

from (select rt.calendar_date || rt.time_1_hour sample_date_time,

rt.lane_type,

sum(rt.volume) volume,

avg(rt.speed) speed,

s.station_desc

from dph_dashboard.raw_traffic_view rt,

dph_dashboard.STATIONS S

where s.station_id = rt.station_id

and rt.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 rt.sample_date >= '01-feb-2017'

and rt.sample_date <= '28-feb-2017'

and rt.day_of_week_number in (2,3,4,5,6)

and to_date(rt.time_5_minute, 'hh24:mi') >= to_date('05:00', 'hh24:mi') --time from

and to_date(rt.time_5_minute, 'hh24:mi') <= to_date('20:00', 'hh24:mi') --time to

group by calendar_date || time_1_hour, lane_type, s.station_desc)

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, station_desc

order by to_date(sample_date_time, 'yyyymmddhh24:mi')

March 22, 2017

I keep getting this error (https://www.highcharts.com/errors/18) on my report and I cannot figure out why. I followed this: http://community.jaspersoft.com/blog/playing-advanced-properties-dual-axis-high-chart-japser-studio-6x-or-later-how-control-y-axes

Seems like it should work but it's not.. Below is my code. Any help would be immensely appreciated!!

<?xml version="1.0" encoding="UTF-8"?>

<![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>

</background>

<title>

</textElement>

</staticText>

<image>

</image>

<image>

</image>

</band>

</title>

</pageHeader>

<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=""T&R Raw Traffic Report Chart""/>

<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="xy"/>

<hc:chartProperty name="chart.showAxes" value="false"/>

<hc:chartProperty name="plotOptions.series.allowPointSelect" 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>

</axisLevelBucket>

</axisLevel>

</dataAxis>

</multiAxisMeasure>

</multiAxisMeasure>

</multiAxisMeasure>

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

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"?>

<![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>

</background>

<title>

</textElement>

</staticText>

<image>

</image>

<image>

</image>

</band>

</title>

</pageHeader>

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

</axisLevelBucket>

</axisLevel>

</dataAxis>

</multiAxisMeasure>

</multiAxisMeasure>

</multiAxisMeasure>

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

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)

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_time

order by to_date(sample_date_time, 'yyyymmddhh24:mi')

March 20, 2017

Thank you for that tip! I tried it, but it kept breaking.. :( I've been stuck on this for awhile. I've tried so many different things, and the report keeps breaking. I'm trying to retrieve the time from S.START_TIME and S.END_TIME, and put it in the report as a range that returns all data between S.START_TIME and S.END_TIME. The entire query is below - everything seems to work, until this line in the parameters: and (START_TIME between $P{startTime} AND $P{endTime} ). It keeps complaining that START_TIME is an invalid identifier. I've checked the types, and I was told to put the parameters in as String, because the fields are converted using TO_CHAR.. Any help would be GREATLY appreciated!

SELECT /* HOT LANE TRAFFIC REPORT */

t.agency,

t.facility,

t.direction,

S.START_DATE,

S.END_DATE,

TO_CHAR(S.START_DATE, 'HH24:MI') START_TIME,

TO_CHAR(S.END_DATE, 'HH24:MI') END_TIME,

TO_CHAR(S.START_DATE, 'DAY') DAYOFWK,

SEG.DESCRIPTION SEGMENT,

SE.DESCRIPTION SECTION,

SEG.DISPLAY_ORDER DISPLAY_ORDER,

S.EL_TOTAL_VOLUME,

NVL(T.GP_TOTAL_VOLUME, 0) GP_TOTAL_VOLUME,

S.EL_TOTAL_VOLUME + NVL(T.GP_TOTAL_VOLUME, 0) TOTAL_VOLUME,

NVL(T.EL_AVG_SPEED, 0) EL_AVG_SPEED,

NVL(T.GP_AVG_SPEED, 0) GP_AVG_SPEED,

NVL(T.EL_TOTAL_SPEED, 0) EL_TOTAL_SPEED,

NVL(T.GP_TOTAL_SPEED, 0) GP_TOTAL_SPEED,

NVL(T.GP_TRAF_RECORD, 0) GP_TRAF_RECORD,

NVL(T.EL_TRAF_RECORD, 0) EL_TRAF_RECORD,

S.EL_VALID_AVI_COUNT,

S.EL_INVALID_AVI_COUNT,

S.EL_NON_AVI_VOLUME

FROM (SELECT a.DISPLAY_DESCRIPTION as agency,

F.DISPLAY_DESCRIPTION as facility,

d.dire_desc as direction,

utl_time.ROUND_TIME(START_DATE, 30) START_DATE,

utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE,

SEGMENT_ID,

SUM(TXN_COUNT) EL_TOTAL_VOLUME,

SUM(VALID_AVI_COUNT) EL_VALID_AVI_COUNT,

SUM(INVALID_AVI_COUNT) EL_INVALID_AVI_COUNT,

SUM(NON_AVI_COUNT) EL_NON_AVI_VOLUME

FROM DP_OWNER.HOT_LANE_TRAFFIC_SUMMARY hlts

,rite_common.AGENCIES a

,rite_common.FACILITIES f

,rite_common.PLAZAS p

,rite_common.LANES l

,rite_common.directions d

WHERE HLTS.FACILITY_ID = F.FACS_ID

and a.AGCY_ID = f.AGCY_ID

and f.FACS_ID = p.FACS_ID

and l.PLAZ_ID = p.PLAZ_ID

and l.dire_id = d.dire_id

and trunc(START_DATE) BETWEEN

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')

AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)

AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)

GROUP BY utl_time.ROUND_TIME(START_DATE, 30), SEGMENT_ID, a.DISPLAY_DESCRIPTION, F.DISPLAY_DESCRIPTION, d.DIRE_DESC) S

LEFT OUTER JOIN (SELECT ag.DISPLAY_DESCRIPTION as agency,

FC.DISPLAY_DESCRIPTION as facility, di.dire_desc as direction, utl_time.ROUND_TIME(START_DATE, 30) START_DATE,

utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE, SEGMENT_ID,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN AVG_VOLUME ELSE 0 END) GP_TOTAL_VOLUME,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED END) /

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT END) EL_AVG_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED END) / SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT END) GP_AVG_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT ELSE 0 END) EL_TRAF_RECORD,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT ELSE 0 END) GP_TRAF_RECORD,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED ELSE 0 END) EL_TOTAL_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED ELSE 0 END) GP_TOTAL_SPEED

FROM DP_OWNER.TMS_TRAFFIC_SUMMARY TMS

,rite_common.AGENCIES ag

,rite_common.FACILITIES fc

,rite_common.PLAZAS pl

,rite_common.LANES la

,rite_common.directions di

WHERE trunc(START_DATE) BETWEEN

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')

and TMS.FACILITY_ID = FC.FACS_ID

and ag.AGCY_ID = fc.AGCY_ID

and fc.FACS_ID = pl.FACS_ID

and la.PLAZ_ID = pl.PLAZ_ID

and la.DIRE_ID = di.DIRE_ID

AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)

AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)

GROUP BY utl_time.ROUND_TIME(START_DATE, 30), ag.DISPLAY_DESCRIPTION, FC.DISPLAY_DESCRIPTION, SEGMENT_ID, DI.DIRE_DESC) T

ON S.START_DATE = T.START_DATE

AND S.SEGMENT_ID = T.SEGMENT_ID

JOIN DP_OWNER.SEGMENTS SEG

ON SEG.SEGMENT_ID = S.SEGMENT_ID

JOIN DP_OWNER.SECTION_SEGMENT_XREF SSX

ON SSX.SEGMENT_ID = SEG.SEGMENT_ID

JOIN DP_OWNER.SECTIONS SE

ON SE.SECTION_ID = SSX.SECTION_ID

where ($X{IN, SEG.DESCRIPTION, segment})

and ( $X{IN, T.AGENCY, agencyList})

and ( $X{IN, T.DISPLAY_DESCRIPTION, facility} )

and ($X{IN, SE.DESCRIPTION, section})

and ($X{IN, T.DIRECTION, direction})

and ( $P{startDate} is null or $P{startDate} <= S.START_DATE)

and ( $P{endDate} is null or $P{endDate} >= S.END_DATE )

and ($X{IN, DAYOFWK, days})

and (START_TIME between $P{startTime} AND $P{endTime} )

ORDER BY S.START_DATE, SEG.DISPLAY_ORDER

March 20, 2017

I've been stuck on this for awhile. I've tried so many different things, and the report keeps breaking. I'm trying to retrieve the time from S.START_TIME and S.END_TIME, and put it in the report as a range that returns all data between S.START_TIME and S.END_TIME. Query is below - everything seems to work, until this line in the parameters: and (START_TIME between $P{startTime} AND $P{endTime} ). It keeps complaining that START_TIME is an invalid identifier. I've checked the types, and I was told to put the parameters in as String, because the fields are converted using TO_CHAR.. Any help would be GREATLY appreciated!

SELECT /* HOT LANE TRAFFIC REPORT */

t.agency,

t.facility,

t.direction,

S.START_DATE,

S.END_DATE,

TO_CHAR(S.START_DATE, 'HH24:MI') START_TIME,

TO_CHAR(S.END_DATE, 'HH24:MI') END_TIME,

TO_CHAR(S.START_DATE, 'DAY') DAYOFWK,

SEG.DESCRIPTION SEGMENT,

SE.DESCRIPTION SECTION,

SEG.DISPLAY_ORDER DISPLAY_ORDER,

S.EL_TOTAL_VOLUME,

NVL(T.GP_TOTAL_VOLUME, 0) GP_TOTAL_VOLUME,

S.EL_TOTAL_VOLUME + NVL(T.GP_TOTAL_VOLUME, 0) TOTAL_VOLUME,

NVL(T.EL_AVG_SPEED, 0) EL_AVG_SPEED,

NVL(T.GP_AVG_SPEED, 0) GP_AVG_SPEED,

NVL(T.EL_TOTAL_SPEED, 0) EL_TOTAL_SPEED,

NVL(T.GP_TOTAL_SPEED, 0) GP_TOTAL_SPEED,

NVL(T.GP_TRAF_RECORD, 0) GP_TRAF_RECORD,

NVL(T.EL_TRAF_RECORD, 0) EL_TRAF_RECORD,

S.EL_VALID_AVI_COUNT,

S.EL_INVALID_AVI_COUNT,

S.EL_NON_AVI_VOLUME

FROM (SELECT a.DISPLAY_DESCRIPTION as agency,

F.DISPLAY_DESCRIPTION as facility,

d.dire_desc as direction,

utl_time.ROUND_TIME(START_DATE, 30) START_DATE,

utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE,

SEGMENT_ID,

SUM(TXN_COUNT) EL_TOTAL_VOLUME,

SUM(VALID_AVI_COUNT) EL_VALID_AVI_COUNT,

SUM(INVALID_AVI_COUNT) EL_INVALID_AVI_COUNT,

SUM(NON_AVI_COUNT) EL_NON_AVI_VOLUME

FROM DP_OWNER.HOT_LANE_TRAFFIC_SUMMARY hlts

,rite_common.AGENCIES a

,rite_common.FACILITIES f

,rite_common.PLAZAS p

,rite_common.LANES l

,rite_common.directions d

WHERE HLTS.FACILITY_ID = F.FACS_ID

and a.AGCY_ID = f.AGCY_ID

and f.FACS_ID = p.FACS_ID

and l.PLAZ_ID = p.PLAZ_ID

and l.dire_id = d.dire_id

and trunc(START_DATE) BETWEEN

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')

AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)

AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)

GROUP BY utl_time.ROUND_TIME(START_DATE, 30), SEGMENT_ID, a.DISPLAY_DESCRIPTION, F.DISPLAY_DESCRIPTION, d.DIRE_DESC) S

LEFT OUTER JOIN (SELECT ag.DISPLAY_DESCRIPTION as agency,

FC.DISPLAY_DESCRIPTION as facility, di.dire_desc as direction, utl_time.ROUND_TIME(START_DATE, 30) START_DATE,

utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE, SEGMENT_ID,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN AVG_VOLUME ELSE 0 END) GP_TOTAL_VOLUME,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED END) /

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT END) EL_AVG_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED END) / SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT END) GP_AVG_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT ELSE 0 END) EL_TRAF_RECORD,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT ELSE 0 END) GP_TRAF_RECORD,

SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED ELSE 0 END) EL_TOTAL_SPEED,

SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED ELSE 0 END) GP_TOTAL_SPEED

FROM DP_OWNER.TMS_TRAFFIC_SUMMARY TMS

,rite_common.AGENCIES ag

,rite_common.FACILITIES fc

,rite_common.PLAZAS pl

,rite_common.LANES la

,rite_common.directions di

WHERE trunc(START_DATE) BETWEEN

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND

TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')

and TMS.FACILITY_ID = FC.FACS_ID

and ag.AGCY_ID = fc.AGCY_ID

and fc.FACS_ID = pl.FACS_ID

and la.PLAZ_ID = pl.PLAZ_ID

and la.DIRE_ID = di.DIRE_ID

AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)

AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)

GROUP BY utl_time.ROUND_TIME(START_DATE, 30), ag.DISPLAY_DESCRIPTION, FC.DISPLAY_DESCRIPTION, SEGMENT_ID, DI.DIRE_DESC) T

ON S.START_DATE = T.START_DATE

AND S.SEGMENT_ID = T.SEGMENT_ID

JOIN DP_OWNER.SEGMENTS SEG

ON SEG.SEGMENT_ID = S.SEGMENT_ID

JOIN DP_OWNER.SECTION_SEGMENT_XREF SSX

ON SSX.SEGMENT_ID = SEG.SEGMENT_ID

JOIN DP_OWNER.SECTIONS SE

ON SE.SECTION_ID = SSX.SECTION_ID

where ($X{IN, SEG.DESCRIPTION, segment})

and ( $X{IN, T.AGENCY, agencyList})

and ( $X{IN, T.DISPLAY_DESCRIPTION, facility} )

and ($X{IN, SE.DESCRIPTION, section})

and ($X{IN, T.DIRECTION, direction})

and ( $P{startDate} is null or $P{startDate} <= S.START_DATE)

and ( $P{endDate} is null or $P{endDate} >= S.END_DATE )

and ($X{IN, DAYOFWK, days})

and (START_TIME between $P{startTime} AND $P{endTime} )

ORDER BY S.START_DATE, SEG.DISPLAY_ORDER

March 17, 2017

I have been having trouble extracting the time from a date field, and tying it to a parameter. I have tried TO_CHAR, EXTRACT, TRUNC, and nested ones, but they all break when it comes to the parameter. Below is a snippet of the code with the parameter. I need to set up Time Period, between two times from S.START_DATE and S.END_DATE.

SELECT (TO_NUMBER(TO_CHAR(S.START_DATE, 'HH24'))) DISPLAY_START_TIME

FROM TABLE_NAME

WHERE ( $P{startTime} is null or $P{startTime} <= DISPLAY_START_TIME )

March 15, 2017

I figured it out! I missed the little step that the parameter name on the server needs to match, exactly, to the parameter name in the Report studio. It all works now.

March 13, 2017

I have been trying to figure out how to pre-populate a drop-down list for my parameters without hard coding it in. I've been working on this report in the studio, and then I will be publishing it to the server. Below is my query from the report. It isn't perfect, I've been playing around a bit with the parameters without much luck. Right now, my primary focus is getting the agency parameter to pre-populate with the data from the database. I do have several others that need the same thing, but they all should fall into place once I get this one figured out. Any help would be GREATLY appreciated.

SELECT /* HOT LANE TRAFFIC REPORT */t.agency, t.facility,S.START_DATE,S.END_DATE,TO_CHAR(S.START_DATE, 'HH24:MI:SS') DISPLAY_START_DATE,TO_CHAR(S.END_DATE, 'HH24:MI:SS') DISPLAY_END_DATE,SEG.DESCRIPTION SEGMENT,SEG.DISPLAY_ORDER DISPLAY_ORDER,S.EL_TOTAL_VOLUME,NVL(T.GP_TOTAL_VOLUME, 0) GP_TOTAL_VOLUME,S.EL_TOTAL_VOLUME + NVL(T.GP_TOTAL_VOLUME, 0) TOTAL_VOLUME,NVL(T.EL_AVG_SPEED, 0) EL_AVG_SPEED,NVL(T.GP_AVG_SPEED, 0) GP_AVG_SPEED,NVL(T.EL_TOTAL_SPEED, 0) EL_TOTAL_SPEED,NVL(T.GP_TOTAL_SPEED, 0) GP_TOTAL_SPEED,NVL(T.GP_TRAF_RECORD, 0) GP_TRAF_RECORD,NVL(T.EL_TRAF_RECORD, 0) EL_TRAF_RECORD,S.EL_VALID_AVI_COUNT,S.EL_INVALID_AVI_COUNT,S.EL_NON_AVI_VOLUME  FROM (SELECT a.DISPLAY_DESCRIPTION as agency, F.DISPLAY_DESCRIPTION as facility,  utl_time.ROUND_TIME(START_DATE, 30) START_DATE,               utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE,               SEGMENT_ID,               SUM(TXN_COUNT) EL_TOTAL_VOLUME,               SUM(VALID_AVI_COUNT) EL_VALID_AVI_COUNT,               SUM(INVALID_AVI_COUNT) EL_INVALID_AVI_COUNT,               SUM(NON_AVI_COUNT) EL_NON_AVI_VOLUME          FROM DP_OWNER.HOT_LANE_TRAFFIC_SUMMARY hlts                ,rite_common.AGENCIES a                ,rite_common.FACILITIES f          WHERE HLTS.FACILITY_ID = F.FACS_ID            and a.AGCY_ID = f.AGCY_ID            and ( $X{IN, a.AGCY_ID, agency} )            and ( $P{facility} is null or  $P{facility} = f.DISPLAY_DESCRIPTION  )            and trunc(START_DATE) BETWEEN               TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND               TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')           AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)           AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)         GROUP BY utl_time.ROUND_TIME(START_DATE, 30), SEGMENT_ID,  a.DISPLAY_DESCRIPTION, F.DISPLAY_DESCRIPTION ) S  LEFT OUTER JOIN (SELECT ag.DISPLAY_DESCRIPTION as agency, FC.DISPLAY_DESCRIPTION as facility, utl_time.ROUND_TIME(START_DATE, 30) START_DATE,                          utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE, SEGMENT_ID,                          SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN AVG_VOLUME ELSE 0 END) GP_TOTAL_VOLUME,                          SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED END) /                          SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT END) EL_AVG_SPEED,                          SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED END) / SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT END) GP_AVG_SPEED,                          SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT ELSE 0 END) EL_TRAF_RECORD,                          SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT ELSE 0 END) GP_TRAF_RECORD,                          SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED ELSE 0 END) EL_TOTAL_SPEED,                          SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED ELSE 0 END) GP_TOTAL_SPEED                     FROM DP_OWNER.TMS_TRAFFIC_SUMMARY TMS                        ,rite_common.AGENCIES ag                        ,rite_common.FACILITIES fc                               WHERE trunc(START_DATE) BETWEEN                          TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND                          TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3')            and TMS.FACILITY_ID = FC.FACS_ID            and ag.AGCY_ID = fc.AGCY_ID            and ( $X{IN, ag.DISPLAY_DESCRIPTION, agency})            and ( $P{facility} is null or  $P{facility} = fc.DISPLAY_DESCRIPTION  )                      AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT)                      AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT)                    GROUP BY utl_time.ROUND_TIME(START_DATE, 30),ag.DISPLAY_DESCRIPTION,  FC.DISPLAY_DESCRIPTION, SEGMENT_ID) T    ON S.START_DATE = T.START_DATE   AND S.SEGMENT_ID = T.SEGMENT_ID  JOIN DP_OWNER.SEGMENTS SEG    ON SEG.SEGMENT_ID = S.SEGMENT_ID    where ($P{segment} is null or  $P{segment} = SEG.DESCRIPTION)ORDER BY S.START_DATE, SEG.DISPLAY_ORDER[/code]

Sign In

ewallace

Posts

Joined

Last visited

Content Type

Profiles

Forum

Events

Featured Visualizations

Knowledge Base

Documentation (PDF Downloads)

Blog

Documentation (Test Area)

Documentation

Dr. Jaspersoft Webinar Series

Downloads

Posts posted by ewallace

limiting range between dates

limiting range between dates

using ternary operator for two fields

fields not showing up in custom template export

mapping parameter

how to do time parameters

query works in db platform but not in jasper

query works in db platform but not in jasper

Issues with Y/X Axis

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

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

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

how to extract time from a field and tie it to a parameter

how to extract time from a field and tie it to a parameter as a range

how to extract time from a field and tie it to a parameter

how to pre-populate drop-down list for a parameter

how to pre-populate drop-down list for a parameter

Activity

Products

Explore