Clarity PPM: Jaspersoft input control Resource OBS Unit not sorted in alphabetical order

Document ID : KB000122209
Last Modified Date : 05/12/2018
Show Technical Document Details
Issue:
The order of the input control(Resource OBS Unit ) in Jaspersoft is different than that of the Clarity display.
 
Cause:
The query does not sort in alphabetical order as it presumes the user will type in the name of the OBS to autopopulate the field.
Resolution:

1. Navigate to Home > Advanced Reporting > View > Repository

2. Expand Folders > CA PPM > Input Controls > Queries

3. Modify the first query(Resource OBS Type)

a. Search for the input control name(Resource OBS Type)
b. Right-click the name of the query and click Edit.
c. Click Next button twice to display the query.
d. Replace the query with the modified query which sorts by alphabetical order.

SELECT DISTINCT obs.obs_type_key AS obs_type_key, 
obs.obs_type_id AS obs_type_id, 
obs.obs_type AS obs_type 
FROM dwh_lkp_obs_unit obs 
WHERE obs.is_resource_obs = 1 
ORDER BY obs.obs_type_id, obs.obs_type 

e. Click Save.

4. Modify the first query(Resource OBS Unit)

a. Search for the input control name(Resource OBS Unit)
b. Right-click the name of the query and click Edit.
c. Click Next button twice to display the query.
d. Replace the query with the modified query which sorts by alphabetical order.

SELECT obs.obs_unit_key AS obs_unit_key, 
obs.obs_unit AS obs_unit, 
obs.obs_path AS obs_display_name, 
obs.hierarchy_level AS hierarchy_level 
FROM dwh_lkp_obs_unit obs 
WHERE obs.is_resource_obs = 1 
AND obs.obs_type_key = $P{resourceOBSTypeKey_1} 
ORDER BY obs.obs_unit 

e. Click Save.

5. Test the input control by running clicking on a report that utilizes it.