Crystal Reports 2011 sql view query

Question

I'm working on a report for health benefit deductions in Crystal Reports 2011, grabbing fields from SQL views. The view I'm stuck on, is called emp_groups.group_code. Here's a screenshot of the fields in the view:

Screenshot.

What I want to do, is create a formula that grabs all the groups the employee is in from this view(they can be enrolled in 1 up to all of the groups. And then put it in my report. I'm a Crystal Syntax newbie and thought a select statement would work, but it only grabs the first group someone is enrolled in. (ie: a person may be enrolled in LUNLRN,PHYEX,&WGHTLOSS but only LUNLRN shows up for me after this select statement)

select {emp_groups.group_code} 
   Case "COACHEDU": 
      "Coach" 
   Case "HRA": 
      "HRA2012" 
   Case "LUNLRN": 
      "Lunch&Learn" 
   Case "PHYSICAL": 
      "Phyiscal" 
   Case "PHYEX": 
      "Exercise" 
   Case "WGHTLOSS": 
      "WeightLoss" 
   Default : 
      "";

Robert Niestroj Robert Niestroj · Accepted Answer · 2012-07-14T07:53:18

I would join the groups your employee belongs to the employee table and then group in the crystal report by employee id and show the employee details as well as the groups he belongs concatenated in the group footer

When you group by employee id then put in the details section a formula like this:

WhilePrintingRecords;
StringVar employeeGroups;

   employeeGroups:= employeeGroups & Trim({table.group_name}) & ','

in group footer:

WhilePrintingRecords;
StringVar employeeGroups;

employeeGroups

in group header

WhilePrintingRecords;

StringVar employeeGroups:= ""

Crystal Reports 2011 sql view query

1 Answers