How to change Crosstab column header background color based on field value?

[HuseyinEce]

Hi,

Please look at the attached sample crosstab report. The report shows each job's status in a month(1,2,3,4.. is days of the month.)
I am trying to change days'(1,2,3,4...) background color based on my one of my field value(field is bd). This field keeps value that shows that day business day or not. If it is weekend then my value is “N” other wise is “Y”.
I am trying to do this under format field editor --> Border-->Background-->X2.
I have used below code but it doesn’t work.

“if {S_CCO_JOB_STATUS;1.bd} = "Y" then crGreen else crNoColor “

Here is the attached file link:

http://www.mediafire.com/?dg56t273dadoxx1

Can anyone please help me how to do that?

Any help is greatly appreciated,

Thanks

 

[NickieP]

In report design view, select the heading or field in the crosstab and right click to get the menu option highlighting expert
then choose your condition, e.g.if bd field = Y then background = green, text = red or whatever.
This is what conditionally changes font or background colour in Crosstabs rather than formatting the field.
(I can't see your file to see the example, our sec settings don't allow)

 

[HuseyinEce]

Hi,

The problem is Y or NO value is not in that field.

 

[NickieP]

Test it though, not sure it has to be that field, you can select a field to highlight based on the value of another field.

 

[NickieP]

Just tested it. You can make a CT field have a conditionally-coloured background or font colour not based on the field you are colouring, but another field in the CT.
If you don't want the other field to display in CT, you can suppress it and the highlighting will still work.
Or make the field you don't want to show a v small font and everything else in that row the same size, with a black font and a black background you can resize that row to look like a bold outline.

 

[HuseyinEce]

Hi,

highlighting expert shows only field that holds values 1,2,3,4.. (days). Sorry I am new for the Crystal report. What is CT? In Cross tab expert do you want me to add the field holds values N or Y to the column then suppress? can you be more clear please as I am new ?
Appreciate your help.

Thanks

 

[NickieP]

Our IT Security Manager sent me the jpg, so I can see what you meant.
CT above is just shorthand for crosstab

Insert a new column heading underneath the 1,2,3
so cell below 1 says Y or N, cell below 2 says Y or N etc...

- You have to have the CT in a group header or footer that only shows one calendar month, or it can be in report header/footer if whole report is one calendar month only

in design view,right click on the CT field that shows N F P
choose highlighting expert - see dropdowns to select the colour to change in this field based on the value bd field

so in right hand side of highlighting expert:
value of @bd is equal to Y background = Lime
on the left side of the highlighting expert it will say
sample ab12 @bd=Y with the ab12 being highlighted in lime green colour,
say OK

go to the row beneath 1,2 that says Y or N and select format field, suppress this field

The cells that were yellow/red/green in your example are now green if the day 1-31 is a bd in my example I tried.

(can't upload screenshots sadly, not sure if we are allowed pm or to disclose mail addresses on here)

 

[HuseyinEce]

Hi,

Please see the attached screen shots after applying above process.
It did not work. :-(

Here is the link for the attached file:

http://www.mediafire.com/?8o4s2370i1zfy3i

Thanks

 

[ianoctdec]

HuseyinEce,

I cannot access your report, but I assume that your crosstab columns are of type date.
In this case, right click on the column field > Format > Border > Background > x+2:

if dayofweek(currentfieldvalue) in [1, 7] then
crgreen
else
crblack

Dana

 

[HuseyinEce]

Hi,

That column values shows months days but it is not date field.

Column shows 1,2,3...for each month and I just want to highlight background color of holiday days.

Any help ?

Thanks

 

[NickieP]

In the cross tab column heading where it shows 1, 2, etc it will definitely colour BD's one colour and non- BD's another colour or leave uncoloured.

But this can only work if the CT is in an area of the report such a a group on calendar month or a smaller range like week where the day # 1 cannot be both a BD AND a NON-BD in the same CT at the same time.

 

[HuseyinEce]

Hi ,

So, do you mean there is no why to do that?

Thanks

 

[lbass]

Create a master formula that identifies holidays, adding in whatever dates you consider holidays, like the following and place it in the report header:

whilereadingrecords;
datevar array hols := [date(2011,12,31),date(2012,1,1), date(2012,12,31),date(2013,1,1)];
0

Then select the crosstab date column label->right click->border->color->background->x+2 and enter:

datevar array hols;
if not(currentfieldvalue in hols) and
dayofweek(currentfieldvalue) in 2 to 6 then
crgreen else //crgreen for business days
crnocolor

-LB

 

[HuseyinEce]

Hi,

The field that holds 1,2,3.. value is not a date value. Just shows days of month. Also it is hard to put all the holidays and weekends in array for the coming years.

Thanks

 

[lbass]

Well, then you need to add the date as the column field, and then use format field->display string just to show the day of month, as in:

totext(day({table.date}),0)

-LB