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

[Anon Anon]

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

[furrelkt7]

I am also in need of an answer about this, please anyone????

[Anon Anon]

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.        

[Deactivated User]

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

[Anon Anon]

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

[Anon Anon]

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

[Deactivated User]

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.

[Anon Anon]

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

[Deactivated User]

Thanks SUndar, you certainly are quick with SQL.    Martti K.

[Anon Anon]

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

[Naman Patel]

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@

[Anon Anon]

How to handle sorting in two dimensional Portlet. If you sort columns of your porlet does date range displays correctly ?


Thanks

Regards
Gaurav

[navzjoshi00]

"If you sort columns of your portlet does date range displays correctly ?" - Don't think so (for custom portlets) :tongue:

NJ

[Anon Anon]

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!

[Deactivated User]

The full query was never there. Instead there were just sections as examples of how you could do the columns.

Martti K.

[Deactivated User]