How to hide destroyed models in JasperReports Availability Individual Selected Models

Products

• DX NetOps
• CA Spectrum

Issue/Introduction

The Input Controls is displaying the destroyed/deleted models in the JasperReports Availability Individual Selected Models. How to hide the destroyed models?

Environment

Release : Spectrum 21.2.x - 22.2.5 and JasperReports 7.9.1.2

Resolution

This is the workaround to customize the MySQL query in the following Input Controls to hide destroyed models.

1) Please log in as Superuser in JasperReports.

http://<JasperReports_hostname>:8080/jasperserver-pro/login.html

2) Click on View and select Repository. Then expand root --> Public --> ca --> Spectrum --> inputcontrols --> resportspecfic and select Alarm Log-Selected Devices and Models. Right-click on the $R{p_modelName} item and select Edit.

3) Click on the Next button until you reach the Define the Query.

(Edit Input Control) click Next --> (Locate Query) click Next --> (Name the Query) click Next --> (Link a Data Source to the Query) click Next --> (Define the Query)

4) This is the OOTB query in the Define the Query:

select '' as model_name,-1 as model_key
from model
union
select model_name,model_key from (SELECT
m.model_key,
m.model_h,
m.model_name,
mt.mtype_name,flag
From
model m
LEFT JOIN (select model_h as flag,model_h,model_key from devicemodel) dm on m.model_h =dm.model_h
INNER JOIN modeltype mt ON(m.mtype_h = mt.mtype_h ) 
inner join reporting.v_security_string_accessibility_by_landscape sec on (m.landscape_h=sec.landscape_h) and (m.security_string=sec.security_string) and (is_security_enabled()= FALSE or sec.user_name=$P{LoggedInUsername})  
WHERE
($P{p_nameCheckbox}=true or $P{p_modelClassCheckBox}=TRUE or $P{p_landscapeNameCheckBox}=TRUE or 
$P{p_show_devices_only}=TRUE) AND
($P{p_nameCheckbox}=FALSE or ($P{p_nameCheckbox} =TRUE and m.model_name LIKE '$P!{p_name}')) AND
($P{p_modelClassCheckBox}=FALSE or($P{p_modelClassCheckBox}=TRUE and m.model_class=$P{p_modelClass}))
AND ($P{p_landscapeNameCheckBox}=FALSE or($P{p_landscapeNameCheckBox}=TRUE and m.landscape_h=$P{p_landscape}))
) asd
where ($P{p_show_devices_only}=FALSE or($P{p_show_devices_only}=TRUE and flag is not null))
ORDER BY model_name

5) Locate this section in the MySQL query:

FALSE or sec.user_name=$P{LoggedInUsername})  
WHERE

6)  After WHERE add this line:

m.destroy_time IS NULL AND

and click on the Save button.

7) The modified MySQL query looks like this:

select '' as model_name,-1 as model_key
from model
union
select model_name,model_key from (SELECT
m.model_key,
m.model_h,
m.model_name,
mt.mtype_name,flag
From
model m
LEFT JOIN (select model_h as flag,model_h,model_key from devicemodel) dm on m.model_h =dm.model_h
INNER JOIN modeltype mt ON(m.mtype_h = mt.mtype_h ) 
inner join reporting.v_security_string_accessibility_by_landscape sec on (m.landscape_h=sec.landscape_h) and (m.security_string=sec.security_string) and (is_security_enabled()= FALSE or sec.user_name=$P{LoggedInUsername})  
WHERE
m.destroy_time IS NULL AND
($P{p_nameCheckbox}=true or $P{p_modelClassCheckBox}=TRUE or $P{p_landscapeNameCheckBox}=TRUE or 
$P{p_show_devices_only}=TRUE) AND
($P{p_nameCheckbox}=FALSE or ($P{p_nameCheckbox} =TRUE and m.model_name LIKE '$P!{p_name}')) AND
($P{p_modelClassCheckBox}=FALSE or($P{p_modelClassCheckBox}=TRUE and m.model_class=$P{p_modelClass}))
AND ($P{p_landscapeNameCheckBox}=FALSE or($P{p_landscapeNameCheckBox}=TRUE and m.landscape_h=$P{p_landscape}))
) asd
where ($P{p_show_devices_only}=FALSE or($P{p_show_devices_only}=TRUE and flag is not null))
ORDER BY model_name

8) Click on the Submit button.