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

[ewallace]

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]

[Scott Andersen]

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-user-guide/v630/adding-input-controls

http://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v630/adding-cascading-input-controls-report

The sampel report "16. Interactive Sales Report" is a good eample when it comes to prepopulating input controls.

[ewallace]

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.