You would like to create a custom portlet with SQL curve period data and make sure the portlet has optimal performance.
All Supported Clarity Releases
We recommend using the function nk_sum_slices_row_fct for optimal performance with SQL Curve periods
Example queries you can use to adapt to your use case:
Note : You must replace the entity "Corporate" with the entity name you are using on your system
with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate') slices_calendar from dual )
SELECT prid, slices.*
FROM prteam, nk_sum_slices_row_fct(odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) slices
with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prid, slices.*
FROM prj_resources, nk_sum_slices_row_fct(odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) slices
with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate') slices_calendar from dual )
SELECT prid, slices.*
FROM prteam, nk_sum_slices_row_fct(odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) slices
with slices_calendar as (select CRV_CALENDAR_FCT('months',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prteam.prid, avail_slices.ts1 avail_ts1, avail_slices.ts2 avail_ts2, alloc_slices.ts1 alloc_ts1, alloc_slices.ts2 alloc_ts2, avail_slices.ts1 - alloc_slices.ts1 remain_ts1
FROM prteam, prj_resources
, nk_sum_slices_row_fct(prj_resources.odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) avail_slices
, nk_sum_slices_row_fct(prteam.odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) alloc_slices
WHERE prteam.prresourceid = prj_resources.prid
with slices_calendar as (select CRV_CALENDAR_FCT('months',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,12,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prj_resources.prid, max(avail_slices.ts1) avail_ts1, max(avail_slices.ts2) avail_ts2, sum(alloc_slices.ts1) alloc_ts1, sum(alloc_slices.ts2) alloc_ts2, count(*) cnt
FROM prteam, prj_resources
, nk_sum_slices_row_fct(prj_resources.odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) avail_slices
, nk_sum_slices_row_fct(prteam.odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) alloc_slices
WHERE prteam.prresourceid = prj_resources.prid
GROUP BY prj_resources.prid
Note: When the periods are set to "default" then what you set for entity does not matter it will return all the data. If the period is fiscal, then it will need the correct entity or else it returns nulls.