Selection Criteria in a Group Summary

[socalvelo]

CR XI
Oracele db

I have a report that determines if an employee handled one or more calls during a period of one day. Basically this determines if they were at work as they always handle at least one call during a shift. A distinct count summary is grouped by employee ID. The summary is based on a date field. Grouping in the employee ID field works fine and I end up with a monthly summary of days worked for each employee. distinctcount({call_date},{employee_IDnumber})

...and here is the issue:

I have a graveyard shift that starts at 10PM and I don't want to count the calls they receive between 10PM and Midnight because this may increase their "days worked count". To accomplish this I can use a text based time field (for the call record) and a shift identification field to remove these records.

Here is my formula to remove records assigned to a graveyard shift employee where a call was received between 2200 and 2359. For our purposes here this seems to work OK to remove the criteria for those hours if they are assigned to a graveyard shift.

if left({Call_Time},2) <> ["22","23"]
or ({Shift_ID})<> "G" then totext({call_date})

When I create the (distinct count) summary with this formula, it is also counting the null values as one. Can someone assist on a better way to set up this summary so it doesn't count the null (or blank) value - only the date from my formula.

 

[MCuthill]

socalvelo,

One possible approach is to build a formula field similar to the following, then sum said formula instead of counting.

if left({Call_Time},2) <> ["22","23"]
or ({Shift_ID})<> "G"  then 1 else 0

As the "exceptions" are now 0, they will not impact the overall total.

Hope this helps.


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[socalvelo]

MCuthill - thanks for the reply. I need to make a distinct count of the date fields. Most days there are several calls for each employee. Each unique date needs a count of 1 to indicate that someone was working on a given day. A sum rather than a distinct count, would result in an overall total of every call.

 

[MCuthill]

Hmmm... thanks for the clarification, clearly I didn't read your original post quite meticulously enough!

You could create a formula field for the bottom of your Employee Group:

DistinctCount({Field},{Group}) - 1

to remove the one null count (as all nulls should be counted the same I would assume).

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

socalvelo,

another idea, depending on other criteria of the report...
"Each unique date needs a count of 1 to indicate that someone was working on a given day"

if you only need to show days where someone has worked, you could perhaps still base your Group Selection on the Sum of my previous formula to be greater than 0.

Sum({@Formula},{Group})>0

then, so long as at least one call exists within a date group, the group will display.

Just an idea that came to mind after my previous post.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[socalvelo]

This will also subtract 1 from employees not in the problem group (G shift with a call time starting with "22" or "23")

 

[MCuthill]

erm. *scratches head* quite the quadary.

Another idea:
@Null Counter:

If IsNull({@yourOriginalFormula}) then 1 else 0

Then you could place this in the employee footer.

If Sum({@NullCounter},{Group})>0 THEN
DistinctCount({Field},{Group}) - 1 ELSE
DistinctCount({Field},{Group})

Basically, if a null is found, subtract one from the distinct count for the group, if not, return the distinct count.

My apologies for all the misdirection and hope this idea proves more useful than the previous posts.


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[socalvelo]

Mike
Using the null counter and the second formula seems to be giving me the correct results. Thank you very much for your assistance.

 

[lbass]

A simpler method is to create a formula {@null} by opening a new formula and saving it without entering anything. Then change your initial formula to:

if left({Call_Time},2) <> ["22","23"]
or ({Shift_ID})<> "G" then
{call_date} else
date({@null}) //assuming {call_date} is a date

If you want to use totext({call_date}), remove the date().

A distinctcount of this formula will be accurate, since nulls don't get counted.

-LB

 

[socalvelo]

LB,
Thank you. It works very well.