Clarity PPM

Expand all | Collapse all

The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

Jump to Best Answer
  • 1.  The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 10-08-2015 10:54 AM

    I have performed the SQL trace I could see that 2 is passed as variable for this (@where:param:user_def:integer:hg_all_rows@ parameter upon doing filtering and 1 is passed as variable for export to excel. I have tried to change this decode function   DECODE(@where:param:user_def:integer:hg_all_rows@,1,optinv.name,NULL) by replacing 1 with 3 so that only filtered data is shown by doing export to excel (data only) but the excel sheet was returning only the filtered data but all the child rows were not showing up correctly, all the child rows are appearing at the bottom of the excel sheet.

    Can someone please suggest me where the changes should be made in the NSQL.

     

    This is our NSQL:

     

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:INVESTMENT:invid||seq:hiddenid@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:name:InvestmentName@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:dname:dimendionname@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:forecasttype:forecasttype@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:NVL(seq,-1):seq@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:high:highlight@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:hg_has_children:hg_has_children@,
    @SELECT:DIM:USER_DEF:IMPLIED:DT:period_name:period_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DT:displayname:displayname@,
    @SELECT:METRIC:USER_DEF:IMPLIED:ROUND(tgtval,2):val@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:portfolio:portfolio@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:(select name from ODF_CA_OPT_PORTFOLIO where code = portfolio):portfolio_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:invfilter:invfilter@
    FROM (
    WITH ecrvals AS
      (SELECT ecr.odf_parent_id,ecrtype.name ecrtype,ecrforecast.name forecasttype,ecrtgt.slice_date,SUM(ecrtgt.slice) ecrval
      FROM odf_ca_opt_ent_capital_req ecr
      INNER JOIN (  SELECT odf_parent_id,opt_request_type,opt_forecast_type,opt_status,MAX(created_date) createdate FROM odf_ca_opt_ent_capital_req
                    WHERE opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
                    GROUP BY odf_parent_id,opt_request_type,opt_forecast_type,opt_status) latestecr ON latestecr.odf_parent_id=ecr.odf_parent_id
            AND latestecr.opt_request_type=ecr.opt_request_type AND latestecr.createdate=ecr.created_date
            AND latestecr.opt_forecast_type=ecr.opt_forecast_type AND latestecr.opt_status=ecr.opt_status
      INNER JOIN ODF_SL_5038394 ecrtgt ON ecrtgt.prj_object_id=ecr.id
        AND ecrtgt.slice_request_id IN
        (SELECT id FROM prj_blb_slicerequests WHERE UPPER(request_name) =
        'ECR_TARGET_'||@WHERE:PARAM:USER_DEF:STRING:pperiod@ )
      INNER JOIN cmn_lookups_v ecrtype ON ecrtype.lookup_code=ecr.opt_request_type AND ecrtype.lookup_type='OPT_ENTCAPREQTYPE'
          AND ecrtype.language_code=@WHERE:PARAM:LANGUAGE@
      INNER JOIN cmn_lookups_v ecrforecast ON ecrforecast.lookup_code=ecr.opt_forecast_type
         AND ecrforecast.lookup_type='OPT_ERC_FORECAST_TYPE' AND ecrforecast.language_code=@WHERE:PARAM:LANGUAGE@
      INNER JOIN (  SELECT odf_parent_id,opt_request_type,opt_forecast_type,MAX(opt_status) opt_status FROM odf_ca_opt_ent_capital_req
                    WHERE opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
                    GROUP BY odf_parent_id,opt_request_type,opt_forecast_type)maxstat ON maxstat.odf_parent_id=ecr.odf_parent_id
            AND maxstat.opt_request_type=ecr.opt_request_type AND maxstat.opt_forecast_type=ecr.opt_forecast_type AND maxstat.opt_status=ecr.opt_status
      WHERE (ecr.odf_parent_id=(SELECT id FROM odf_ca_opt_investment optinv WHERE optinv.code=@WHERE:PARAM:USER_DEF:STRING:pinvestmnt@)
         OR @WHERE:PARAM:USER_DEF:STRING:pinvestmnt@ IS NULL)
    AND (ecr.opt_forecast_type=@WHERE:PARAM:USER_DEF:STRING:pforecasttype@ OR @WHERE:PARAM:USER_DEF:STRING:pforecasttype@ IS NULL)
    AND opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
      GROUP BY ecr.odf_parent_id,ecrtype.name,ecrforecast.name,ecrtgt.slice_date)
    , ftvals AS (
    SELECT ft.odf_parent_id,fttype.name fttype,ftforecast.name forecasttype,fttgt.slice_date,SUM(fttgt.slice) ftval
      FROM odf_ca_opt_funding_targets ft
      INNER JOIN (  SELECT odf_parent_id,opt_funding_type,opt_forecast_type,MAX(created_date) createdate FROM odf_ca_opt_funding_targets
                    WHERE opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
                    GROUP BY odf_parent_id,opt_funding_type,opt_forecast_type) latestft ON latestft.odf_parent_id=ft.odf_parent_id
            AND latestft.opt_funding_type=ft.opt_funding_type and latestft.createdate=ft.created_date AND latestft.opt_forecast_type=ft.opt_forecast_type
      INNER JOIN ODF_SL_5038373 fttgt ON fttgt.prj_object_id=ft.id
          AND fttgt.slice_request_id IN (SELECT id FROM prj_blb_slicerequests
              WHERE UPPER(request_name) =
    DECODE(@WHERE:PARAM:USER_DEF:STRING:pperiod@,'ANNUALLY','ANNUAL',
    @WHERE:PARAM:USER_DEF:STRING:pperiod@)||'INVFUNDTARGET' )
      INNER JOIN cmn_lookups_v fttype ON fttype.lookup_code=ft.opt_funding_type AND fttype.lookup_type='OPT_FUNDTGTTYPE'
          AND fttype.language_code=@WHERE:PARAM:LANGUAGE@
      INNER JOIN cmn_lookups_v ftforecast ON ftforecast.lookup_code=ft.opt_forecast_type
         AND ftforecast.lookup_type='OPT_FORECASTTYPE' AND ftforecast.language_code=@WHERE:PARAM:LANGUAGE@
      WHERE (ft.odf_parent_id=(SELECT id FROM odf_ca_opt_investment optinv WHERE optinv.code=@WHERE:PARAM:USER_DEF:STRING:pinvestmnt@)
         OR @WHERE:PARAM:USER_DEF:STRING:pinvestmnt@ IS NULL)
      AND (ft.opt_forecast_type=@WHERE:PARAM:USER_DEF:STRING:pforecasttype@ OR @WHERE:PARAM:USER_DEF:STRING:pforecasttype@ IS NULL)
      AND opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
      GROUP BY ft.odf_parent_id,fttype.name,ftforecast.name,fttgt.slice_date)
    , ocvals AS (
    SELECT oc.odf_parent_id,octype.name octype,ocforecast.name forecasttype,octgt.slice_date,SUM(octgt.slice) ocval
      FROM odf_ca_opt_other_costs oc
      INNER JOIN (  SELECT odf_parent_id,opt_cost_type,opt_forecast_type,MAX(created_date) createdate FROM odf_ca_opt_other_costs
                    WHERE opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
                    GROUP BY odf_parent_id,opt_cost_type,opt_forecast_type) latestoc ON latestoc.odf_parent_id=oc.odf_parent_id
            AND latestoc.opt_cost_type=oc.opt_cost_type and latestoc.createdate=oc.created_date and latestoc.opt_forecast_type=oc.opt_forecast_type
      INNER JOIN ODF_SL_5038355 octgt ON octgt.prj_object_id=oc.id
          AND octgt.slice_request_id IN (SELECT id FROM prj_blb_slicerequests
    WHERE UPPER(request_name) = 'OTHERCOST_EXTENDEDPRICE_'||@WHERE:PARAM:USER_DEF:STRING:pperiod@)
      INNER JOIN cmn_lookups_v octype ON octype.lookup_code=oc.opt_cost_type AND octype.lookup_type='OPT_OTHERCOSTTYPE'
          AND octype.language_code=@WHERE:PARAM:LANGUAGE@
      INNER JOIN cmn_lookups_v ocforecast ON ocforecast.lookup_code=oc.opt_forecast_type
         AND ocforecast.lookup_type='OPT_FORECASTTYPE' AND ocforecast.language_code=@WHERE:PARAM:LANGUAGE@
      WHERE (oc.odf_parent_id=(SELECT id FROM odf_ca_opt_investment optinv WHERE optinv.code=@WHERE:PARAM:USER_DEF:STRING:pinvestmnt@)
         OR @WHERE:PARAM:USER_DEF:STRING:pinvestmnt@ IS NULL)
       AND (oc.opt_forecast_type =@WHERE:PARAM:USER_DEF:STRING:pforecasttype@ OR @WHERE:PARAM:USER_DEF:STRING:pforecasttype@ IS NULL)
       AND opt_funding_year=EXTRACT(YEAR FROM SYSDATE)
      GROUP BY oc.odf_parent_id,octype.name,ocforecast.name,octgt.slice_date)
    ,portvals AS(
      SELECT ecrvals.odf_parent_id,ecrvals.ecrtype dim,ecrvals.forecasttype,ecrvals.slice_date,ecrvals.ecrval tgtval
          FROM ecrvals
          UNION ALL
          SELECT ftvals.odf_parent_id,ftvals.fttype dim,ftvals.forecasttype,ftvals.slice_date,ftvals.ftval tgtval
          FROM ftvals
          UNION ALL
          SELECT COALESCE(ftvals.odf_parent_id,ocvals.odf_parent_id) odf_parent_id
          ,'Total Capital (SDSS + Non-SDSS)' dim,NULL forecasttype,COALESCE(ftvals.slice_date,ocvals.slice_date) slice_date
          ,SUM(nvl(ftvals.ftval,0)+nvl(ocvals.ocval,0)) tgtval
          FROM (SELECT ftvals.odf_parent_id,ftvals.slice_date,SUM(ftvals.ftval) ftval FROM ftvals
                WHERE ftvals.fttype IN ('Allocated - Non-SDSS Labor','Allocated - SDSS Labor')
                GROUP BY ftvals.odf_parent_id,ftvals.slice_date)ftvals
          FULL OUTER JOIN (SELECT ocvals.odf_parent_id,ocvals.slice_date,SUM(ocvals.ocval) ocval
                 FROM ocvals
                 WHERE ocvals.octype IN ('ET Labor','Business Labor','OptumInsight Labor','Hardware and Software','Other Costs')
                 GROUP BY ocvals.odf_parent_id,ocvals.slice_date)ocvals ON ftvals.odf_parent_id=ocvals.odf_parent_id AND ftvals.slice_date=ocvals.slice_date
          GROUP BY COALESCE(ftvals.odf_parent_id,ocvals.odf_parent_id),COALESCE(ftvals.slice_date,ocvals.slice_date)
          UNION ALL
          SELECT ocvals.odf_parent_id,ocvals.octype dim,ocvals.forecasttype,ocvals.slice_date,ocvals.ocval tgtval
          FROM ocvals
          UNION ALL
          SELECT ftvals.odf_parent_id,'SDSS' dim,NULL forecasttype,ftvals.slice_date,SUM(ftvals.ftval) tgtval
          FROM ftvals
          WHERE ftvals.fttype ='Allocated - SDSS Labor'
          GROUP BY ftvals.odf_parent_id,ftvals.slice_date
          UNION ALL
           SELECT COALESCE(ftvals.odf_parent_id,ocvals.odf_parent_id) odf_parent_id
          ,'Non-SDSS' dim,NULL forecasttype,COALESCE(ftvals.slice_date,ocvals.slice_date) slice_date
          ,SUM(nvl(ftvals.ftval,0)+nvl(ocvals.ocval,0)) tgtval
          FROM (SELECT ftvals.odf_parent_id,ftvals.slice_date,SUM(ftvals.ftval) ftval FROM ftvals
                WHERE ftvals.fttype IN ('Allocated - Non-SDSS Labor')
                GROUP BY ftvals.odf_parent_id,ftvals.slice_date)ftvals
          FULL OUTER JOIN (SELECT ocvals.odf_parent_id,ocvals.slice_date,SUM(ocvals.ocval) ocval
                 FROM ocvals
                 WHERE ocvals.octype IN ('ET Labor','Business Labor','OptumInsight Labor','Hardware and Software','Other Costs')
                 GROUP BY ocvals.odf_parent_id,ocvals.slice_date)ocvals ON ftvals.odf_parent_id=ocvals.odf_parent_id AND ftvals.slice_date=ocvals.slice_date
          GROUP BY COALESCE(ftvals.odf_parent_id,ocvals.odf_parent_id),COALESCE(ftvals.slice_date,ocvals.slice_date)
          UNION ALL
          SELECT ecrvals.odf_parent_id,'Enterprise Capital Reporting' dim,NULL forecasttype,ecrvals.slice_date,SUM(ecrvals.ecrval) tgtval
          FROM ecrvals
          GROUP BY ecrvals.odf_parent_id,ecrvals.slice_date
           )
    ,dims AS (
      SELECT 'Total Capital (SDSS + Non-SDSS)' dtype,'Total Capital (SDSS + Non-SDSS)' dname,1 seq,1 high FROM DUAL
      UNION ALL
      SELECT 'SDSS' dtype,' '||' '||' '||'SDSS' dname,2 seq,1 high FROM DUAL
      UNION ALL
      SELECT 'Allocated - SDSS Labor' dtype,' '||' '||' '||' '||'Allocated' dname, 3 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Funded - SDSS Labor' dtype,' '||' '||' '||' '||' '||'Funded' dname,4 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Pipeline - SDSS Labor' dtype,' '||' '||' '||' '||' '||'Pipeline' dname, 5 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Non-SDSS' dtype,' '||' '||' '||'Non-SDSS' dname, 6 seq,1 high FROM DUAL
      UNION ALL
      SELECT 'Allocated - Non-SDSS Labor' dtype,' '||' '||' '||' '||'Allocated' dname,7 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Funded - Non-SDSS Labor' dtype,' '||' '||' '||' '||' '||'Funded' dname,8 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Pipeline - Non-SDSS Labor' dtype,' '||' '||' '||' '||' '||'Pipeline' dname,9 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'ET Labor' dtype,' '||' '||' '||' '||'ET Labor' dname, 10 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Business Labor' dtype,' '||' '||' '||' '||'Business Labor' dname,11 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'OptumInsight Labor' dtype,' '||' '||' '||' '||'OptumInsight Labor' dname,12 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Hardware and Software' dtype,' '||' '||' '||' '||'Hardware and Software' dname,13 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Professional Services Fees' dtype,' '||' '||' '||' '||'Professional Services Fees' dname,14 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Other Costs' dtype,' '||' '||' '||' '||'Other Costs' dname,15 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'Enterprise Capital Reporting' dtype,'Enterprise Capital Reporting' dname,16 seq,1 high FROM DUAL
      UNION ALL
      SELECT 'ECR Data - SDSS' dtype,' '||' '||' '||'ECR Data - SDSS' dname,17 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'ECR Data - Non-SDSS Labor' dtype,' '||' '||' '||'ECR Data - Non-SDSS Labor' dname, 18 seq,0 high FROM DUAL
      UNION    ALL
      SELECT 'ECR Data - Total Cap' dtype,' '||' '||' '||'ECR Data - Total Cap' dname,19 seq,0 high FROM DUAL
      UNION ALL
      SELECT 'ECR Data - Total Non-Cap' dtype,' '||' '||' '||'ECR Data - Total Non-Cap' dname,20 seq,0 high FROM DUAL
    )
    SELECT optinv.id invid,optinv.name,bizperiods.period_name
    ,DECODE(bizperiods.period_type,'ANNUALLY',SUBSTR(bizperiods.period_name,0,4),'MONTHLY',TO_CHAR(bizperiods.start_date,'Month YY'),bizperiods.period_name) displayname
    ,null dname,null forecasttype, null tgtval,null seq,0 high
    ,optinv.id hg_has_children,optinv.opt_portfolio portfolio,optinv.code invfilter
    FROM odf_ca_opt_investment optinv
    INNER JOIN (SELECT DISTINCT period_name,biz.period_type,biz.start_date,biz.end_date
    FROM biz_com_periods biz WHERE biz.period_type=@WHERE:PARAM:USER_DEF:STRING:pperiod@
      AND biz.start_date<=@WHERE:PARAM:USER_DEF:DATE:pend@ and biz.end_date-1>=
    @WHERE:PARAM:USER_DEF:DATE:pstart@
      )bizperiods ON 1=1
    WHERE (optinv.code=@WHERE:PARAM:USER_DEF:STRING:pinvestmnt@ OR @WHERE:PARAM:USER_DEF:STRING:pinvestmnt@ IS NULL)
    AND optinv.opt_is_active=1
    AND optinv.id IN (SELECT odf_parent_id FROM portvals)
    AND (@WHERE:PARAM:USER_DEF:INTEGER:psegobs@ IS NULL
      OR optinv.id IN (SELECT asso.record_id
           FROM prj_obs_associations asso WHERE asso.table_name='opt_investment'
            AND asso.unit_id=@WHERE:PARAM:USER_DEF:INTEGER:psegobs@))

    AND @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ IS NULL
    AND (optinv.opt_ucmg_id=@WHERE:PARAM:USER_DEF:STRING:pucmgid@ OR @WHERE:PARAM:USER_DEF:STRING:pucmgid@ IS NULL)
    UNION ALL
    SELECT optinv.id invid
    ,DECODE(@where:param:user_def:integer:hg_all_rows@,1,optinv.name,NULL) name,bizperiods.period_name
    ,DECODE(bizperiods.period_type,'ANNUALLY',SUBSTR(bizperiods.period_name,0,4),@WHERE:PARAM:USER_DEF:STRING:pperiod@,TO_CHAR(bizperiods.start_date,'Month YY'),bizperiods.period_name) displayname
    ,dims.dname,fct.forecasttype,portvals.tgtval,dims.seq,dims.high, null hg_has_children
    ,optinv.opt_portfolio portfolio,optinv.code invfilter
    FROM odf_ca_opt_investment optinv
    INNER JOIN (SELECT DISTINCT period_name,biz.period_type,biz.start_date,biz.end_date
    FROM biz_com_periods biz WHERE biz.period_type=@WHERE:PARAM:USER_DEF:STRING:pperiod@
      AND biz.start_date<=@WHERE:PARAM:USER_DEF:DATE:pend@ and biz.end_date-1>=@WHERE:PARAM:USER_DEF:DATE:pstart@
      )bizperiods ON 1=1
    INNER JOIN dims ON 1=1     
    LEFT OUTER JOIN portvals ON portvals.odf_parent_id=optinv.id AND portvals.slice_date BETWEEN bizperiods.start_date AND bizperiods.end_date-1
      AND dims.dtype=portvals.dim
    LEFT OUTER JOIN (SELECT dim,max(forecasttype)forecasttype from portvals group by dim) fct ON fct.dim=dims.dtype
    WHERE optinv.id=@WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@
    OR (@where:param:user_def:integer:hg_all_rows@ = 1 AND
    optinv.id IN (SELECT odf_parent_id FROM portvals))
    ) invvals
    WHERE @FILTER@
    AND ROWNUM<(select opt_query_limit from odf_ca_opt_system_settings)



  • 2.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 02:35 AM

    Hi Sravani,

     

    If you use the same query and filter, the number of rows you see in CA PPM is that different from export, actually what you see in the UI should be exported back.

     

    Regards

    Suman Pramanik



  • 3.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 02:51 AM

    Hi Suman,

     

    I'm using the same query mentioned above. The GUI is showing only the filtered data but upon doing Export to excel (data only) entire data is returned without filter.

     

    Example: I'm filtering for a particular Segment OBS "M&V"  - GUI shows only data related to M&V segment but export to excel(data only) returns  data for all the segments .



  • 4.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 02:55 AM

    Hi Sravani,

     

    Can you share the screen shot of filter data and then exported data.

     

    Regards

    Suman Pramanik



  • 5.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 03:12 AM

    Hi Suman,

     

    Please find the below screen shot for filtered data and attached file for export to excel (data only)

     

     

     

    Thanks,

    Sravani

    PPM Optics SS&MO

    Mobile-9700705632, Office-04030895977

    Attachment(s)



  • 6.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 03:16 AM

    Hi Suman,

     

    Please find the below Screen shots.

     

     



  • 7.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 03:34 AM

    Hi Sravani,

     

    This helps me a bit, can you expand one investment and see the sub projects and then compare the data, as in Excel everything comes as other separate row. just trying to lower the volume of data and check.

     

    Regards

    Suman Pramanik



  • 8.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 04:27 AM

    Hi Suman,

     

    In GUI I have expanded the Investment M&V Customer requests. Please refer the below screenshot.

     

     

     

    Below are corresponding rows for that investment  from excel

     

     

     

     

     

    As we can see the order of the columns are also exported incorrectly to the excel.

     

     

    Thanks,

    Sravani

    PPM Optics SS&MO

    Mobile-9700705632, Office-04030895977



  • 9.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.
    Best Answer



  • 10.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 10:09 AM

    When you are in the UI viewing a hierarchical grid, it shows each set of children below its parent as you expand them one by one.  Each level being displayed is a separate query execution.

     

    So for example, let's say you first visit the portlet and only the top levels are showing for your filter criteria.  This is one query execution.

     

    For the first parent or top-level item you drill down 3 levels, that's 3 more query executions to fetch that data - the original query results for the parent information isn't re-fetched.

     

    Now you drill down a level on 2 other top-level items, that's 2 more query executions.

     

    To view the data showing to you so far has taken 6 queries to execute and each time only the subset of data belonging to a single parent (except for the first execution) is returned in the results - making it easy to 'inject' those rows in the hierarchy displayed directly below the parent.

     

    When the Export to Excel query executes, it executes just 1 time only for all the levels in the hierarchy simultaneously.

     

    Therefore, the way (and order) that data comes out isn't necessarily going to be as it was in the UI for the same criteria.  Your criteria will be applied correctly in both cases, but the child rows will not be automatically placed below each parent in the hierarchy as showing in the UI.

     

    To get close to that ordering, you should begin by having your dimension key for your query providing values that if sorted, produces the results in the correct order.

     

    That means this column: @SELECT:DIM:USER_DEF:IMPLIED:INVESTMENT:invid||seq:hiddenid@

     

    Typically, hierarchical grid queries don't merge the UI and export portions of the query together managed by a function like decode().  Whilst you might be able to construct the logic this way, it may make for a more complex query management.

     

    More typically (or at least, more often I have seen) the query designed out of unions, and you have a separate union for each of these conditions:

     

    1) The parent / top-level items to be shown in the UI (based on hg_row_id being set to null)

    2) The child / sub-level items to be shown in the UI (based on hg_row_id being a numeric value that refers to the parent item)

    3) All (filtered) items for exporting to excel (based on hg_all_rows being 1)

     

    The rest is down to your query design (which the above information and your sql traces should help you to create).



  • 11.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-02-2015 10:12 AM

    Hi Suman,

     

    The article helped me. The portlet is working after making all the parameters defined in where clause as required fields and also the portfolio field which is not a part of where clause. But by doing this user might be facing problem to fill all the required fields because finding the UCMGID corresponding to that particular investment or finding a portfolio of that particular investment would be a manual work for users.

     

    Could you please suggest me the best approach to solve this.  Is there a way where we can update the NSQL to remove the UCMGID and investment from where clause or some other modifications on the query.

     

     

     

     

     

    Thanks,

    Sravani

    PPM Optics SS&MO

    Mobile-9700705632, Office-04030895977



  • 12.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-03-2015 01:06 AM

    Hi Sravani,

     

    Glad to hear that portlet is working as expected, please see Nick input above. Hope it helps

     

    Regards

    Suman Pramanik



  • 13.  Re: The export to excel (data only) option shows the entire data instead of showing the filtered ones. Seems like the issue is with hg_all_rows parameter. Also I hope Decode function is also causing the issue.

    Posted 11-18-2015 09:07 AM

    Hi Suman /Nick

     

    I have analyzed the query as per suggestions below. I suspect the problem is with Decode function

    Internally engine is passing 2 as parameter for hg_all_rows there by the decode function becomes DECODE (2,1,optinv.name,NULL) so the name feild becomes null and only the filtered rows are selected. This is working fine.

     

    But in case of export to excel 1 is passed as parameter for hg_all_rows in this case the decode function becomes DECODE (1,1,optinv.name,NULL) so it is all fetching the values from optinv table and returning the entire data.

     

    I knew that system is designed in such a way to pass 1 as parameter for hg_all_rows  in hierarchical NSQL queries so as to view the child records . But not sure how to correct he issue.  

     

    Your suggestions would really help me.

     

     

    SELECT optinv.id invid
    ,DECODE(@where:param:user_def:integer:hg_all_rows@,1,optinv.name,NULL) name,bizperiods.period_name
    ,DECODE(bizperiods.period_type,'ANNUALLY',SUBSTR(bizperiods.period_name,0,4),@WHERE:PARAM:USER_DEF:STRING:pperiod@,TO_CHAR(bizperiods.start_date,'Month YY'),bizperiods.period_name) displayname
    ,dims.dname,fct.forecasttype,portvals.tgtval,dims.seq,dims.high, null hg_has_children
    ,optinv.opt_portfolio portfolio,optinv.code invfilter
    FROM odf_ca_opt_investment optinv
    INNER JOIN (SELECT DISTINCT period_name,biz.period_type,biz.start_date,biz.end_date
    FROM biz_com_periods biz WHERE biz.period_type=@WHERE:PARAM:USER_DEF:STRING:pperiod@
      AND biz.start_date<=@WHERE:PARAM:USER_DEF:DATE:pend@ and biz.end_date-1>=@WHERE:PARAM:USER_DEF:

     

     

    Making the columns in where condition as required fields is
    solving the issue because we are filtering the where clause by passing values to  the parameters, but it becomes difficult for the users to use such
    functionality as there are too many columns in where conditions. They find it
    difficult to search for investment that corresponds to UCMGID if both the
    UCMGID and Investment fields are marked as required felids. Below is the
    screenshot from GUI. In my case I need to mark Investment, UCMGID and Segment
    OBS as required fields.


    Thank you

    Sravani