Formatting 3

[Simon9999]

Hi all,

I am using CRXI on sql database

I have a report with 52 columns (weeks) each with numbers 0-5. I need to format them conditionally i.e. 1 = yellow, 2 = blue etc. I can do this with the highlight expert to one column but cannot apply the formatting to all 52 and would therefore have to repeat the process 52 times which I am trying to avoid.

I can select all 52 fields and Format Fields>Border > Background but then I can only reference one of the fields e.g. week 1 which applies the formatting from 1 week to all 53.

Any suggestions or do I need to do this all 52 times?

Thanks

Simon

 

[CoSpringsGuy]

select all 52 fields and Format Fields>Border > Background

then use
if field =1 the then crred
else if field = 2 then cryellow
else if field = 3 then crblack
etc.....

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[CoSpringsGuy]

i noticed you didnt mention it in your post so let me add something

go to
select all 52 fields and Format Fields>Border > Background

then click the X-2 button

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[CoSpringsGuy]

field is the name of your field by the way... I just noticed that that might not be obvious in my post

if {your_field} = 1 then crred
etc....

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[Simon9999]

Thanks

A was aware you could apply formatting in this way however the {field} is different each time.

So I have 52 objects - e.g. week 1, week2 etc.

If I go to X2 and enter
if {week1} = "5" then crred

it will apply that formatting to all 52 objects, problem being that I want week 2 to be

if {week2} = "5" then crred

Any ideas if it is possible?

 

[lbass]

You can select all fields and then use a formula like this:

select currentfieldvalue
case 0 : crYellow
case 1 : crRed
case 2 : crBlue
case 3 : crGreen
case 4 : crAqua
case 5 : crPurple
default : crNoColor

-LB

 

[CoSpringsGuy]

try usign case then I dont think you have to specifiy the field

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[CoSpringsGuy]

lol yeah like he said

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[Simon9999]

Awesome

Thankyou both for your help, has saved me a load of time.

Incidently, do you know if it is possible to do the same sort of thing with the actial formulas.

e.g. for week1 I say:
if date(field) = {Week1 date} then 5

then in week2 I say
if date(field) = {Week2 date} then 5

I have had to copy and past this 52 times and modify it and cant think of an easier way to do it.

Thanks again for your help

 

[AndyMc]

My understanding is that the cool function she used in the formula, CurrentFieldValue, can only be used in formatting formulas, not standard formulas.

Thanks, LB, I know I'll make use of this function a lot going forward.

Andy

 

[lbass]

Simon,

Is there a reason you are not using a crosstab for this? It might be helpful to see your actual formula (not a simulated one) to know whether this would make sense.

-LB

 

[Simon9999]

Hi Ibass

My actual code for week 1 is as below, for each following week {@First Monday} and {@End Sunday}is replaced with {@First Monday}+7 then +14 etc.

{@First Monday} is the first day of this week

I wasn't sure if a cross tab would work for this as for each column I also need a heading that gives the first day of the week. If this can be done in a crosstab then I prob should have used one.


if
date({BidEventDates.PQQ-Issue}) in {@First Monday} to {@End Sunday}
or
date({BidEventDates.PQQ-Submission}) in {@First Monday} to {@End Sunday}
or
(date({BidEventDates.PQQ-Issue}) < {@First Monday} and date({BidEventDates.PQQ-Submission})>{@End Sunday})
then "1"

else if
date({BidEventDates.ISOP-Issue}) in {@First Monday} to {@End Sunday}
or
date({BidEventDates.ISOP-Submission}) in {@First Monday} to {@End Sunday}
or
(date({BidEventDates.ISOP-Issue}) < {@First Monday} and date({BidEventDates.ISOP-Submission})>{@End Sunday})
then "2"

else if
date({BidEventDates.ITN-Issue}) in {@First Monday} to {@End Sunday}
or
date({BidEventDates.ITN-Submission}) in {@First Monday} to {@End Sunday}
or
(date({BidEventDates.ITN-Issue}) < {@First Monday} and date({BidEventDates.ITN-Submission})>{@End Sunday})
then "3"

else if
date({BidEventDates.BAFO-Issue}) in {@First Monday} to {@End Sunday}
or
date({BidEventDates.BAFO-Submission}) in {@First Monday} to {@End Sunday}
or
(date({BidEventDates.BAFO-Issue}) < {@First Monday} and date({BidEventDates.BAFO-Submission})>{@End Sunday})
then "4"

else if
date({Opportunity.Service Start}) in {@First Monday} to {@End Sunday}
or
date({Opportunity.Contract End Date}) in {@First Monday} to {@End Sunday}
or
(date({Opportunity.Service Start}) < {@First Monday} and date({Opportunity.Contract End Date})>{@End Sunday})
then "5

 

[lbass]

I'm not really clear on what you are trying to do, but you realize, don't you, that this formula will only evaluate until a record meets one of your criteria and then it will stop?

It looks like you are essentially hardcoding the columns using a formula with currentdate, and since you've gone this far down the road, it probably doesn't make sense for me to be second-guessing you.

-LB

 

[Simon9999]

Thanks for the reply

Yes I am aware it stops when it meets criteria. Basically I want it to show a number from 1-5 depending on which bid stage we are at and we would only ever be bidding one of the 5 stages.

I am trying to show an overall picture of bidding for the next year in a gant chart. So for example if we are bidding stage ITN a 3 would be displayed which could then be coloured blue. For each of the following columns (i.e. weeks) another 3 would be displayed until the ITN was submitted. Later on a 4 might be displayed if we were bidding the next stage etc.

Cheers

Simon

 

[lbass]

Then your approach probably makes more sense than using a crosstab anyway.

-LB