Clarity

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)

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

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 .

Hi Sravani,

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

Regards

Suman Pramanik

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

Hi Suman,

Please find the below Screen shots.

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

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

Hi Sravani,

See if this helps

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec534932.aspx

Regards

Suman Pramanik

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).

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

Hi Sravani,

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

Regards

Suman Pramanik

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