AnsweredAssumed Answered

How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

Question asked by geornimofelipe on Aug 9, 2018
Latest reply on Sep 12, 2018 by geornimofelipe

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#

Outcomes