Custom Groups

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.RS also has a concept of groups, and the construction of groups is very flexible. Groups are usedion DataRegion, such as Table, Matrix and Lists. Groups can be be grouped, filtered, sorted using multiple expressions. These expression can be just a field value, or they can be more complex.

In your case, you could create a group that uses an expression to return 1 if the value starts with A-G, return 2 if the value starts with H-P, and so on. The expression would look something like this, it assumes only upper case ASCII characters.

=Switch("ABCDEFGH".Contains(Fields!Name.Value.Substring(0,1)), 1, "IJKLMNOP".Contains(Fields!Name.Value.Substring(0,1)), 2, "QRSTUVWXYZ".Contains(Fields!Name.Value.Substring(0,1)), 3)

or

(use 64 since Choose uses the 1-based index into the array)

=Choose(Convert.ToInt32(Fields!Name.Value.Chars(0)) - 64, {1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3})

If you want something a little cleaner, then I would use a custom function that takes the first character as an argument and determines the appropriate value using regular control statements.|||

Thanks Ian!

Instead of editing the report, I decided to just add a field to my SQL statement to group on. It looks something like this:

select case
when left(lname,1) between 'a' and 'h' then 'A-H'
when left(lname,1) between 'i' and 'p' then 'I-P'
when left(lname,1) between 'q' and 'z' then 'Q-Z'
end as NameType from employee

I then created a group on the NameType field.

This worked great and my group headers were named properly as well.

|||I am trying something similar, but for some reason it does not like the syntax near AS. I see that it creates field called "NameType"...but does 'Employee' refer to the table the lname is coming from?|||Yes, the 'AS NameType' clause specifies the name for the result of the case statement, and 'lname' is a field in the 'Employee' table.

What is your query? Can you post it? I'm happy to take a look at it and see if there is something I can spot that would result in the syntax error.

Ian