Crosstab: Formatting Summarized Fields

[csxcandy]

I would like to add a conditional format to the summarized fields of a crosstab in CR11. I have time periods set up as my columns, and people as my rows. I would like to color code the summarized field such that if the second summarized field is less than the first summarized field (week 1 is 9.8, and week 2 is 9.7), the background turns green. Or if the second is greater than the first it turns red (week 1 is 9.7 and week 2 is 9.8). If they are equal, no color coding is necessary. There are 4 weeks of data, so 4 columns.

Ex.
Wk1 Wk2 Wk3 Wk4
Person1 9.8 9.2 9.6 9.6
Person2 8.0 8.0 8.5 8.0
Person3 7.5 5.4 5.4 7.5

 

[lbass]

What about columns 3 and 4? Are you only coloring column 2?
Or is it that you want to color each column based on a comparison with the previous week?

-LB

 

[csxcandy]

Yes I wanted columns three and four to have the same formatting as column two. I tried something different yesterday. I added a row for the Quarter goal. Now I want the summarized fields to be formatted based on the goal. I can turn them red and green, but the formatting isn't based on their goal. (Not every person's goal is the same.) I can't do a highlight field based on > or < a certain number. The highlight expert doesn't allow for > or < another field.

 

[lbass]

Do you mean that you added a summary field for the goal? I don't see how it would work with a goal for the row. Please provide a sample of what the crosstab now looks like. Can I assume that you now only want to compare the original summary with the goal?

-LB

 

[csxcandy]

The report now looks like this:
(row 1) (row 2) Wk 1 Wk 2 Wk 3 Wk 4
Person 1 Qtr Goal Summary Sum. Sum. Sum.

So the actual report looks like:
Wk 1 Wk 2 Wk 3 Wk 4
Smith 9.0 8.1 7.2 9.3 8.6
Jones 6.7 7.5 6.0 6.2 3.4

I want the Summaries for each week to be highlighted in red (either red font, or the background red, or something) if the summary is greater than the goal for that row. Then I want the summary to be green if it is less than or equal to the goal. This is different from the first request where I was just going to show improvement week to week. Now I am actually comparing it to a goal. If everyone had the same goal of 10.5 as example, I know I could do the highlight expert and say if the summary is greater than 10.5 then crRed. But since the goal changes, and is row specific, I don't know how to put a formula in to format the summaries.

 

[lbass]

First go to the customize style tab and choose horizontal summaries. Then add the goal as your first summary field and insert a maximum on it, with your usual summary as the second summary field. Then select the goal summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar goal := currentfieldvalue;
false

Then select the second summary field->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar goal;
if currentfieldvalue > goal then
crRed else
crGreen

You can then suppress the goal summary column and resize the width to minimize it.

-LB

 

[rajlan]

My report looks like this:

Column1 Col2 Col3 Col4 Col5
Type1 type A sum1 sum2 sum3
type B sum1 sum2 sum3
Type2 type A sum1 sum2 sum3
type B sum1 sum2 sum3
type c sum1 sum2 sum3
Type3 type A sum1 sum2 sum3
type B sum1 sum2 sum3
Type4 type A sum1 sum2 sum3
type B sum1 sum2 sum3
type C sum1 sum2 sum3
type D sum1 sum2 sum3

I want to highlight the summary fields based on Column 1 value.
If col1="Type1" then col2,3, &4 should be highlighted with red color
If col1="Type2" then col2,3, &4 should be highlighted with blue color and so on
I want a color differentiation between the segments Type1, 2,3 & 4.

Thanks in advance

 

[lbass]

Please identify your column field, row field, and summary field--as entered into the crosstab expert, using real field names. Then describe what you are trying to do. Your current description is confusing.

-LB

 

[rajlan]

crosstab Columns: warehouses (values: warehouse 1,warehouse 2,warehouse 3,warehouse 4 and so on)

crosstab rows:
1)Category (values: EXPORT, ORGANIC, REGULAR etc)
2)Variety (values: V1, V2, V3)

crosstab summary filed: Qty

I would like to see all the Export items to be in RED
ORGANIC in Blue
Regular in green color.
colored text[/color] = colored text


WH1 WH2 WH3 WH4

EXPORT V1 10 11 78 65
V2 56 43 98 34
V3 67 43 23 90


ORGANIC V1 98 56 543 43
V2 98 56 43 98


REGULAR V2 76 45 98 34
V3 76 34 87 09

 

[lbass]

You didn't provide the actual field name for category, so I will call it {table.category}. In preview or design mode, select your two row labels and the summary in the inner cell->right click->format field->font->color->x+2 and enter:

select gridrowcolumnvalue("table.category") //replace curly brackets with double quotes
case "EXPORT" : crRed
case "ORGANIC" : crBlue
case "REGULAR" : crGreen
default : crBlack

-LB

 

[rajlan]

Thanks a lot. Solved my problem.