Tuesday, December 12

Making More Extensive Changes to The Selection Formula

Google+ Pinterest LinkedIn Tumblr +

For this second example, imagine that you need to develop a human resources report containing employees from the Training department of the Charlotte office and the Marketing department of the Cincinnati office. These two teams will receive a bonus for some excellent work they did together develop­ing a new product.

It is not possible to create the proper selection criteria using just the Select Expert. The best it could do would be to print all employees in either the Training or Marketing departments in either the Charlotte or Cincinnati offices. But, you want marketing from Cincinnati only, and Training from Charlotte only. You must edit the selection formula to change one-. “And’’ to an “or’’, and also move some of the statements around to accommodate the re­quirements. Click Show Formula in the Select Expert to modify the selection formula so that you get only the information that you want on your report.

Basing Groups on a Selection Formula.

It might occur to you that using the Select Expert to filter the groups on your report, as well as the records, would be useful. If you want this report to include only customers that have ordered more than $20,000 worth of products, you would set up a group selection formula to accomplish this. Note that since group selection formulas must involve a summary field, “Sorting, Grouping, and Totaling Data on Your Report”), you’ll probably want to work some on the main body of your report first, and then return to the Select Expert to add the group selection formula.

The group selection formula processes later during the processing of your report. SCR first applies your record selection criteria, groups your records, calculates the sum of order amount, and then uses the result o/f the sum formula in the group selection formula. “Advanced Formulas,” explains the order in which functions are processed in detail. Here we want to only display groups (Customer Name) whose Order Amount exceeds$20,000. Take note, though, that a group selection formula might cause your report to look incorrect in some aspects. The Group Tree area might show groups that have been filtered out with the group formula. Also, if you have a grand total, it will be incorrect because it will have taken all sums into account, not only those that met the group selection criteria. This totaling problem can be overcome with running totals instead of a grand total.

To use a selection formula for a group, click Report, Edit Selection Formula, and Group or open the Select Expert, click Show Formula, and then select the Group Formula option. Click the Formula Editor button and the Formula Editor becomes available to write a selection formula for the group.


About Author

Leave A Reply