Question Crystal Reports: Group BY and Order by issue

[robtyketto]

Greetings,

My crystal report has a group name field (boolean) and and a sort selection field (membership id).

This generates the following sql (via database -> show sql)

 SELECT `Members`.`cMembershipId`, `Members`.`cTitle`, `Members`.`CSurname`, `Members`.`CforeNames`, `Members`.`cWRorBranch`, `Members`.`cDateLastVisited`, `Members`.`cHouseNum`, `Members`.`cAddressLine1`, `Members`.`cAddressLine2`, `Members`.`cTown`, `Members`.`cCity`, `Members`.`cCounty`, `Members`.`cPostcode`, `Members`.`cTelephone`, `Members`.`cMobile`, `Members`.`cDeceased`
 FROM   `Members` `Members`
 ORDER BY `Members`.`cDeceased`, `Members`.`cMembershipId`

Somewhat suprised as their is no GROUP BY clause within the SQL.

However the results are as expected with the report being grouped into TWO halves since the group name field is a boolean.

I previously had my application running the report passing in a sort field value into my report, which at the time had no group name field.

Programming the sort code via vb.net code worked without issues.

I then added the group name field into the crystal report.
Then when running the report the group name field ALWAYS was set to the sort field rather than the deceased value as set in the .rpt file.

Even though the code did not access the group field.

'Assign field name to field defination
FieldDef = cryRpt.Database.Tables.Item(0).Fields.Item("cMembershipId")
cryRpt.DataDefinition.SortFields.Item(0).Field = FieldDef

I then debugged further by programmatically setting the group name field:-

Dim grpline As FieldDefinition
grpline = cryRpt.Database.Tables(0).Fields("cDeceased")
cryRpt.DataDefinition.Groups.Item(0).ConditionField = grpline

'Assign field name to field defination
FieldDef = cryRpt.Database.Tables.Item(0).Fields.Item("cMembershipId")
cryRpt.DataDefinition.SortFields.Item(0).Field = FieldDef

also experimented with the Database option -> peform grouping on server.

What is the way forward to ensure the report is grouped by the cDeceased field and then sorted by a choice of other fields base on the value of a radio button (this code is in place, an example of setting the sortfield is included in this post).

Any advice and help would be really appreaciated.

Thanks
Rob