How to Get Fields Associated With a Group Maximum: e.g. Employees with Maximum Sales Per Month

[JWiseman]

One method to flag a field when a value in a group is a maximum is to create a print-time formula with a variable that stores that field. For example, in a report grouped by month you want to flag which employee has sold the maximum order for that month. You would create a flag formula like:

whileprintingrecords;

if {Orders.Order Amount} = Maximum ({Orders.Order Amount}, {Orders.Order Date}, "monthly")

then stringvar employee:= {Employee.Last Name} + ',' + {Employee.First Name}

or

if {Orders.Order Amount} = Maximum ({Orders.Order Amount}, {Orders.Order Date}, "monthly")

then global stringvar employee:= {Employee.Last Name} + ',' + {Employee.First Name}

The drawbacks of using this method is that the flagged values must be displayed at the group footer level and those print-time formulas in general cannot be used in charts and cross-tabs.

This blog post shows how to get around the limitations of the above method by creating a formula that appends all relevant details info and creating a maximum off of that new formula. I have attached a sample report that illustrates this technique. Please extract the contents of the attachment and change the .txt extension to .rpt.

In keeping with our maximum sales by employee per month example, we would create a new formula (I named the formula OrderInfo in this example) with syntax like the following:

totext({Orders.Order Amount},'000000') + '|' + totext({Orders.Order Date},"yyyy/MM/dd") + '|' + {@EmployeeName}

Note that the first field in the formula is the field that we want to show the maximum for, which is the Orders Amount. I've used the ToText() function with an option to pad the results so that the results are sorted properly. This is because we are producing a text type output. The EmployeeName formula above is {Employee.Last Name} + ',' + {Employee.First Name}. The formula will produce details level results like this:

Now if we right click on the OrderInfo formula and choose Insert > Summary and choose Maximum as the Type, we can have this maximum and all of the concatenated information available to parse out and display in the Group Header and available for use in charts and in cross-tabs.

If we look at the information in the context of showing the monthly group, the maximum of the formula is $010886|2004/01/15|Leverling,Janet  for January 2004. The maximum summary is colour-coded yellow below.

We can now create a set of formulas to parse this information out. For example, to get the employee name for the maximum monthly orders we use this syntax:

if ubound(split(Maximum ({@OrderInfo}, {Orders.Order Date}, "monthly"),'|')) >= 3

then split(Maximum ({@OrderInfo}, {Orders.Order Date}, "monthly"),'|')[3]

This employee name formula is colour-coded green above and is also available to use in cross-tabs and charts. For example, we can create a pie chart that counts the number of times that an employee has had the maximum sales amount, by month. To see what formulas were used in the charts and cross-tabs shown in this blog, please see the attached report.