Technical Questions: Alternate group row color with multiple groups

Print Page | Close Window

Alternate group row color with multiple groups

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7794
Printed Date: 29 Apr 2024 at 5:20am

Topic: Alternate group row color with multiple groups

Posted By: elie234
Subject: Alternate group row color with multiple groups
Date Posted: 24 Sep 2009 at 2:11pm

I have a report with multiple groups where I would like to alternate row color in the group footer. I have seen the tip where one makes a running count of the groupnumber and then evens have color and odds don't. But that doesn't work in this instance since there are mupltiple groups and I only want to alternate colors for the footers of 2 of the 6 groups. In this case want to alternate colors for group footers 6 and 5 (i.e. 6 and 5 together would have no color and then the next 6 and 5 would be silver etc.) How could I alternate in this case?

Replies:

Posted By: Jyothi Yepuri
Date Posted: 24 Sep 2009 at 4:39pm

Try this in selection expert color condition for GF5,GF6 ..no need for Running total

IF Remainder(GroupNumber,2) =0 then
crSilver
else
crNoColor

HTH,
Jyothi

Posted By: elie234
Date Posted: 25 Sep 2009 at 7:06am

Thanks Jyothi, but that doesn't work in this case (I think it is basically the same idea as the running total). It calculates group numbers for all of the groups in the report but since I am surpressing some groups the calculation doesn't work to alternate on just groups 6 and 5 which are the displayed groups.

Posted By: DBlank
Date Posted: 25 Sep 2009 at 10:13am

Use a variable or RT that excludes the suppressed items and only "counts" shown items then replace the groupnumber with that option.

likely your suppression criteria can be reused in the selection criteria (evaluate as a formula) in a RT.

Posted By: lockwelle
Date Posted: 25 Sep 2009 at 10:55am

I agree with DBlank, I would use a variable and only increment it when it is appropriate. In your case I believe I would increment it in GF6, but if you have multiple GF6 inside of GF5...OK, if you want GF5 and 6 to match, I would increment the variable in GH5, this way, ALL GF6 and GF5 will be the same for a GF5. Then the changing of the color is easy.

HTH

Posted By: elie234
Date Posted: 25 Sep 2009 at 11:03am

Is there a syntax to increment on the appearance of a specific group, like to do a count for each appearance of GH5?

Posted By: DBlank
Date Posted: 25 Sep 2009 at 11:07am

Not that I am aware of but you can accomplish the same thing via a variable (lockwelles preference) or a Running Total (my preference).

Likely you can use the same RT or variable for this need and you will not need to create 2 of them.

If you need help with that please post your grouping set up and conditional suppression requirements.

Posted By: elie234
Date Posted: 25 Sep 2009 at 11:21am

Thank you. There are 6 groups. There is just straight suppression not conditional. Group headers 2 and 3 are shown, rest are suppressed. Group footers 6,5,3,2 shown, 4,1 suppressed.

Posted By: DBlank
Date Posted: 25 Sep 2009 at 11:41am

So when you applied Jyothi's approach you did not get what you wanted because synching on the 5-6 grouping even though there is a 1:1 relationship between them ? So the question is what is the group level that keeps it synched for your coloring issue? Once you identify that you use. It is likely whatever the field that you are using for group 5.

Try and create a RT field as...

Name:BackGroundColor

Field to Summarize: group 5 field

Type of summary: Count

Evaluate= on change of field (select field used for group 5)

Reset = Never

Place this RT on your GF5 and and again of GF6 and see if it is giving you what you want in terms of even/odd. If so use it in place of the GroupNumber in J's original formula.

If Remainder(#BackGroundColor,2) =0 then
crSilver
else
crNoColor

Does that work?

Posted By: lockwelle
Date Posted: 25 Sep 2009 at 12:00pm

Variables, definitely my preference. Just in case DBlank's solution doesn't work for you (it probably will) here is what I would do.

1) create formula call it increment:

shared numbervar iCount := iCount +1;

"" //hides the counting on the report

2) place it in GH5

3) goto Section Expert, GF5, Color

a) check the Background color box

b) click x-2 and type:

shared numbervar iCount;

if iCount mod 2 = 0 then //same as remainder()

crSilver

else

crNoColor

Repeat for GF6

Hopefully one of else has a solution for you.

Posted By: elie234
Date Posted: 25 Sep 2009 at 12:08pm

I get an error in the background color formula saying to please contact Business Objects! Haven't gotten that one before. Sounds bad.

Posted By: elie234
Date Posted: 25 Sep 2009 at 12:09pm

That was on the Running total suggestion btw.

Posted By: DBlank
Date Posted: 25 Sep 2009 at 12:10pm

if you place the RT field on the report canvas on GF5 and GF6 (without color change formula use) is it working as you want it to or are you getting an error (or incorrect #'s)?

Posted By: elie234
Date Posted: 25 Sep 2009 at 12:14pm

tried the shared variable approach..i get shading but it is not alternating correctly..will retry running total

Posted By: elie234
Date Posted: 25 Sep 2009 at 12:18pm

when i put the rt formula in GF6 and GF5 I get numbers but incorrect ones.

Posted By: DBlank
Date Posted: 25 Sep 2009 at 12:22pm

what are you getting?

If you post some actual grouped data it may help explain.

You may need to bump my suggesstion up to using group level 4 field.

Likely you just need to tweak either of our suggestions to get it to give the correct increment changes. Leave the RT or variables on the canvas and make the changes to see when you get the right combination.

It is hard to tell you what will work without knowing the actual report data and how the grouping is working on this. I am also going under the assumptiont that you always have a 1:1 relationship in group 5 and 6. If not I think there is a logical issue in how you want to color this or I am misunderstanding something about your design and desired output.

Posted By: elie234
Date Posted: 25 Sep 2009 at 12:29pm

5 and 6 are always displayed in pairs. The desired output is to alternate shading of pairs of 5 and 6. (6-5 not shaded, 6-5 shaded, 6-5 not shaded).
Sample data for running total field:
GF 6:       2
GF 5:       2
GF 6:       4
GF 5:       4
GF 6:       6
GF 5:       6
GF 6:       22
GF 5:       22
GF 6:       36
GF 5:       36
Goes on to pairs of 60, 71

Posted By: DBlank
Date Posted: 25 Sep 2009 at 12:41pm

try it using G6 field. Sorry but I can only guess as grouping impacts it but the raw data is the key. If that does not work look up the "grouping chain" for what field is your key field that changes everytime for your grouping at that level. Display the group headers and look for it that way. Which GH changes for each displayed GF. Once you see it change your RT to changing on that field then you should see it go.

Posted By: elie234
Date Posted: 25 Sep 2009 at 1:24pm

Thanks for your help.

Print Page | Close Window