Trying to add 2 filters (Investment Type and Booking Status) to 2 queries: Allocation Compliance Pie Chart Query ID: rego_allocation_comp_chart and to Allocation Compliance List QueryID: Drill: Allocation Compliance Grid. Looking within CA-PPM to illustrate the 2 fields I want to add as filters to the 2 queries above (A pie chart and grid display).
What kind of "JOIN" code do I need to add the 2 fields below as filters so the 2 types of charts? Where do I insert the join code into each of the 2 queries? I've included the NSQL code for the 2 charts below.
I've attached files showing the corresponding SQL code for the 2 queries illustrated below.
Below are the 2 fields I need to add as filters to the 2 queries:
- Booking Status
- Table: PR_TEAM
- Booking Status: PRBOOKING - indicates the booking status of a team member. Values are: 5=Soft, 15=Hard,10=Mixed
- Table Key: ???
- Investment Type
- Table: INV-INVESTMENTS
- Investment Type: ODF_OBJECT_CODE - The ODF (Object Data Field???) object code that identifies the specific investment type (e.g. Project, Application, Asset)
Below are the 2 queries I need to add the 2 filters (above) as choices when running these queries:
- Allocation Compliance Pie Chart Query ID: rego_allocation_comp_chart NSQL code below:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:grp:final.alloc_grp:alloc_grp@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:grp:final.grp_id:grp_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:grp:final.start_date:start_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:grp:final.end_date:end_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:grp:NVL(final.obs_id,0):obs_id@,
@SELECT:METRIC:USER_DEF:IMPLIED:COUNT(final.alloc_grp):alloc_grp_cnt@
FROM (SELECT srmr.id res,
av.avail,
NVL (al.alloc, 0) alloc,
NVL (al.alloc, 0) / av.avail * 100 alloc_pct,
CASE
WHEN NVL (al.alloc, 0) / av.avail * 100 < 80 THEN 1
WHEN NVL (al.alloc, 0) / av.avail * 100 BETWEEN 80 AND 120 THEN 2
WHEN NVL (al.alloc, 0) / av.avail * 100 > 120 THEN 3
END grp_id,
CASE
WHEN NVL (al.alloc, 0) / av.avail * 100 < 80
THEN
'A: Under Allocated (Less Than 80%)'
WHEN NVL (al.alloc, 0) / av.avail * 100 BETWEEN 80 AND 120
THEN
'B: Appropriately Allocated (Between 80-120%)'
WHEN NVL (al.alloc, 0) / av.avail * 100 > 120
THEN
'C: Over Allocated (Over 120%)'
END alloc_grp,
@WHERE:PARAM:USER_DEF:INTEGER:obs@ obs_id,
to_date(substr(@WHERE:PARAM:USER_DEF:DATE:startdate@,1,10),'YYYY-MM-DD') start_date,
to_date(substr(@WHERE:PARAM:USER_DEF:DATE:enddate@,1,10),'YYYY-MM-DD') end_date
FROM srm_resources srmr
INNER JOIN prj_resources prjr
ON srmr.id = prjr.prid AND prjr.prisrole = 0
INNER JOIN (SELECT av.prj_object_id resource_id,
SUM(av.slice) avail
FROM prj_blb_slices av
WHERE av.slice_request_id = 1
AND av.slice > 0
AND av.slice_date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@
AND @WHERE:PARAM:USER_DEF:DATE:enddate@
GROUP BY av.prj_object_id) av
ON srmr.id = av.resource_id
LEFT JOIN (SELECT tm.prresourceid resource_id,
SUM(al.slice) alloc
FROM inv_investments invi
LEFT OUTER JOIN inv_projects invp
ON invi.id = invp.prid and invp.is_template = 0
INNER JOIN prteam tm
ON invi.id = tm.prprojectid
AND invi.odf_object_code <> 'idea'
INNER JOIN prj_blb_slices al
ON tm.prid = al.prj_object_id
AND al.slice_request_id = 10
WHERE invi.is_active = 1
AND al.slice_date BETWEEN @WHERE:PARAM:USER_DEF:DATE:startdate@
AND @WHERE:PARAM:USER_DEF:DATE:enddate@
GROUP BY tm.prresourceid) al
ON srmr.id = al.resource_id
WHERE srmr.is_active = 1
AND (@WHERE:PARAM:USER_DEF:INTEGER:obs@ is null
OR EXISTS (SELECT 1
FROM prj_obs_units_flat obsf
INNER JOIN prj_obs_associations obsa
ON obsf.unit_id = obsa.unit_id and obsa.table_name = 'SRM_RESOURCES'
WHERE obsf.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:obs@
AND obsa.record_id = srmr.id)
)) final
WHERE @FILTER@
GROUP BY final.alloc_grp,
final.grp_id,
final.start_date,
final.end_date,
final.obs_id
HAVING @HAVING_FILTER@
- Allocation Compliance List QueryID: Drill: Allocation Compliance Grid NSQL code below:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RES:final.res:res_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RES:final.res_name:res_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RES:final.res_mgr:res_mgr@,
@SELECT:METRIC:USER_DEF:IMPLIED:final.avail:avail@,
@SELECT:METRIC:USER_DEF:IMPLIED:final.alloc:alloc@,
@SELECT:METRIC:USER_DEF:IMPLIED:alloc_pct:alloc_pct@
FROM (SELECT srmr.id res, srmr.full_name res_name, mgr.full_name res_mgr,
av.avail,
NVL (al.alloc, 0) alloc,
NVL (al.alloc, 0) / av.avail * 100 alloc_pct,
CASE
WHEN NVL (al.alloc, 0) / av.avail * 100 < 80 THEN 1
WHEN NVL (al.alloc, 0) / av.avail * 100 BETWEEN 80 AND 120 THEN 2
WHEN NVL (al.alloc, 0) / av.avail * 100 > 120 THEN 3
END grp_id,
CASE
WHEN NVL (al.alloc, 0) / av.avail * 100 < 80
THEN
'A: Under Allocated (Less Than 80%)'
WHEN NVL (al.alloc, 0) / av.avail * 100 BETWEEN 80 AND 120
THEN
'B: Appropriately Allocated (Between 80-120%)'
WHEN NVL (al.alloc, 0) / av.avail * 100 > 120
THEN
'C: Over Allocated (Over 120%)'
END alloc_grp
FROM srm_resources srmr
LEFT JOIN srm_resources mgr
ON mgr.user_id = srmr.manager_id
INNER JOIN prj_resources prjr
ON srmr.id = prjr.prid AND prjr.prisrole = 0
INNER JOIN (SELECT av.prj_object_id resource_id,
SUM(av.slice) avail
FROM prj_blb_slices av
WHERE av.slice_request_id = 1
AND av.slice > 0
AND av.slice_date BETWEEN to_date(REPLACE(SUBSTR(@WHERE:PARAM:XML:STRING:/data/stdate/@value@,0,10),'-',''), 'YYYYMMDD')
AND to_date(REPLACE(SUBSTR(@WHERE:PARAM:XML:STRING:/data/enddate/@value@,0,10),'-',''), 'YYYYMMDD')
GROUP BY av.prj_object_id) av
ON srmr.id = av.resource_id
LEFT JOIN (SELECT tm.prresourceid resource_id,
SUM(al.slice) alloc
FROM inv_investments invi
LEFT OUTER JOIN inv_projects invp
ON invi.id = invp.prid and invp.is_template = 0
INNER JOIN prteam tm
ON invi.id = tm.prprojectid
AND invi.odf_object_code <> 'idea'
INNER JOIN prj_blb_slices al
ON tm.prid = al.prj_object_id
AND al.slice_request_id = 10
WHERE invi.is_active = 1
AND NVL(invp.is_template,0) = 0
AND al.slice_date BETWEEN to_date(REPLACE(SUBSTR(@WHERE:PARAM:XML:STRING:/data/stdate/@value@,0,10),'-',''), 'YYYYMMDD')
AND to_date(REPLACE(SUBSTR(@WHERE:PARAM:XML:STRING:/data/enddate/@value@,0,10),'-',''), 'YYYYMMDD')
GROUP BY tm.prresourceid) al
ON srmr.id = al.resource_id
WHERE srmr.is_active = 1
AND (@WHERE:PARAM:XML:STRING:/data/robs/@value@ = 0
OR EXISTS (SELECT 1
FROM prj_obs_units_flat obsf
INNER JOIN prj_obs_associations obsa
ON obsf.unit_id = obsa.unit_id and obsa.table_name = 'SRM_RESOURCES'
WHERE obsf.branch_unit_id = @WHERE:PARAM:XML:STRING:/data/robs/@value@
AND obsa.record_id = srmr.id)
)) final
WHERE @FILTER@
AND final.grp_id = @WHERE:PARAM:XML:STRING:/data/grpid/@value@
HAVING @HAVING_FILTER@
- The ...png files attached highlight a single work-around line that would be removed and replaced, once I figure out the code to substitute the two filter choices for each query. Any help would be greatly appreciated#