select issue

[willimike]

I'm working on a report that gives employee total hours worked over a date range (entered as a report parameter)
I want to limit the report to only employees who's totals exceed a number set in another report parameter. My question is how to run the report to get the totals, then select the employees that are displayed, based on the totals. I can get the totals, but am not able to limit the report to those exceeding the set number.

 

[Madawc]

I assume you are grouping time records by employee, and then using a summary total for the group.

Having done this, you could suppress groups that have totals below a parameter value. You can find this in Report > Selection Formulas > Group for Crystal 11.

Another method is 'Top N', but group suppression should be easier.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.



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

 

[willimike]

Working with CR 8.5

Thank you for looking at this.

Yes, I am grouping by employee, and summarizing.
Suppression, unless I'm doing something wrong, works to a point. The problem I have with suppressing the group is that it also supresses the details for an employee up to the point when total hours exceeds the parameter. So on a week's worth of records, it will supress all but the last 2 or 3. I need to show all of the detail records for the employee that has exceeded the parameter hours.

 

[lbass]

Use group selection instead of suppression (although suppression would work too if you used the correct formula, which would be the reverse of the formula below). Go to report->selection formula->GROUP and enter:

sum({table.hours},{table.employee}) > {?Hours}

-LB

 

[willimike]

Thanks for the help.
I love the concept of doing this in the selection code, but no mater what I try, it won't limit my records (except for the date range). Here's my code in the group selection formula

{empldetl.TRAN_DATE}>={?Start_Date} and
{empldetl.TRAN_DATE}<={?End_Date} and
sum({empldetl.REG_HOURS},{empldetl.CODE})>{?Max_Hours}

 

[lbass]

This should be in your record selection formula area:

{empldetl.TRAN_DATE}>={?Start_Date} and
{empldetl.TRAN_DATE}<={?End_Date}

Not in the group selection area.

-LB

 

[willimike]

I actually had the formula in both record and group. If I remove it from group, I get all the records (takes a 66 page report to a 1400 page report). Strange, but the group formula does appear to work, but on too many records.

 

[lbass]

Please show your current record selection formula and your current group selection formula.

-LB

 

[willimike]

Record Selection Formula:

{empldetl.TRAN_DATE}>={?Start_Date} and
{empldetl.TRAN_DATE}<={?End_Date}

Group Selection Formula:

{empldetl.TRAN_DATE}>={?Start_Date} and {empldetl.TRAN_DATE}<={?End_Date}
and sum({@Day_Total},{empldetl.CODE})>{?Max_Hours}

Report is grouped on [empldetl.code} with one group header, one detail line, and one group footer.
With the current formulas, I get the all employee records filtered to the Start/End dates, but not filtered to ?Max_Hours. I've tried several different fields to sum in the group selection formula, and get no indication that it is filtering at all beyond Tran_Date.

 

[lbass]

The group selection formula should just be:

sum({@Day_Total},{empldetl.CODE})>{?Max_Hours}

I think you need to take a look at what the formula:

sum({@Day_Total},{empldetl.CODE})

...returns if placed in a group section for {empldetl.CODE}. What is the content of {@Day_Total}?

-LB

 

[lbass]

PS. An explanatory note:

Record selection defines the base set of data that is returned to the report. Group selection selects data from the base set returned by the record selection formula that also meets the group selection criteria.

-LB

 

[willimike]

With that said, if I ignore the Max_hours issue for now, what might be causing the report to return all records if I have the Record Selection formula set to Start/End dates, and the Group Selection formula blank?

And thank you for your continued help on this - I really appreciate your time!

 

[lbass]

I don't have enough information to tell you that. Please go to database->show SQL query and copy that into the thread.

-LB

 

[willimike]

My database is not SQL. I'm pulling from a single table in a DataFlex application.

 

[lbass]

So what do you see if you go to database->show SQL query? Anything?

-LB

 

[willimike]

the option is greyed out

 

[lbass]

I'm not sure, but that suggests that you may not be able to apply parameters within Crystal--you might have to do this within the original application.

-LB

 

[willimike]

You may be right.

Thank you again for taking time to help.