Pie Chart Question

[crystal123456]

I am looking to make a pie chart that shows workload percentage by task, and if all the available time is not being allocated to these 4 specific tasks, I want the remaining portion of my pie chart to display an "unused time" percentage.

I currently have my pie chart breaking down each task but I am having trouble adding in my section of unused time.

I currently have my pie chart working like this.

I first created an IF formula that looks at what type of task is being performed in the record and it applies the proper time formula.

I am then using this to graph the pie chart;

On change of: Task performed

Show Value: IF formula I just described

This is where I would like to somehow add another sliced for the unused time if the total time worked is less that the total time available.

Any ideas?

Let me know if what I have described is not clear and I will try to clear anything up

 

[lbass]

Please show the content of your "if" formula.

-LB

 

[crystal123456]

My "on change of" formula is: (Ill call it "OCO" for later:

If {TASK}="A" then "A" else if
{TASK}="B" then "B" else if
{TASK}="C" then "C" else if
{TASK}="D" then "D"
Else "Misc"

My show value formula is:

If {OCO}="A" then {TASK A Formula} else if
{OCO}="B" then {TASK B Formula}else if
{OCO}="C" then {TASK C Formula}else if
{OCO}="D" then {TASK D Formula}
else {MISC Formula}


I believe my problem is that there are no records that are labeled "Misc" thus nothing is showing up for this. I'm not sure if I am on the right track with my above formulas, but I am just needing to add this "MISC" section to my pie chart and that is what is giving so much trouble.

I appreciate all the help.

 

[lbass]

So is "Misc" the difference between some constant amount of time available and the sum of the other values? What is the constant?

-LB

 

[crystal123456]

The constant is the Total available workdays between two user specified dates. To calculate "Misc" I do what you said above and subtract this from the sum of the other values.

 

[lbass]

Okay. I think you have to use a command for this. Here is an example using the Xtreme database:

Select
(
select sum(A.`Ship Date`- A.`Order Date`)
from Orders A where
A.`Ship Via` = Orders.`Ship Via`
) as Amt, Orders.`Ship Via`
from Orders
union all
select
8000-(
select sum(A.`Ship Date`- A.`Order Date`)
from Orders A
), 'Misc'
from Orders

In this example 8000 is the total number of available days. You would then use the {Command.Ship Via} field for the "on change of" field and {command.Amt} as the summary field--but be sure to use a maximum on this, not a sum, since the returned value will be the same for all detail level records.

If this is part of another report, I would do this in a subreport so that the command is the sole datasource for the chart.

-LB

 

[crystal123456]

Thank you lbass for your help on this.

I'm a bit confused from your last post. What do I need to sub in for the 'ship date', 'order date'?

I'm not sure if I am completely understanding the command that you have written.

I don't think I'm seeing how this incorporates all of my different tasks?

 

[lbass]

You said that you were summarizing durations in days, so the dates are showing the date differences from beginning to the end of the task. I don't know what fields you are working with.

Why don't you show your actual formulas for the show value--including for any nested formulas. I'm unclear on why you are showing separate formulas for each task in the show value formula.

It might also help to see your SQL query (database->show SQL query).

My suggestion allows you to group on data that doesn't actually exist ("misc") for the pie chart.

-LB

 

[crystal123456]

Currently I have it set up like this exactly:

On Change of (named @chartformula):
If {@Change Type Trim}="NPR" then "NPR" else if
{@Change Type Trim}="CPM" then "CPM" else if
{@Change Type Trim}="NPM" then "NPM" else if
{@Change Type Trim}="CPR" then "CPR"
Else "Misc"

{@Change Type Trim}=left({PRODUCT_CHANGE_REQ.CHANGE_TYPE},3)
I did this because these records are customer specific and I only wanted the see the first 3 letters of the request type (NPM, CPM, etc...)

Show Values (named @chartif):
If {@Chart Formula}="NPR" then {@Days NPR} else if
{@Chart Formula}="CPM" then {@Days CPM}else if
{@Chart Formula}="NPM" then {@Days NPM}else if
{@Chart Formula}="CPR" then {@Days CPR}
else {@Misc Days}


(@Days NPM)= ({@Sum NPM}*6)/24 and (@SUM NPM) is total number of "NPM" records in the timeframe. Each "Days" formula is set up just like this.


(@Misc Days) = {@Total Days}-{@Total Days Worked}
{@Total Days}=Total days in the time period
{@Total Days Worked}={@Days CPM}+{@Days CPR}+{@Days NPM}+{@Days NPR}


My SQL query is:

SELECT "PRODUCT_CHANGE_REQ"."REQUEST_ID", "PRODUCT_CHANGE_REQ"."REQUEST_DATE", "PRODUCT_CHANGE_REQ"."CHANGE_TYPE"
FROM "PHCVQ"."dbo"."PRODUCT_CHANGE_REQ" "PRODUCT_CHANGE_REQ"
WHERE ("PRODUCT_CHANGE_REQ"."REQUEST_DATE">={ts '2010-11-01 00:00:00'} AND "PRODUCT_CHANGE_REQ"."REQUEST_DATE"<{ts '2010-11-30 00:00:01'})

Let me know if this info helps you out in helping and again I really do appreciate your time on this.

I also found another pie chart question where you had given a solution that I'm wondering could be relevant here?

http://www.tek-tips.com/viewthread.cfm?qid=1186403&page=340

 

[lbass]

Okay, the method in my previous thread also might work, but you would then need to set up the pie chart for all records (not on change of), and add one summary for each option: {@DaysNPR}, {@DaysCPM}, etc. (Ignore your other formulas). Then set up a formula like this and add it as your last formula:

//{@Misc}:
200 - sum({@TotalDaysWorked})

...where 200 is the total days possible at the grand total level (substitute your number or calculation). Be sure to use a maximum on this formula (since it is already a summary), while for the task formulas use a sum (assuming those formulas are written like this):

if left({PRODUCT_CHANGE_REQ.CHANGE_TYPE},3) = "NPR" then
datediff("d",{table.taskstartdate},{table.taskenddate})

Choose the option to show labels, but you would have to manually edit the labels.

-LB

 

[crystal123456]

lbass,

I have tried doing what you stated above but when I add even just a few of my @DaysNPM and @DaysCPM formulas under a pie chart that is set "For each record", I end up just getting and blank pie chart with "0" as my value shaded all in blue.

Here are exactly how my formulas calculate as maybe this will help you understand my problem:

(@countNPM)=If {@Change Type Trim}="NPM" then 1
This is counting each NPM record.

(@sumNPM)=Sum ({@Count NPM})
This is giving the total number of NPM records

(@daysNPM)=({@Sum NPM}*6)/24
This is applying my time formula for the NPM records, and converting into day units. So basically the number that(@daysNPM)returns in the total number of days that were spent on NPM records.

Each different task is set up like this (NPM,NPR, etc...)

When I add these different formulas under the show values of my pie chart, I get nothing.

I also noticed that I have no choice to pick what type of summary is being performed in the pie chart options, the button is grayed out. The (@daysNPM) formula defaults into being "Sum of @DaysNPM" in the show values section.

 

[crystal123456]

^ In regards to my post above I meant to say I was using a "For all records" type pie chart like you recommended.

Since posting I have also created formulas to calculate the time spent on each task on a per line basis, so this way I can effectively use the sum function under the pie chart options. This did open up the previously grayed out "Set summary operation" button.

However when I add two different formulas, all I get are two seperate pie charts with each one completely filled in blue.

The actually numbers they are showing are the correct values, they just are not being shown together in the same pie chart.

 

[lbass]

All of these nested formulas are very confusing to track back to their original values--and I usually avoid them for that reason.

That aside, I can't quite see how you are getting those results. If you are starting with the desired summary already in the formula as in your second to last post, I think you could just add all formulas and use maximums as the summaries (for all records).

-LB