Clarity PPM

Expand all | Collapse all

How to build custom grid portlet with time-scaled values?

Jump to Best Answer

Martti Kinnunen12-07-2018 05:24 AM

  • 1.  How to build custom grid portlet with time-scaled values?

    Posted 04-24-2009 01:56 AM
    Hi,  I would like to build a portlet on a custom query and include a time-scaled value on a time slice (monthly) date, is this possible?I have tried multiple types of queries, 1-dimensional, 2-dimensional etc, but I never have the "Display type" option to add a Time-scaled value to the corresponding portlet?  Any clues or examples would be great!  Regards,Sven


  • 2.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-02-2009 12:14 PM
    I am also in need of an answer about this, please anyone????


  • 3.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-02-2009 09:57 PM
      |   view attached
    I think you are seeking a way to mimic the out-of-the-box grids that permit dynamic change left/right to the displayed timescale (like the attached example).If so; I believe you cannot mimic this behaviour through NSQL based grid portlets regardless of how you spread data over time units.        


  • 4.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-02-2009 10:55 PM
    Agreed you can't have dynamic change left/right to the displayed timescale (like the attached example), but isn't that a feature you need when you have no idea what the portle is used for.If you on the other hand know that you want to see the actuals for three months into the past and ETC for three months to the future and the months to roll as time goes by you can build that in MS SQL with elements like the one below inside the wrapper  left join

    (select    
    niku.prtask.prprojectid pid,
    niku.prassignment.prresourceid rid,
    year(niku.prj_blb_slices.slice_date) Year,
    month(niku.prj_blb_slices.slice_date) Month,
    isnull(sum(niku.prj_blb_slices.slice),0) Actual_hours
    from
    niku.prj_blb_slices,
    niku.prj_blb_slicerequests,
    niku.prtask,
    niku.prassignment
    where
    niku.prj_blb_slices.slice_date > dateadd(m,-1,(getdate()))
    and niku.prj_blb_slices.slice_date


  • 5.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-04-2009 12:12 AM
    Hi,    The Main drawback (I hope & thought)  is that we can't change the  name of the attributes  defined in the Portlet View & Filter not the Data.That is static and we need to change it  Periodically.  For example what martti sql is dynamic and it is based on sysdate.If you consider the current  month Sep'09 we will have actuals shown for   June,July,Aug'09 then Sep'09 and Oct'09 and Nov'09 will be the ETC.so for the next month the data will be automatically changed  based on the  sysdate defined in the Query but the column names in the List View we need to change the Month and Year Name.  We can handle  all the logic  in the Query Itself and just display in the portlet.we can have the values  for the whole year (Jan-Until-actuals)  so that  put the  12  columns (Jan-sep'09 actuals and remaining ETC)  in the portlet and show only the  required columns  and user can configure and take whatever columns he need.User must be educated to do that.  Scrolling -- Will CA provide that facility to custom portlets it would be an added advantage.(Like weekly allocation column (Virtual) in Resource Worloads Porlet under Resource Planning)  Regards,Sundar


  • 6.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-04-2009 03:49 AM
    I'd like to suggest 2 actions.  1. Raise an enhancement request (PLEASE!). The static nature of column labels is a very valid point if the unerlying query is dynamic.  2. [Maybe?] Use a process not associated with an object, these can be scheduled.
    In that process include a GEL step which XOG reads the existing portlet (no dependencies) and updates the wanted labels in the XOG structure, then writes that updated portlet definition back to Clarity. [Possible: XML query and XML transforms might be employed to simplify the update portlet XOG structure.]

    Once such a processwas available you could schedule it as appropriate.  all point 2. untried


  • 7.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-04-2009 06:58 AM
    An alternative to the dynamic labels would be to get the same functionality as in Weekly Detail and Resource Workload portlets also to user defined portlets.  While wainting for the ERQ for you current release you might consider a work around:  put at the beginning of the query inside the wrapper  Select
     Datename (Month, dateadd(m,-3,getdate())) 'Month - 3',
     Datename (Month, dateadd(m,-2,getdate())) 'Month - 2',
     Datename (Month, dateadd(m,-1,getdate())) 'Month - 1',
     Datename (Month, getdate()) 'Current Month',
     Datename (Month, dateadd(m,1,getdate())) 'Month + 1',
     Datename (Month, dateadd(m,2,getdate())) 'Month + 2',
     Datename (Month, dateadd(m,3,getdate())) 'Month + 3'
    UNION ALL  and convert the result data from the main query to the same data type.  Martti K.


  • 8.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-07-2009 03:47 AM
    Martti,  Good Stuff.I enjoyed it.  For Oracle i modified the Query and here is it.  select (select unique_name from srm_projects s where s.id=project_id) "Project Id",
    (select unique_name from srm_resources s where s.id=resource_id)"Resource Id",
    nvl(sum(Month_3),0) "Month - 3" , nvl(sum(Month_2),0) "Month - 2", nvl(sum(Month_1),0) "Month - 1" ,
    nvl(sum(Cur_Month),0) "Current Month",nvl(sum(Month1),0) "Month + 1", nvl(sum(Month2),0)"Month + 2" ,
    nvl(sum(Month3),0) "Month + 3"
    from
    (
    SELECT Project_Id,
    Resource_Id,
    case when REQUEST_NAME='MONTHLYRESOURCEACTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month_3,
    case when REQUEST_NAME='MONTHLYRESOURCEACTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, -2),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month_2,
    case when REQUEST_NAME='MONTHLYRESOURCEACTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month_1,case when REQUEST_NAME='MONTHLYRESOURCEACTCURVE' and TRUNC(SYSDATE,'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Cur_Month,case when REQUEST_NAME='MONTHLYRESOURCEESTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, +1),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month1,
    case when REQUEST_NAME='MONTHLYRESOURCEESTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, +2),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month2,
    case when REQUEST_NAME='MONTHLYRESOURCEESTCURVE' and TRUNC(ADD_MONTHS(SYSDATE, +3),'MM')=SLICE_MONTH then nvl(sum(Actual_hours),0) else 0 end Month3
    from
    (
    select  
    prtask.prprojectid project_id,
    prassignment.prresourceid resource_id,request_name,
    to_char(TRUNC(prj_blb_slices.slice_date,'YEAR'),'YYYY') Year,
    TRUNC(prj_blb_slices.slice_date,'MONTH') SLICE_MONTH,
    nvl(sum(prj_blb_slices.slice),0) Actual_hours
    from
    prj_blb_slices,
    prj_blb_slicerequests,
    prtask,
    prassignment
    where
    prj_blb_slices.slice_date >= TRUNC(SysDate,'YEAR')
    and prj_blb_slices.slice_date


  • 9.  Re: How to build custom grid portlet with time-scaled values?

    Posted 09-07-2009 07:58 AM
    Thanks SUndar, you certainly are quick with SQL.    Martti K.


  • 10.  RE: Re: How to build custom grid portlet with time-scaled values?

    Posted 08-04-2010 09:53 AM
      |   view attached
    Hi

    I used the IE Developer tool to check what is behind this TSV field (from the Resource Planning > Workloads > Resource Workloads portlet), and found the below code somehow -

    [color=#233ACC]javascript:gridTimePeriodScroll('page_5001002','npt.timePeriodScroll','type=prev','portletInstanceId=5001002','','numberOfTimePeriods=6','','startDateMacro=currentWeek','timeScale=yearWeek','startDateType=macro' ...[color]

    I've attached the screenshot as well...

    Seems to be a good functionality if we can get the code...

    Regards
    NJ


  • 11.  RE: Re: How to build custom grid portlet with time-scaled values?
    Best Answer

    Posted 09-11-2012 01:00 AM
      |   view attached

    sundar wrote:


    For example what martti sql is dynamic and it is based on sysdate.If you consider the current  month Sep'09 we will have actuals shown for   June,July,Aug'09 then Sep'09 and Oct'09 and Nov'09 will be the ETC.so for the next month the data will be automatically changed  based on the  sysdate defined in the Query but the column names in the List View we need to change the Month and Year Name.
    I have done this using multi dimention portlet. Where header of the column will be one of the dimension and I get label data from query.

    NSQL Select :
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:DIM1:res.id:id@,
               @SELECT:DIM:USER_DEF:IMPLIED:DIM2:res.date_str:DateSTR@,
               @SELECT:DIM:USER_DEF:IMPLIED:DIM3:res.What:What@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:res.FULL_NAME:Resource_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:res.unique_name:Resource_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:res.Category:Category@,
          @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:u.last_name || ', '||u.first_name:manager_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:u.id:manager_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:JSLS_CUR_QTY:cur_qty@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM2:res.alloc_date:alloc_date@,
    @SELECT:METRIC:USER_DEF:IMPLIED:SUM(res.qty):qty@


  • 12.  RE: Re: How to build custom grid portlet with time-scaled values?

    Posted 05-15-2013 01:22 PM
    How to handle sorting in two dimensional Portlet. If you sort columns of your porlet does date range displays correctly ?


    Thanks

    Regards
    Gaurav


  • 13.  RE: Re: How to build custom grid portlet with time-scaled values?

    Posted 05-16-2013 02:58 AM
    "If you sort columns of your portlet does date range displays correctly ?" - Don't think so (for custom portlets) :tongue:

    NJ


  • 14.  RE: Re: How to build custom grid portlet with time-scaled values?

    Posted 08-03-2010 04:04 PM
    Sorry, it looks as though your original post has been cut off. Would you be able to re-post the full query? I am trying to do something very similar, and could use all the help I can get!


  • 15.  RE: Re: How to build custom grid portlet with time-scaled values?

    Posted 09-06-2012 04:31 PM
    The full query was never there. Instead there were just sections as examples of how you could do the columns.

    Martti K.


  • 16.  Re: How to build custom grid portlet with time-scaled values?

    Posted 12-07-2018 05:24 AM

    https://communities.ca.com/servlet/JiveServlet/download/2284026-1-45664/tsv-portlet-example.jpg