Formula Returning Wrong Value for Group Name

[nlaliberte]

I'm using Crystal XI on an OLE DB connection.

This is the strangest bug I've ever encountered in a Crystal Report. Here is the situation: Group 2 has 'Use a Formula as a Group Name' selected. In that formula it states;

IF {proc.group2_type_id} = 70
THEN
IF {proc.group2_value} = -1
THEN " "
ELSE {proc.group2_display}

This seems to be working correctly, however the GroupName field is returning bad values.

The first set of groupings is correct. Group2_type_id = 70, Group2_value = 2 and Group2_display = 'Linked'. As expected, 'Linked' is appearing in the GroupName field.

For all remaining groups, Group2_type_id = 70, Group2_value = -1 and Group2_display = 'AA-Unknown', so " " should be the GroupName.

In the second set of groups, the GroupName for group 2 is appearing as " " in the header, but is displaying 'Linked' in the Detail and Footer section. This makes no sense to me as there is no place for Crystal to get 'Linked' in the dataset for those records and it is not hardcoded anywhere in the report file.

This behavior continues for each set of groupings on that page, however when a new starts, the Detail and Footer sections correctly display as " ".

I'll try to illustrate what it looks like below. Note that all I'm putting next to the sections is the GroupName field.


Page 1:

GH1 Company 1
GH2 Linked
D Linked
D Linked
D Linked
GF2 Linked Totals
GF1 Company 1 Totals

GH 1 Company 2
GH2
D Linked
D Linked
D Linked
GF2 Linked Totals
GF1 Company 2 Totals

GH1 Company 3
GH2
D Linked
D Linked
PAGE 2
D
GF2
GF1 Company 3 Totals:

GH1 Company 4
GH2
D
D
D
GF2
GF1 Company 4 Totals:


And this is what it should look like...

Page 1:

GH1 Company 1
GH2
D
D
D
GF2
GF1 Company 1 Totals

GH 1 Company 2
GH2
D
D
D
GF2
GF1 Company 2 Totals

GH1 Company 3
GH2
D
D
PAGE 2
D
GF2
GF1 Company 3 Totals:

GH1 Company 4
GH2
D
D
D
GF2
GF1 Company 4 Totals:

Does anyone have any ideas? I almost want to say this is an issue within Crystal...

 

[CoSpringsGuy]

I notice that the sample you sent the only time you get GF2 "Linked Totals" is if the last detail record is "Linked". I think thats a hint to the issue but I cant put my finger on it. What is group 2 grouped on.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

if you use Use a Formula as a Group Name option you shouldn't refer directly to database field names, other than if you are grouping on that particular field. Doing so will cause undesirable results as you are getting. Try changing your formula to refer directly to the field or formula you used to group on.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nlaliberte]

The fields that the formula is referencing are all on the group level, and one of them is the field that I am grouping on. If I put the formula in a seperate formula object ('test') than it returns the desired result on a per record basis.

Also, the reason the only time GF2 says 'Linked' is when the last detail before it says 'Linked' is because the problem seems to correct itself at the start of a new page. You'll notice in the example I outlined that once you pass 'Page 2' the problem no longer exists.

 

[CoSpringsGuy]

Im sure I am still not understanding something but how can {proc.group2_type_id} or {proc.group2_value} or {proc.group2_display} be a group level reference? The syntax indicates 3 separate database fields.

I still dont know what field G2 is grouped on but it sounds like you verified that. You also said you placed that formula in the details section and the exact same return existed on each record in the detail section? If that is the case, then I have exhausted my suspicions on your issue. Hopefully someone else will read through this that has some insight. Good Luck

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nlaliberte]

I say they are all on the group level because Group 2 is being grouped on the {proc.group2_display} field. However, this field is governed within the procedure by the combination of {proc.group2_type} and {proc.group2_value}.

So essentially the three fields combined are what make up group2.

 

[Turkbear]

Hi,
Try:

IF ({proc.group2_type_id} = 70 AND {proc.group2_value} = -1)
  THEN " "
  ELSE {proc.group2_display}

It may not help,but worth a try





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think you should place the following fields in the detail section:

{proc.group2_type_id} {proc.group2_value} {proc.group2_display}

... and then note whether they always have the same value within group #2--I think you will find that they don't. Your customized name for group #2 would pick up on the value of these fields in the first row of the group.

-LB

 

[nlaliberte]

Unfortunatly they all do have the same values, as that was the first thing I checked. The values are the same when both inserting the fields in the detail section and running the stored proc through Management Studio.

{proc.group2_type_id} = 70

{proc.group2_value} = -1

{proc.group2_display} = 'AA - Unkown'

It's also worth noting that if I create a formula to mimic the logic that takes place in the custom function and place that in the detail section it returns the expected (correct) result.

 

[lbass]

So it is displaying correctly at the GH2 level, but not at the detail level? Instead of using the groupname field in the detail section, just create the same formula that your used to customize the groupname and add it directly to the detail section. It sounds like the "customize groupname" feature only applies to the group level or something.

-LB

 

[nlaliberte]

Okay,

So the issue was really with the title being displayed in the Group Footer, the fields in the detail section were strictly debug fields.


I'VE FIGURED OUT THE CAUSE OF THE PROBLEM, though I still don't quite understand why this was an issue. The code in the Custom Function read;

CASE 70: //linked
SELECT valueField
CASE -1: ""
DEFAULT: displayField

The fix to the issue I simply changed the "" to " ". As so;

CASE 70: //linked
SELECT valueField
CASE -1: " "
DEFAULT: displayField

For some reason this causes the Group Name to display correctly in all sections, and not just the group header. When Case -1 resulted in "" with no spaces, the Group Name reverted back to the previous Group Name for the Detail and Footer section, until the page changed at least.

This could be because "" does not have a value, and it reverts back to the last 'known' value??? I would still think that since the custom function evaluates per record, that it should return correctly for each detail, especially if it's able to return correctly in the header.

Either way, I've discovered the fix, even if I don't know how it really works.

Any thoughts lbass?