AnsweredAssumed Answered

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.

Question asked by sravani_nidamanuri on Oct 8, 2015
Latest reply on Nov 18, 2015 by sravani_nidamanuri

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)

Outcomes