Clarity PPM

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-09-2018 07:33 PM

    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#


  • 2.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-13-2018 04:13 AM

    You should not need a "JOIN" as such, just a condition on where you already access the INV_INVESTMENTS table (for the Investment Type) and where you access the PRTEAM table (for the Booking Status)

     

    eg.

    AND invi.odf_object_code = @WHERE:PARAM:USER_DEF:INTEGER:investment_type@

    which would give you an attribute called param_investment_type in your query which you would then associate with the Investment Types lookup. Then use that param_investment_type field in the filter section of your portlet.

     

    and

    AND tm.prbooking = @WHERE:PARAM:USER_DEF:INTEGER:booking_status@

    which would give you an attribute called param_booking_status in your query which you would then associate with the Booking Status lookup. Then use that param_booking_status field in the filter section of your portlet.

     



  • 3.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-13-2018 03:02 PM

    Hi Dave,

    Garrett and I applied the 2 lines after the stmts used in both queries (Pie chart & Drill list) below:

    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
                                         AND invi.odf_object_code = @WHERE:PARAM:USER_DEF:INTEGER:investment_type@
                         INNER JOIN prteam tm
                                ON invi.id = tm.prprojectid
                                      AND tm.prbooking = @WHERE:PARAM:USER_DEF:INTEGER:booking_status@
                         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 tm.prbooking = @WHERE:PARAM:USER_DEF:INTEGER:booking_status@
                                       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

     

    We added the Booking Status and Investment Type fields to the filter section.  When we enter a value like "hard" for booking status and "project" for investment type, we get an "Error incorrect number format".

     

    Is there a way to display the booking status and investment type as a lookup instead of a text entry?

     

    We lost the values for 2 fields (allocated and allocation %) when we inserted the 2 lines above (ended up with zero values for both fields).

     

    Are we putting the 2 lines in the correct place?  Should they go after the statement below?:

         WHERE invi.is_active = 1



  • 4.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-14-2018 03:59 AM
    Is there a way to display the booking status and investment type as a lookup instead of a text entry?

    Yes - this is the "associate with the Investment Types lookup" and "associate with theBooking Status lookup" that I referred to in my answer, it sounds like you have not done that because the field would display as a lookup and not a text entry if you had.

     

    Are we putting the 2 lines in the correct place?  Should they go after the statement below?

    Well that is where I would put them myself (although your original syntax should work I think), I would also actually cater for the values being optional, like this;

    AND invi.odf_object_code = @NVL@ ( @WHERE:PARAM:USER_DEF:INTEGER:investment_type@ , invi.odf_object_code ) 

    for example.



  • 5.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-15-2018 07:32 PM

    Hi Dave,

    I have applied all of the above suggestions and I now get an an ERROR 500 - Internal Server Error.  What am I doing wrong?  I'm a complete novice at this.  I have attached queries in both NSQL & SQLversions that causing the error when I select the tab "Allocation Compliance" tab under "MSU Resource Management" from the Home tab:

    • Allocation Compliance Pie.NSQL
    • Allocation Compliance Pie.SQL
    • Drill_Allocation Compliance Grid.NSQL
    • Drill_Allocation Compliance Grid.SQL

    Please help...  Thanks for your patience!

    ~gerry



  • 6.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-16-2018 02:44 AM

    Your code is using a divide, could it be that you now have data which has X / 0 (ie zero) which will error?

     

    You also have a to_date, where potentially the @value is not a 'true date'.

     

    Just two suggestions which may be causing this error message.



  • 7.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-16-2018 03:32 AM

    "Error 500" in itself doesn't mean anything - it just means "there is an error". You should look in the app-ca.log to see the actual error details which should give you a clue about what is wrong (presumably with the SQL like roland has suggested)



  • 8.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-17-2018 05:41 PM
      |   view attached

    Dave, I don't have "authority to view app-ca.log file. I had to have Garrett Steudle (MSU's contractor) view it/download it in compressed ...zip file/send it to me via email in order for me to look at it.  Not good/acceptable!

    Administration tab choices in DEVelopment environment of CA-PPM

    As a developer, shouldn't I see more than this?  I canNOT debug anything if I cannot SEE or ACCESS the ...log files.

     

    Basically it says - 

    SQL error code: 1722
    Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01722: invalid number

     

    I'm attaching the app-ca.log file that Garrett passed to me. Btw based on Roland's comments, of the "to-date..." not being numeric; I have not changed that code at all (it is the original code) which indicates to me that there may be bad data.  Is this a correct assumption?  Frustration level has left me drained.  What do I have to change, where dId I go wrong in the coding, what now?

    Attachment(s)

    zip
    app-ca.log.zip   55K 1 version


  • 9.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 08-17-2018 11:18 PM

    You need to speak to your PPM administrator on what access has been provided to your PPM account, this forum can't help you with this part.

     

    In relation to your error message, this is the database message that is encountered when it is running the SQL.  It would have helped if you had provided what parameters were being passed, noting that you are using two types of parameters (eg :param_/data/stdate/@value (around 5-6 of these) and :param_booking_status).

     

    Based on the database error message, it is potentially here:

    obsf.branch_unit_id = :param_/data/robs/@value 

    where you may be passing a non-numeric to this parameter.  I personally don't use this format of parameter so I don't know if this is part of your portlet filter, or passed in via the URL &robs - my guess is it is the URL and this is being incorrectly set to non-numeric.

     

    Note, I have only looked at the first attached ZIP file for the above observation.



  • 10.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM
    Best Answer

    Posted 08-18-2018 04:24 AM

    1. I agree, as a developer you need access to the log files to help you debug. There is an admin page in the application from which you can access the log files (without need for access to the server that they sit upon if that is the concern in your IT department). Go here http(s)://YOURSERVERNAMEHERE/niku/nu#action:security.logs  ( here is a thread explaining that https://communities.ca.com/videos/3364?commentID=233965833#comment-233965833 ).

     

    2. "invalid number" is going to be something to do with the parameters that you are passing into the NSQL - somewhere you are presumably passing a character where the SQL expects a number (either on the value you are joining it to, or perhaps even in the definition of the parameter (but I would think you could only get it to fail that second way if you had not associated the attribute with a lookup and had left it "free text") ).

     

    So look at your parameters carefully. (I have not looked at any of your zip files BTW, it's Saturday morning where I am so..... )

     

    EDIT: actually "invalid number" can also occur where we have joined a char column to a numeric column ; the parser allows this as the char column could contain numeric data that would implicitly get converted at execution time, but in teh case where the char column actually does contain characters rather than numbers then this would fail (with "invalid number") at execution time. So check your joins as well.



  • 11.  Re: How To Add 2 filters to 2 NSQL OBS queries in CA-PPM

    Posted 09-11-2018 10:44 PM

    David Morton & Roland Parrotte,

    I was successful in applying NSQL code changes to the two queries (Allocation Compliance Pie, DRILL Allocation Compliance Grid)  I simply changed to using "STRING" for "investment type" and "INTEGER" for "booking status" for the 2 fields I added as filters.  I didn't get an SQL error code of 1722 anymore.  I've added both filters (Investment type aka odf_object_code, Booking Status aka prbooking) to Portlet Allocation Compliance Pie Chart. I've done unit testing in our DEV environment and awaiting 2 other colleagues here at MSU (Michigan State University) to do a Q&A (Quality Assurance check) on the results and to hammer on the portly (Allocation Compliance Pie Chart) to make sure it is still  robust, yielding correct results and functioning without errors.  Attached to this are the NSQL and SQL listing for the code for your perusal.  I want to thank profusely David Morton and Roland Parotte for their help an patience in this endeavor :-)  I will now pursue viewing the extensive  CA-PPM tutorials , guides etc. to get a better basic understanding of CA-PPM (Clarity) and JasperSoft Studio.  Thank-you all for your help!

     

    TO: David Morton & Roland Parrotte,

    Update Wednesday 11/14/2018:  Uploaded a more current and corrected versions of the two queries (Allocation Compliance Pie, DRILL Allocation Compliance Grid).  Lookup was corrected for both filters. Additional coding changes were made so that portlet would work with all combinations of two filters used simultaneously.  BOTH filters now exists simultaneously (Investment type aka odf_object_code, Booking Status aka prbooking) for the Portlet Allocation Compliance Pie Chart.  New attachments to this reply were uploaded on this date.  I consulted someone on-site with more SQL expertise to refine the NSQL code to get correct results after having our users  test/hammer the porltlet in DEVelopment environment.  Thank-you all for your help!