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

0

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
ewallace's picture
Joined: Oct 7 2016 - 11:31am
Last seen: 2 years 7 months ago

2 Answers:

1

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.

sandersen's picture
700
Joined: Feb 19 2014 - 10:16am
Last seen: 10 months 2 weeks ago
0

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.

ewallace's picture
Joined: Oct 7 2016 - 11:31am
Last seen: 2 years 7 months ago
Feedback
randomness