Filtering a report based on data in columns (using the "or" function?) 1

[nolanma]

Below is an example of a report I have created. This is a manual crosstab report where I used formulas to create the columns. I will try to describe my delema as briefly as possible, but please let me know if you need more information in order to understand what I need to do.

Name Sat Sun Mon Tues Wed Thur Fri
5/12 5/13 5/14 5/15 5/16 5/17 5/18 TTL
John Smith 0 0 8 8 8 0 0 24
Jim Jones 0 0 0 8 8 0 0 16
Mike Scott 0 0 8 8 8 0 0 24
Jan Peters 0 0 8 0 8 0 0 16

The report shows timecard entries for a current week. Time should be entered daily. The report is grouped by employee and will be run daily to show any employee who did not enter time for a previous day or days in the week. I want to group the report to show those employees with 'missing' entries first. So, for example, assuming this report was run on Thurs 5/17, Jim Jones and Jan Peters should be listed first because they have 0 hours entered on 5/14 and 5/15 respectively. (note: Sat and Sun are normal days off and will be 0 for most people. At this point I am more concerned with the normal work days, Mon-Fri.)

Below is an example of a formula I've tried, to give you an idea of my thought pattern. The problem,however,is the formula is not on the drop down list when I try to create a group on it. I've tried a similar formula thinking I could suppress records where hours were >0,but had no success.

if Sum ({@SUNHRS}, {table.empl})=0 or
Sum ({@MONHRS}, {table.empl})=0 or
Sum ({@TUESHRS}, {table.empl})=0 or
Sum ({@WEDHRS}, {table.empl})=0 or
Sum ({@THURSHRS}, {table.empl})=0 or
Sum ({@FRIHRS}, {table.empl})=0 or
Sum ({@SATHRS}, {table.empl})=0 then "missing"

Any ideas,comments,suggestions are greatly appreciated!

 

[dgillz]

So why do you want to group on this formula? Please show your desired output, as well as what you currently get.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

I think you could handle this by inserting a sum on the {table.hrs} field at the employee group level. Then go to report->group sort and choose sum of {@wktotal}, ascending. This will place those with fewer hours at the top. If you need a fake group title, e.g., "Missing", this is also possible (not sure you really want that).

Another approach would be to use a SQL expression or command to return the summary directly--then you could use the result in a formula to group on.

-LB

 

[nolanma]

Thanks for the replies.

Below is the desired output. The difference from the above example is that Jim Jones and Jan Peters are listed first. Remember to assume this report was run on Thursday. The purpose of the report is to show employees who did not enter time on the previous day (or days). Jim Jones did not enter time on Monday and Jan Peters did not enter time on Tuesday. They should be listed first.

Name Sat Sun Mon Tues Wed Thur Fri
5/12 5/13 5/14 5/15 5/16 5/17 5/18 TTL
Jim Jones 0 0 0 8 8 0 0 16
Jan Peters 0 0 8 0 8 0 0 16
John Smith 0 0 8 8 8 0 0 24
Mike Scott 0 0 8 8 8 0 0 24

I need a way to pin point those employees with missing entries. I think a formula is needed to accomplish this, but need help coming up with the correct formula. If I could get the formula correct, I could then group on it so those entries would be listed at the top of the group. The report is already grouped by employee and the hours are summed at the employee group level. I cannot use the "sort-ascending" on the week total because it is possible that an employee work overtime on one day to have the correct total hours but still be missing a daily entry for another day.

I appreciate the replies so far, Thank you! If anyone has any other suggestion, it would be appreciated.

 

[lbass]

Using my suggesion would give you that result.

-LB