Alternate Row Shading Crosstab 1

[hoagieryder]

I know there have been a bunch of threads addressing this question, but I can't find an answer that works. I have a very simple crosstab in CR XI.

Rows: Trends.SrcName
Column: Trends.Dates
Summarized Field: Average of Trends.Trend

I want every other row to be shaded. The crosstab is in Report Header and will never cross on to a second page. The data for SrcName and Dates vary for each company database I run the report on. Is there a formula I can use? If a manual cross tab is better I would assume I would have to use formula field saying:

If trends.dates = (the first date) then trends.trend

I know how I would do this if I knew the first date (ie. trends.dates = "12/24/10") but it is not constant from database to database. How would I address this?

 

[lbass]

What methods have you tried so far?

-LB

 

[hoagieryder]

I used the method from this thread:

http://tek-tips.com/viewthread.cfm?qid=1594363

... the lables and first columm line up, after that, they alternate.

 

[lbass]

Please review the instructions in that thread and make sure you implemented them exactly as I specifically set that up to correct the issue of odd versus even numbers of rows.

-LB

 

[hoagieryder]

As far as I know I have set it up identical to how you wrote it:

The crosstab is in GH1.

I have a formula field @reset that has been placed in both the report header and GF1 that says:

whileprintingrecords;
numbervar d;
if remainder(distinctcount({Trends.SrcName}),2) = 0 then
d := 200 else
d := 255;


The inner cells:
whileprintingrecords;
numbervar c;
if c = 0 then
c := 200
else if c = 255 then
c := 200 else
c := 255;
color(200,c,200)

Row Lables:
whileprintingrecords;
numbervar d;
if d = 200 then
d := 255 else
if d = 255 then
d := 200;
color(200,d,200)

 

[lbass]

It looks like you did set it up correctly. However, in your first post, you said the crosstab was in the report header and did not cross pages. Now you are saying you put it in a group header--and I'm wondering if it now crosses pages. If it does, then go into the group expert->options tab-> and check "keep group together".

-LB

 

[hoagieryder]

Yea I decided to put the crosstab in GH1 because I did not want @reset and the crosstab in the same section, I wanted it above it(not sure if that matters). I don't think it is running over the page, here is a screenshot:

 

[lbass]

Okay, it turns out that it matters whether row and column totals are shown. With the current formulas, if you added totals, I think it would work properly.

To make this work without totals, do the following. Change the reset formula to:

whileprintingrecords;
numbervar d;
[red]numbervar cnt := 0;[/red]
if remainder(distinctcount({Trends.SrcName}),2) = 0 then
d := 200 else
d := 255;

Select the inner cells and add a formula to format field->suppress->x+2:

whileprintingrecords;
numbervar cnt;
if cnt/distinctcount({Trends.SrcName}) = 1 then
cnt := 0;
false

Then change the color format formula for the inner cells to:

whileprintingrecords;
numbervar c;
[red]numbervar cnt;
if remainder(cnt,distinctcount({Trends.SrcName})) = 1 then
c := 255 else
c := c;[/red]
if c = 0 then
c := 200 else
if c = 255 then
c := 200 else
c := 255;
color(200,c,200)

-LB

 

[hoagieryder]

Thanks for the help LB but it still doesn't seem to work. Using the technique above isn't cnt always equal to 0?

 

[lbass]

Oops, sorry--please change the formula to:

whileprintingrecords;
numbervar c;
numbervar cnt[red] := cnt + 1[/red];
if remainder(cnt,distinctcount({Trends.SrcName})) = 1 then
c := 255 else
c := c;
if c = 0 then
c := 200 else
if c = 255 then
c := 200 else
c := 255;
color(200,c,200)

-LB

 

[hoagieryder]

Works great, thanks for the help!