Clarity

There is a requirement for creation of chart portlet in our application. The chart portlet should display the project timesheets status for a particular time period to his project manager. That means the project manager should see for particular time period how many timesheets are in submitted, approved or posted status for a project by the resources . Also how many hours has been submitted.

So this has to be created as bars graph. The attached screenshot can give more info.

The NSQL query has been created, but the portlet does not show data as required.

Can you anyone help me in this?

I think you'll need to post the NSQL that you have!

Select

@SELECT:DIM:USER_DEF:IMPLIED:DATA:temp.uniq:uniqid@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.proj_id:project_id@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.project_name1:project_names@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.status_id:Tstatus_id@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.timesheet_status:timesheet_status@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.count:count@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.hours:hours@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:temp.project_manager:project_manager@

from

(SELECT

project_name ||','||prstatus ||','||project_manager uniq,

proj_key proj_id,

project_name project_name1,

prstatus status_id,

timesheet_status timesheet_status,

count(*) count,

sum(hours) hours,

project_manager project_manager

from

(SELECT srmr.full_name resource_name,

         srmr.unique_name AS resource_id,

         srmr.ID res_key,

         cmn.ID manager_key,

         tp.prstart,

         tp.prfinish,

         prodcal.philips_week AS week_number,

         SRMP.ID AS proj_key,

         srmp.unique_name AS project_unique_name,

         srmp.NAME AS project_name,

         SPM.FULL_NAME AS PROJECT_MANAGER,

         SPM.USER_ID as Proj_mgr_key,

         ROUND ( (SUM (te.practsum) / 3600), 2) AS hours,

         ts.prstatus,

         DECODE (ts.prstatus,

                 0, 'Open',

                 1, 'Submitted',

                 2, 'Returned',

                 3, 'Approved',

                 4, 'Posted',

                 'Other')

            AS timesheet_status

         FROM srm_resources srmr

         INNER JOIN prtimesheet ts

            ON srmr.ID = ts.prresourceid

         INNER JOIN prtimeperiod tp

            ON ts.prtimeperiodid = tp.prid

         INNER JOIN z_sparc_prod_cal prodcal

            ON tp.prstart = prodcal.fact_date

         INNER JOIN prtimeentry te

            ON ts.prid = te.prtimesheetid

         LEFT JOIN cmn_sec_users cmn

            ON srmr.manager_id = cmn.ID

         LEFT JOIN srm_resources srmrm

            ON srmrm.user_id = cmn.ID

         LEFT JOIN prassignment prass

            ON te.prassignmentid = prass.prid

         LEFT JOIN prtask prt

            ON prass.prtaskid = prt.prid

         LEFT JOIN srm_projects srmp

            ON prt.prprojectid = srmp.ID

         INNER JOIN PRJ_PROJECTS PR

            ON SRMP.ID = PR.PRID

         LEFT JOIN SRM_RESOURCES SPM

            ON PR.MANAGER_ID = SPM.USER_ID

   WHERE ts.prstatus < 5

   AND tp.prstart BETWEEN (SYSDATE - 65) AND SYSDATE

   and (@WHERE:PARAM:USER_DEF:INTEGER:manager_id@ IS NULL OR cmn.id=@WHERE:PARAM:USER_DEF:INTEGER:manager_id@)

   and (@WHERE:PARAM:USER_DEF:DATE:param_begindate@ IS NULL OR TP.PRSTART >=@WHERE:PARAM:USER_DEF:DATE:param_begindate@ - 6)

   AND (@WHERE:PARAM:USER_DEF:DATE:param_begindate@ IS NULL OR TP.PRFINISH <=(@WHERE:PARAM:USER_DEF:DATE:param_begindate@ +NVL(@WHERE:PARAM:USER_DEF:STRING:param_week@,'13')*7))

   and srmp.id in(5336230,5059738,5065150)

GROUP BY srmr.full_name,

         srmr.unique_name,

         srmr.ID,

         cmn.ID,

         tp.prstart,

         tp.prfinish,

         prodcal.philips_week,

         SRMP.ID,

         srmp.unique_name,

         srmp.NAME,

         SPM.FULL_NAME,

         SPM.USER_ID,

         prt.prname,

         prt.prid,

         ts.prstatus

HAVING SUM (te.practsum) / 3600 > 0)

group by proj_key,project_name,prstatus,timesheet_status,project_manager)temp

where @Filter@

So if you want to create a chart like in you image ; you need to return the status counts as distinct metrics in the query - but your query is not constructed like that.

i.e.. you have something like this returned ;

project / status / count / hours   (and you will get multiple rows per project)

what you need is

project / count OPEN / hours OPEN / count SUBMITTED / hours SUBMITTED / count APPROVED / hours APPROVED / ... and so on  (and you want one row per project)

Thanks David. That was a valuable info.

Can you help me out how can I find such columns in my query as I am not able to figure out how to do that?

You have all the right data/columns in your query ; you just need to "restructure" it like I say above.

( the thread NJ linked to is a similar problem ; a query needing to be restructured to return data in the right way to drive a portlet )

Hi Dave,

If possible can you please help me with an example as to how to arrange all timesheet statuses in a single row?

the thread NJ linked to is a similar problem ; a query needing to be restructured to return data in the right way to drive a portlet

Would be good if you can share the query. Also, take a look at the below -

Help with Pie Chart for a custom query for timesheets

https://communities.ca.com/message/7006964#7006964

NJ