Thank you sir.
I tired this out in the SQL template designer just as a test:
SELECT DISTINCT j.client_name,
sa.service_group
FROM apt_v_job j,
apt_v_server_attribute sa
WHERE j.client_id = sa.host_id
and it did pull up the attribute values. So progress...
Now with any luck I can modify this report to group by the same custom attribute:
--Author: rich.rose@aptare.com
--Last Updated: 02/07/2018
WITH
var AS (
SELECT
DECODE('${freeCombo2}','KB',1,'MB',1024,'GB',(1024*1024),'TB',(1024*1024*1024),'PB',(1024*1024*1024*1024)) div_by
FROM apt_v_dual
)
SELECT
DECODE('${freeCombo1}',
'Client',client_host_name,
'Master Server',master_host_name,
'Media Server',media_host_name,
'Storage Unit',storage_unit_label,
'Policy',policy_name,
'Policy Type',policy_type_name,
'Job Type',job_type_name,
'Schedule',schedule_name,
'Schedule Type',schedule_type_name,
'Try Count',try_count) unit,
SUM(kilobytes/div_by) total_unexpired_data,
SUM(CASE WHEN expiration_date BETWEEN sysdate AND sysdate+30 THEN kilobytes/div_by END) less_than_30,
SUM(CASE WHEN expiration_date BETWEEN sysdate+30 AND sysdate+90 THEN kilobytes/div_by END) bt_30_and_90,
SUM(CASE WHEN expiration_date BETWEEN sysdate+90 AND sysdate+365.25 THEN kilobytes/div_by END) bt_90_and_1yr,
SUM(CASE WHEN expiration_date BETWEEN sysdate+365.25 AND sysdate+1825 THEN kilobytes/div_by END) bt_1_and_5yrs,
SUM(CASE WHEN expiration_date > sysdate+1825 THEN kilobytes/div_by END) over_5yrs
FROM apt_v_nbu_job_detail, var
WHERE client_id IN (${hosts})
AND start_date BETWEEN ${startDate} AND ${endDate}
AND expiration_date > sysdate
AND DECODE('${freeCombo1}',
'Client',client_host_name,
'Master Server',master_host_name,
'Media Server',media_host_name,
'Storage Unit',storage_unit_label,
'Policy',policy_name,
'Policy Type',policy_type_name,
'Job Type',job_type_name,
'Schedule',schedule_name,
'Schedule Type',schedule_type_name,
'Try Count',try_count) IS NOT NULL
GROUP BY DECODE('${freeCombo1}',
'Client',client_host_name,
'Master Server',master_host_name,
'Media Server',media_host_name,
'Storage Unit',storage_unit_label,
'Policy',policy_name,
'Policy Type',policy_type_name,
'Job Type',job_type_name,
'Schedule',schedule_name,
'Schedule Type',schedule_type_name,
'Try Count',try_count)
ORDER BY 7,6,5,4,3,2,1 DESC
