Clarity

Expand all | Collapse all

Portlet destroys result of query

Jump to Best Answer
  • 1.  Portlet destroys result of query

    Posted 01-29-2015 11:15 AM

    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@



  • 2.  Re: Portlet destroys result of query

    Posted 01-29-2015 11:28 AM

    msnizek wrote:

     

      • 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

     

    Not trying to debug everything, but looking at your code quickly you are explicitly coding date formats - your SQL*Developer NLS settings are quite likey NOT the same as the application's NLS settings - this all could be the cause of your confusion (date formats and date "ordering").

     

    ...the general best practice would be to leave all the "date" fields in your query as raw DATE datatypes ; Clarity can handle these in the GUI OK, and do the ordering correctly if it is a date field, it will order stuff "alphabetically" if you manually convert the date to a string.  i.e. doing this will cope when a user with a different locale to the one that you use tries to use your portlet.



  • 3.  Re: Portlet destroys result of query

    Posted 01-30-2015 02:51 AM

    ... the secnod thing you can try is to make the filter to take the date from a date picker which would it make it automatically the correct NLS date format.



  • 4.  Re: Portlet destroys result of query

    Posted 01-30-2015 03:55 AM

    Hello, can you please describe me how to do that?

    Thanks in advance,

    Martin



  • 5.  Re: Portlet destroys result of query
    Best Answer

    Posted 01-30-2015 04:10 AM

    Since your date-filter parameters (eg @WHERE:PARAM:USER_DEF:DATE:time_from@ ) is already a date field (the :DATE: part of that definition) it should already be a date-type when used as a filter in the portlet (and automatically gets the date-picker I would expect)?

     

    --

     

    My earlier point about treating dates as dates means that you should not be doing things like;

     

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

     

    because what you are doing there is taking a input date format field, substring-ing it (and I have no idea what a substring on a Clarity date format does?), converting the result back to a date format, then adding an interval to it... and then TRUNCing the whole lot (this is all prone to unexpected results)......  this could be a lot simpler as just TRUNC(date_field+interval)



  • 6.  Re: Portlet destroys result of query

    Posted 01-30-2015 08:45 AM

    Thank you Dave, it helped me and the portlet works now as I expect.

     

    Regards,

    Martin