Need to conditionally suppress records in a crosstab... 1

[awest75]

Hello, I am running Crystal 8.5 hitting a SQL Server database.

Client needs to see a crosstab of hours worked and paycodes associated to hours based on the number of shifts worked during a work week. They only want to see people who have worked an excessive number of shifts (based on the employee type) during a single work week.
I have the crosstab working fine, but the suppression is not.

I have two groups in the report:
Group 1 is based on the employee ID.
Group 2 is based on the "applydate" field in the database (the day to which the hours worked are being applied) and is grouped by week.

The crosstab is placed in the header for group 1 to have it generated by employee ID. I originally suppressed group 1 header based on the number of "applydate" records in conjunction with the employee type using an IF..THEN statement, however, if the client wants to query more than one week the IF...THEN will only consider the first week in deciding whether or not to suppress the record.

So I really want Crystal to look at each week within the parameter time frame and, if ANY week has an excessive number of shifts, show the record. I only want the record to be suppressed if NONE of the weeks have an excessive number of shifts.

I have begun a series of formulas trying to force Crystal to recognize the datetimevar range week1, week2, etc. with the idea of using a series of IF...THENs. What I really want is a FOR EACH WEEK loop, but I am not sure how to get Crystal to recognize each instance of a group.

Here is what I want:
For each week,
If {employeestatus} = HRLY8 and distinctcount({applydate}, {applydate}, "weekly") < 6 then suppress = true else suppress = false.

Any help or suggestions appreciated.

 

[lbass]

Use a record selection formula like:

{table.date} in {?daterange}
I'm assuming that in your main report you have a group on employee ID and on applydate, with a crosstab inserted in the employee ID header. In the crosstab, you are using applydate as the row, formatted to print on change of week, and the summary is distinctcount of apply date.

You could then go to report->selection formula->GROUP and enter:

{table.employeestatus} = "HRLY8" and
distinctcount({table.applydate},{table.applydate},"weekly")
>= 6

This will return only those employees that meet your criteria, and will display the crosstab with all weeks. However, only the weekly groups that meet the criteria will appear in the main report for Group #2. Not sure that's okay with you.

-LB

 

[awest75]

Wow, LB, thank you SO MUCH! You know I didn't even realize that group selection formula editor was there. I think it has solved my problem completely. No for loops, no counters, just a simple formula in the group selection criteria.
Beautiful. Now for testing.
THANK YOU!!!!
A~