R – How to group a crystal report based on multiple boolean fields

crystal-reportsgrouping

My data table looks something like this (first row = column names):

Name; Number; Group 1; Group 2; Group 3;

Leslie; 555-555-5555; Y; N; Y;
John; 555-555-1234; N; Y; N;
Mary; 555-555-2222; Y; Y; N;
James; 555-555-3333; N; N; Y;
Sue; 555-555-4444; Y; Y; Y;

I want my report to look like this:

Group Name 1

  Leslie; 555-555-5555
  Mary; 555-555-2222
  Sue; 555-555-4444

Group Name 2

  John; 555-555-1234
  Mary; 555-555-2222
  Sue;  555-555-4444

Group Name 3

  Leslie; 555-555-5555
  James;  555-555-3333
  Sue;  555-555-4444

I feel like I must be overthinking this. Can someone please advise me of the best way to group my report based on multiple boolean fields (and allow the report to repeat details as necessary)?

Thanks in advance,

Leslie Waters

Best Answer

You either have to change your record source to group them the way you want.

Example:

SELECT 'Group 1' as GroupName, Name, Number
FROM Table
WHERE Group1 = 'Y'

UNION

SELECT 'Group 2' as GroupName, Name, Number
FROM Table
WHERE Group2 = 'Y'

UNION

SELECT 'Group 3' as GroupName, Name, Number
FROM Table
WHERE Group3 = 'Y'

OR

You could create a sub report for each group - each sub-report would select the same records except filter them to show only the particular group

Related Topic