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):
- 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
- 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@