Cross tab formatting 2

[danausten]

Is there any way of formatting a cross tab so that every other row has a different background colour?

I created the following formula and applied it to the background of each column on the cross tab but the colouring goes out of synch and the cross tab ends up looking like a chess board!

whileprintingrecords;
numbervar c;
c:=iif(c=255,100,255);
color (0,0,c)

Anyone have any ideas?

Thanks,

Dan Austen

 

[danausten]

I'm using CR 10

 

[lbass]

I'm not sure about the capabilities of CR 10, but in CR 8.0, you could use a formula like the following for the grid:

In preview, select a grid cell or the row total. If the row field (label) happens to consist of consecutive numbers, then use something like the following where "table.row" is the row field without its curly brackets:

if remainder(GridRowColumnValue("table.row"),2) = 0 then color(0,0,255) else crWhite

If the row fields are not consecutive numbers, then you could hard code the values, as in:

if GridRowColumnValue("table.row") in ["FedEx","Parcel Post", "Purolator"] then color(0,0,255) else
if GridRowColumnValue("table.row") in ["Loomis","Pickup", "UPS"] then crWhite

For the labels you would use (for numerical text):

if remainder(val(currentfieldvalue),2) = 0 then color(0,0,255) else crWhite

Or, if hardcoded:

if currentfieldvalue in ["FedEx","Parcel Post", "Purolator"] then color(0,0,255) else crWhite

-LB

 

[danausten]

Thanks for the reply.

The row fields are not consecutive numbers and there can be thousands of rows which can change reguraly so it is not practical to hard corde the row values...

 

[lbass]

Dan,

My apologies--I didn't even try your formula, which works great (*) for coloring every other row. To solve the problem of the labels, use the reverse sequence of color values in your iif statement:

whileprintingrecords;
numbervar c;
c:=iif(c=100,255,100);
color (0,0,c)

-LB

 

[danausten]

I did actualy try reversing the statement but it doesn't seem to have any effect if you have more than 1 column...

 

[lbass]

I tried this both for (multiple) columns and rows and it worked for me. Perhaps I'm confused about what you are trying to do. In your first post you said you wanted to color every other row, but then you went on to say you were trying to apply the formula to each column of the crosstab--which I took to mean the column labels--since the formula does make every other ROW a different color. So are you trying to color the background of every other row or column label or are you trying to color every other column of data, not every other row?

-LB

 

[danausten]

I'm trying to colour every other row.

Hopefully the following table will explain a little better!

Jan Feb Mar Apr May
Customer A 10 10 20 10 10 row colour = blue
Customer B 20 20 30 20 20 row colour = white
Customer C 10 15 40 10 20 row colour = blue
Customer D 15 10 10 20 10 row colour = white
Customer E 30 15 25 30 40 row colour = blue

 

[lbass]

Okay, I think I have the solution:

For the grid, use:

whileprintingrecords;
numbervar c;
c:=iif(c=255,100,255);
color (c,c,255)

For the row label, use:

whileprintingrecords;
numbervar d;
d:=iif(d=100,255,100);
color (d,d,255)

Or, instead of using variable d, variable c could have been declared local in each case. As it was, the variable was interfering with the row formula. I also changed the formulas so that the default was white.

-LB

 

[danausten]

Still not working...

Here is how the grid appears now (actual format)

1 2 3 4 5 6
---------------------------------------------------
0 0 4 2b 1 3b 5
2 1b 1b 5 7b 9 5b
4 6 1 0b 4 3b 7
8 1b 7b 2 1b 8 9b
13 1 1 0b 0 0b 1
15 9b 4b 3 2b 1 0b

the numbers with b next to them indicate that the cell is coloured blue where as the rest are coloured white.

The column field is a date field grouped by day.

HTH

 

[lbass]

Well, that is truly bizarre. The solutions I suggested work when I test them. What I don't understand is why you would get different results per column, when you are only entering the formatting one time for the entire grid. I'm assuming this is an inserted crosstab, and that you are right clicking on a cell in the grid->format field->border->background->x+2 and entering:

whileprintingrecords;
numbervar c;
c:=iif(c=255,100,255);
color (c,c,255)

Where have you placed the crosstab? You might get odd results if it is placed in a group section.

-LB

 

[danausten]

It is bizzare! I don't understand it either as I've created the crosstab exactly as you've stated. The crosstab is placed in the report footer.

 

[danausten]

It is bizzare! I don't understand it either as I've created the crosstab exactly as you've stated. The crosstab is placed in the report footer. I'm guessing it must be something to do with the data?

 

[lbass]

I've seen in some other threads the suggestion that sometimes the iif function doesn't work consistently in CR (I haven't found this to be a problem myself though). I wonder if you changed it to a simple if-then whether the problem would resolve:

whileprintingrecords;
numbervar c;
c := if c = 255 then 100 else 255;
color (c,c,255)

Also, do you have any other special formatting on the crosstab? Are you using anything other than the usual ascending order for groups, or any suppression? Are there any group selects on the report? Not sure any of this would matter...

Other than that, this might be a problem specific to your version (or requiring a service pack?). I'm using 8.0.

-LB

 

[danausten]

Tried your if-then suggestion but still no luck. Also, tried the report in Crystal 9 and still the same problem.

There is no special formatting or grouping...

 

[lbass]

I'm out of ideas, and as I say, I can't recreate the problem--the formatting works when I test it. You might try recreating the crosstab from scratch--just in case there is some corruption. Other than that, it would be great if someone else with CR 10 could test this, and see if the problem is version-related.

-LB

 

[danausten]

Well, thanks for all your efforts.

 

[hansdebadde]

For what it is worth...I was sort of able to recreate this problem. I know this thread is old but I thought I might add something to it as I have been trying to do this. I used all the formulas that lbass suggested and the first column was different in color by one row compared to all the other columns.
For example
grey white white white
white grey grey grey
grey white white white

I found that by putting the row total on the left it fixed the inconsistent row coloring.

 

[hansdebadde]

Ah hah! I got the full checkerboard when I changed the font size of any of the fields. Even if I change the font back it remains a checkerboard. If I undo the change though it gets rid of the checkerboard. Weird? Just make sure you set your font sizes before doing the alternating row formating.