AnsweredAssumed Answered

Portlet destroys result of query

Question asked by msnizek on Jan 29, 2015
Latest reply on Jan 30, 2015 by msnizek

Hello,

 

I built a query which I want to use as a data provider for portlet. Query results Are fine for me so I transferred it to NSQL and entered to Clarity, tried preview on query and tested the query again - result was fine for me, but when I created a portlet the data were destroyed. Let me describe how (parameter is date 1.12.2014):

  1. The portlet is multidimensional and in collumn headers should be date range 1st day of week - last day of week
    • query in SQL developer returns for example 08.12.2014 - 14.12.2014, but the portlet shows 12.12.0001 - 18.12.0001
    • you can also see that 12.12. is not the first day of week
  2. The portlet does not show any column where data are exported by query = show only columns with 0 values

 

Are there some frequent mistakes which can cause this? Does anyone has experience with similar behavior?

 

Thanks in advance,

Martin

 

Here is the nsql query:

SELECT  

  @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.res_id_filter:res_id_filter@,

  @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.res_name:res_name@,

  @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.manager_id_filter:manager_id_filter@,

  @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.manager_name:manager_name@,

   @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:case when R.ay_agg is null then 0 else r.ay_agg end:ay_agg@,

  @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:case when R.ey_agg is null then 0 else r.ey_agg end:ey_agg@,

  @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:case when R.diffy_agg is null then 0 else r.diffy_agg end:diffy_agg@,

  @SELECT:metric:user_def:IMPLIED:case when R.diff_agg is null then 0 else r.diff_agg end:diff_agg@,

  @SELECT:metric:user_def:IMPLIED:case when R.e_agg is null then 0 else r.e_agg end:e_agg@,

  @SELECT:metric:user_def:IMPLIED:case when R.a_agg is null then 0 else r.a_agg end:a_agg@,

  @SELECT:DIM:USER_DEF:IMPLIED:TIMEPERIOD:R.label:label_week@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEPERIOD:R.week_st:week_st@

from

(select

to_char(week_start,'DD.MM.YYYY')||' - '||to_char(week_end,'DD.MM.YYYY') label,

datum.week_start week_st,

-- actuals aggregation

(select sum(slice) from prj_blb_slices where slice_request_id=2

  and (select prresourceid from prassignment where prid=prj_object_id)=res.id

  and slice_date>=datum.week_start and slice_date<=datum.week_end) a_agg,

-- entry system aggregation

(select round((sum(c_ent_minutes)/60),0) from odf_ca_c_entry_system

  where c_ent_resource=res.unique_name and c_ent_day>=week_start and c_ent_day<=week_end) e_agg,

-- difference aggregation

((select round((sum(c_ent_minutes)/60),0) from odf_ca_c_entry_system where c_ent_resource=res.unique_name and c_ent_day>=datum.week_start and c_ent_day<=datum.week_end))-((select sum(slice) from prj_blb_slices where slice_request_id=2

  and (select prresourceid from prassignment where prid=prj_object_id)=res.id

  and slice_date>=datum.week_start and slice_date<=datum.week_end)) diff_agg,

 

 

  -- actuals aggregation

(select sum(slice) from prj_blb_slices where slice_request_id=2

  and (select prresourceid from prassignment where prid=prj_object_id)=res.id

  and slice_date>=prvni_tyden and slice_date<=prvni_tyden+55) ay_agg,

-- entry system aggregation

(select round((sum(c_ent_minutes)/60),0) from odf_ca_c_entry_system where c_ent_resource=res.unique_name and c_ent_day>=datum.week_start and c_ent_day<=datum.week_end+55) ey_agg,

-- difference aggregation

((select round((sum(c_ent_minutes)/60),0) from odf_ca_c_entry_system where c_ent_resource=res.unique_name and c_ent_day>=datum.week_start and c_ent_day<=datum.week_end+55))-((select sum(slice) from prj_blb_slices where slice_request_id=2

  and (select prresourceid from prassignment where prid=prj_object_id)=res.id

  and slice_date>=prvni_tyden and slice_date<=prvni_tyden+55)) diffy_agg,

  last_name||' '||first_name res_name,

res.id res_id_filter,

(select last_name||' '||first_name from cmn_sec_users where id=res.manager_id) manager_name,

res.manager_id manager_id_filter

from

srm_resources res,

(select

to_date(to_char(iw_week,'DD.MM.YYYY'),'DD.MM.YYYY') week_start,

to_date(to_char(iw_week+6,'DD.MM.YYYY'),'DD.MM.YYYY') week_end,

to_char(iw_week,'DD.MM.YYYY')||' - '||to_char(iw_week+6,'DD.MM.YYYY') week_lbl,

to_date(to_char(zacatek_prvni_tyden,'DD.MM.YYYY'),'DD.MM.YYYY') prvni_tyden

from

(SELECT DISTINCT

  TRUNC(to_date(substr(@WHERE:PARAM:USER_DEF:DATE:time_from@,1,10),'YYYY-MM-DD')+(rownum+1),'iw') IW_week,

  TRUNC(to_date(substr(@WHERE:PARAM:USER_DEF:DATE:time_from@,1,10),'YYYY-MM-DD')+(rownum+1),'iw') zacatek_prvni_tyden,

  TO_CHAR(TRUNC(to_date(substr(@WHERE:PARAM:USER_DEF:DATE:time_from@,1,10),'YYYY-MM-DD')+(rownum+1),'iw'),'iw') AS week_num

FROM dual

  CONNECT BY level <= 49

) order by iw_week

) datum

where (manager_id=(select user_id from srm_resources where id=@WHERE:PARAM:USER_DEF:INTEGER:P_RM@) or @WHERE:PARAM:USER_DEF:INTEGER:P_RM@ is null) and (id=@WHERE:PARAM:USER_DEF:INTEGER:P_RESOURCE@ or @WHERE:PARAM:USER_DEF:INTEGER:P_RESOURCE@ is null)) r

where @FILTER@

Outcomes