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
2 Answers:
You define the input controls on the server, and connect them on the server. The JasperReports Server User Guide has a couple of sections that discuss this.
http://community.jaspersoft.com/documentation/tibco-jasperreports-server...
http://community.jaspersoft.com/documentation/tibco-jasperreports-server...
The sampel report "16. Interactive Sales Report" is a good eample when it comes to prepopulating input controls.