Clarity

Hi! We have a report here that shows a pie chart with percentages of your team (or department or whatever level you filter it at) for timesheets submitted. It goes out the morning we do posting. I was requested to make it a portlet. I got a large portion of code for the query, which does work, off of a post on this forum. I pulled that into a grid portlet to try to verify the data. It does show each person and whether or not they have populated, submitted, etc their timesheet for the previous week, and it is filterable by OBS. I just can't make the pie chart work. Most of my tries have ended up with a separate slice for every single person, regardless of the status of their timesheet. I am attaching the code for the query - any insight on what I am doing wrong would be greatly appreciated!

Jennifer

Hi,

Your problem is that your query is "inside out" (that doesn't really make much sense does it! :unsure:)

So your query is returning data of the format;

Dave ; Submitted
John ; Posted
Eric ; Submitted
Eve ; Populated
Kim ; Populated

i.e. a row for every resource along with the timesheet status and thats why you get a pie chart segment for each resource, the pie chart will draw a segment for every ROW returned by your query, with the size of the segment dictated by the "metric" in the row


What the query needs to look like for a pie chart is a SINGLE ROW for each timesheet status, along with a count of the resources at that status (the metric), i.e.

Submitted ; 2
Posted ; 1
Populated 2

So your query really needs to be doing a COUNT / GROUP BY of some sort in there - you have all the right data, you just need to aggregate it in the query that you present to the portlet.

Hope that helps!

So would the status of the timesheet (prtimesheet.prstatus) be the DIM key then? If I do that, it errors, as unpopulated comes up as null in PRSTATUS. I understand what you are saying, it makes a lot of sense in the forehead slapping sort of way. I'm just not sure how to get the dim key to work.

Thank you!!!

Try using ISNULL function for the timesheet status field.

IsNull(prstatus, 99)

This would make sure that the Timesheet status would always have a value. Try and let us know if it works.

Thanks
~Dev

Actually the "Dimension Key" almost doesn't matter (as long as it is unique) - I have pie charts where I just use a "ROWNUM" unique row number reference as my "Dimension Key".

In the pie-portlet based on the query you will get the option in the "Source Data" of choosing a 'Metric' - and you can pick any of your numeric "Dimension Properties" as that metric (i.e. the slice size) - in your case it would just be the 'count of' column.

You then map (in the portlet 'Options') the 'Datapoint labels' and 'Label attribute' to have the "Status" displayed on the pie chart.

--

The comment above about mapping NULL values to real (dummy) numbers is also a useful trick, but if you are doing a simple COUNT/GROUP BY you will not normally get any NULL values - (however your base query is a bit complex for me to follow by eye so you might be getting them I guess).

Well I returned a value for the nulls (which are unpopulated) using a case statement. So I did the cases for all the options for timesheet status except unpopulated, and then did an else "unpopulated" so that it would put those in.

So I tried keeping the same query and simply adding a metric, and using that but it still seems to go off of the resources for the rows rather than the status. So then I tried putting the status as the DIM key, and of course got the null error.

Then I tried what DevK said above, with the IsNull. I put this: @select:dim:user_def:implied:RES:IsNull(ts.prstatus, 99):status@,
and that seems to work but now I am getting a duplicate dimensional data error. I'm attaching a copy of the code I am working with right now, again. It's had adjustments since the first one.

Any other thoughts people have on this is appreciated!! Thanks for the help you have given so far, I am definitely closer! :)

Your query is still returning a row per resource I think (I can tell this because that is what you are using in the GROUP BY section).

--

The specific error message you are getting is because you are using the status as the "dim" (which needs to be unique), that combined with the fact that the query is generating a row per resource means that the status is not likely to be unique (hence the duplicate dimension message).

You need to restructure the query to give you a single row per status and a count of the resources (like I posted above!).

OK, what I mean is something like this....

SELECT 
       @select:dim:user_def:implied:RES:ROWNUM:uq_id@,
       @select:dim_prop:user_def:implied:RES:NVL(status,99):status@,
       @select:dim_prop:user_def:implied:RES:(case
              when status = 0 then 'Populated'
              when status = 1 then 'Submitted'
              when status = 2 then 'Returned'
              when status = 3 then 'Approved'
              when status = 4 then 'Posted'
              when status = 5 then 'Adjusted'
              when status is null then 'Unpopulated'
              else 'Unpopulated'
              end):TS_STATUS_DISPLAY@,
       @select:dim_prop:user_def:implied:RES:statcount:statcount@
FROM
(
SELECT status 
     , count(*) statcount
FROM
(
SELECT           nvl(ts.prstatus,99) status
           ,     case
                 when TS.PRSTATUS= 0 then 'Populated'
                 when TS.PRSTATUS= 1 then 'Submitted'
                 when TS.PRSTATUS= 2 then 'Returned'
                 when TS.PRSTATUS= 3 then 'Approved'
                 when TS.PRSTATUS= 4 then 'Posted'
                 when TS.PRSTATUS= 5 then 'Adjusted'
                 when TS.PRSTATUS is null then 'Unpopulated'
                 else 'Unpopulated'
                 end AS TS_STATUS_DISPLAY
FROM PRTIMEPERIOD TP
JOIN PRJ_RESOURCES PR ON 1=1
JOIN SRM_RESOURCES SR on ( SR.ID = PR.PRID )
LEFT JOIN PRTIMESHEET TS ON (TP.PRID=TS.PRTIMEPERIODID AND SR.ID = TS.PRRESOURCEID)
JOIN PRJ_OBS_ASSOCIATIONS poa on ( SR.ID=poa.RECORD_ID )
JOIN PRJ_OBS_UNITS pou on ( poa.unit_id= pou.id AND pou.type_id=5000002 )
WHERE PR.PRTRACKMODE=2 
  AND PR.prisopen = 1
  AND TP.PRISOPEN=1 
  AND SR.is_active=1
  AND SR.unique_name <> 'OUTR'
  AND SR.last_name <> 'PIC'
          AND (@WHERE:PARAM:USER_DEF:DATE:P_DATE@ between tp.prstart and tp.prfinish)
          AND (pou.id = @WHERE:PARAM:USER_DEF:INTEGER:P_OBS_ID@)
)
GROUP BY status
)
--
WHERE @FILTER@
HAVING @HAVING_FILTER@

I took your query and rewrote it.

Points to note;

This was on ORACLE - you will need to change the NVL() to IsNull()

I have "parameterised" the date for which you are analysing data and the OBS for which you are analysing.
This will make a param_p_date and a param_p_obs appear in your query attributes. You need to associate a lookup that provides the OBS unit_id with the param_p_obs attribute.

By parameterising these fields it makes the portlet much more flexible - you can default the param_p_date to "today" in the portlet.

When creating the portlet, select the "statcount" as the metric and in the "options" select the "ts_status_display" as the label.
Add the 2 "param" fields to the filter portlet.

--

There is a LOT more you can do with this sort of information - you could add "drill-downs" to the segments in the pie-chart to list the specific resources who have not filled in timesheets etc.... but thats a different thread I think.

Hope this helps?

It does help a lot, you are awesome!

I actually got this working last night and this morning, and then I refreshed the page to see your code. I had to take the OBS out to make mine work and was trying to add it back in. So yours is more along the lines of what I was looking to accomplish. THANK YOU!!!

The parameters was something else I was looking into, I have another query associated with a grid portlet that is intended to go with this one. That one does pull the data with each row being a resource, and show who has and has not submitted their timesheets. I was going to pass the filtered OBS unit from there to this pie graph. Thank you again!!

Good stuff!

If you are calling one portlet from another, you should read the threads on "drill-down" portlets on this board.

You'll need to understand how to use the constructs like @WHERE:PARAM:XML:STRING:/data/drill_from/@value@ (where "drill_from" is my invention here) in order to pass "context" (parameters) from one portlet to another.

(that is if you are using the second-portlet to show details based on where you click (drill-down) in the first portlet; if all you want to do is "share" filter criteria between related portlets you can do this with teh rather neat "page filters" aka "filter portlets" (look that up in the docs)).

Thank you Dave for all of your input!

Chris

Hi,

I am wondering if it is possible to group by multiple columns in a pie chart portlet. I have a pie graph for no of projects by status. We need to also include no of projects by status per pm. When I added pm in group by the pie chart looked way different :-). I have also tried using param for pm but this makes pm required field.

Please advice.

Thank you,
Rajani.