Select Records based on multiple conditions 1

[jherold1]

All,

I am having a problem creating the follow selection criteria... any help greatly appreciated. CR XI R2, oracle database, data is time tracking.

What I'm looking for is a graph that is filtered down only to resources that have >50 hours to date in support.

It's a stacked percentage graph

y-axis is percentage of workload
x-axis is date (months)
and the groups are work-type (project, admin, support, etc.)

I can't only filter down to where support>50, because i want to capture the project and admin work for support people.

Thanks in advance!

 

[Madawc]

If a report needs to do two incompatible things, the simplest fix is to put one of them in a subreport. Takes longer, but machine-time is getting cheaper all the time.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[jherold1]

thanks for replying!

maybe i didn't explain this well.

we have these fields

resc_nm (resource name)
to_dt_hrs (hours to date)
proj_nm (project name)

so in the database we could have

ted 10 support
ted 15 admin
ted 5 project
rita 4 admin
rita 10 project
bob 30 support

what i want is to filter on records where resc_nm has ANY support hours. in the above example, i want all records for ted and bob, no records for rita.

What the graph would display is a breakdown of support, admin, and project time for people who do support.

Unfortunately there is no field tagging support people, so this was the best way i could think to tag them.

Thanks in advance!

 

[Turkbear]

Hi,
This:

a breakdown of support, admin, and project time

is confusing since your example indicates you only want 'project' hours - you cannot graph what you excude ( 'admin' and support' ).

Can you clarify further?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jherold1]

No, I want all hours for all resources who have any support hours. That inclues admin and support hours.

So something like

If resouces has any support hours
then
select proj,admin, and support hours
else
ignore

Does that make more sense?
This is more about the data selection than the graph

 

[lbass]

First create a formula:

//{hassupp}:
if {table.proj_nm} = "support" then 1

Insert a group on {table.resc_nm}. Then try using conditional formulas like this in your graph:

//{@showvalue}:
if sum({@hassupp},{table.resc_nm}) > 0 then
{table.to_dt_hrs}

//{@onchangeof}:
if sum({@hassupp},{table.resc_nm}) > 0 then
{table.resc_nm}

You might have to also add a group selection formula (not sure without testing):

sum({@hassupp},{table.resc_nm}) > 0

-LB

 

[jherold1]

Thanks for your responses! For the future reference of others, this is a solution I found that returns what I was looking for. I will try your solution as well lbass, as the performance on my solution is fairly poor.


I put this query as the data source(oracle db):

Select x.*
from

X
Where Exists
(
Select y.RSRC_NM
From

Y
Where y.RSRC_NM = x.RSRC_NM
AND y.PROJ_GRP_NM='Support'
GROUP BY y.RSRC_NM
HAVING SUM(y.TO_DT_HRS)>100)

 

[lbass]

I forgot about the filter regarding volume. You would have replaced the 1 with the amount field in the first formula, and then tested for 50 instead of 0 in the other formulas.

-LB