This formula is not to be used in the select expert (select statement). It is to be used as a new formula field. From there you group on the formula field rather then the original customer id field.
Basically what we are trying to do here is create a piece of data that meets your needs from the already existing data. You indicated that you wanted all of the records to still in the report and therefore you cannoty use the select expert which only ,manages what items are included or excluded, not how they are grouped or sorted.
In the Field explorer, right click on the Formula Fields and select NEW.
Give it a name like "Grouping".
Put the formula from above in it, save and close it.
Place this formula field on your details row next to the original customerid field and preview the report.
It should now have converted all the "other" records into the verbage "Everybody Else" and left the "1004", "1366", "1928", "2045", "2150" ones as is.
Now if you group on this new formula field it sticks all the like items together giving you 6 groupings ... "1004", "1366", "1928", "2045", "2150", "Everybody Else" which is what you wanted.
Note that you can change the wording of "Everybody Else" to something more professional by altering your formula (see red ink below). Your grouping will automatically change when the formula changes...
if {customer.id} in ["1004", "1366", "1928", "2045", "2150"] then {customer.id} else "Other Customers"
You can also remove the formula field from the details row. You don't need it there to group on it. I just put it in there so you can see what it is doing.
Edited by DBlank - 18 Jun 2009 at 10:37am
IP Logged
