change background color in crosstab's cells

[OhioSteve]

My objective is to change cells' background color in my crosstab report. I want to alternate (by column) between silver and transparent.

I already know that I need to do this:
1)right-click the cell.
2)choose format field.
3) choose the border tab.
4) check the "background" checkbox. Choose silver as the color. Press the formula button.
5) write a formula to change the background color.

I do NOT know the syntax for #5. I tried writing "actually, I only want silver for every other column". The machine did not understand that

 

[DataDog]

I've been writing Crystal Reports since Rev 5, and I've never found a way to do what you are asking. Doing the same thing in the details is quite easy, oddly enough.

By the way Go Bucks! (my wife is a Buckeye - twice, in fact)

DataDog
'Failure Is Not An Option'

 

[IdoMillet]

See: thread149-1197856

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Try the following, which assumes you are working with one column field. First create a formula that concatenates your column field with a number. Let's say your column field is {table.state}.

//{@concat}:
whilereadingrecords;
numbervar cnt;
stringvar x;

if instr(x,{table.state}) = 0 then
(cnt := cnt + 1;
x := {table.state};
);
totext(cnt,0,"") + " " + x

Then use {@concat} as your column field instead of {table.state}. Then select group options->options tab->customize group name and select {table.state}.

Next select an inner cell->format field->border->color->background->x+2 and enter:

if remainder(val(left(gridrowcolumnvalue("@concat"),2)),2) = 0 then crSilver else crNocolor

Repeat for the column total. Then select the column label and format it with:

if remainder(val(left(currentfieldvalue,2)),2) = 0 then crSilver else crNoColor
-LB

 

[OhioSteve]

lbass, I think that I can implement a variation of your idea.

Actually, I already have made the distinction between what columns the user sees and what value is actually stored in the columns. I made a plain date field my column value. Then in the crosstab expert I selected that field and choose group options. A pop-up appeared. I choose the "options" tab on the popup. Then I choose a different field dayDate, for the user to see.

Given that situation, perhaps I should do this:

1) most languages have a function that converts a date to an integer. If that exists in Crystal, declare an int and put that value into it.

2) "remainder" seems to be your modulus function. Use it on my integer to toggle between noColor and silver.

 

[lbass]

I don't know why you can't follow my suggestion as is.

-LB

 

[OhioSteve]

lbass, I will give you the most honest answer that I can.

I understand that you are giving me a correct answer. If I followed your instructions perfectly, my crosstab would be striped. So why not just copy your code?

I do not understand WHY your solution is correct, so I would just be copying blindly. I might do that if I was desperate but in this case I have a couple days before my deadline. So I would prefer to incorporate advice from online people, people IRL, and my own experience. I want to invent a solution that is mine and that I understand.

Actually, I just found a solution. More in my next post.

 

[lbass]

The code just attaches a number to each column that can then be used in the remainder formula to color every other column.

You can, of course, manually color the columns by referencing the specific value of every other column field, e.g.,

if gridrowcolumnvalue("table.field") in ["A","K","M"] then crSilver else crNoColor

-LB

 

[OhioSteve]

As I said, I was already showing the user one column value, and using a different one to form the columns. The user saw something like "Fri, Jan 3", and the database read something like "1/3/2006". The real value was called workdate. Using that is a jumping-off point, I did this~

In my SQL Server query, I eliminated workdate. I replaced it with pseudoJulianWorkDate. That field is an integer equal to the current date - Jan 9, 2005. I knew that I would never have records earlier than Jan 9, 2005.

I rebuilt the Crosstab using pseudoJulianWorkDate as my columnvalue. So now my columns had values like 400, 401, 402, etc.

I went to the appropriate place and added this code:

if remainder(Query10b.pseudoJulianWorkDate),2)=1 then
crSilver
else
crNocolor

That caused an error! Ugh. I reviewed lbass's code and saw how he used gridrowcolumnvalue. I tried this:

if remainder(gridrowcolumnvalue("FIRS1_timesheet02b.pseudoJulianWorkDate"),2)=1 then
crSilver
else
crNocolor

CR accepted that without complaining. When I previewed the report, it had vertical stripes!

 

[IdoMillet]

LB's approach is better unless you are 100% sure your data will not have holes in it (e.g., a missing "even" day).

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[OhioSteve]

Okay IdoMillet, I will be happy to continue the conversation. Incidentally, for this report the column value will be a date. The row value is salesAgentName. The cell value is salesTotal. So we are summing sales by agent/date.

I understand that a formula could be used as the column value. I also understand that lbass's formula would create a formula that increase by one each time it ran. Finally, I understand that you can show the user a DIFFERENT value than the actual column value. So I assume that in lbass's solution, @concat would be the column value. I would show the user something prettier, like "Sun, 3/19".

Now, this is where I get confused. Crosstabs need two real values for grouping. When my report runs, I need each sales agent's name and each date. Otherwise how will the machine calculate the total for each agent/date?

That's why I used a julian date. I can use for two things: my formatting code, and actually calculating the subtotals.

These are challenging concepts to master...I enjoy learning about them. I look forward to your responses.

 

[IdoMillet]

My comment simply warned you that if you might have "holes" in your date range, the effect could be spoiled.

LB's approach would always work but I suspect, since it starts with WhileReadingRecords, that it depends on the source records being sorted by date. There are ways to apply her approach even in cases where the records are not sorted by date.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com