Backcolor depending on value in other table 1

[ErnstJan]

Hi,

I am using Crystal Reports that comes with Visual Studio 2005.
Have a tabel consisting of 12 row and 32 colums.
In the colums is a ID value that corresponde with a record in an other table.
In that record is a field containing a color number
Now I want to set the fields backcolor to that color

Need to know 2 thinks.
1) Is it possible in Crystal Reports to convert value like -65536 to a known color.
In vb.net this could be done with color.FromArgb([number])

2) Can you make a formula to set the backcolor depending on the value in a second table.
Before I start making a big query with a lot of links.

Thanks in advance.

Ernst Jan

 

[IanWaterman]

Right click filed and Format selct Border tab then check Back ground coluor.

YOu can then enter a formula to generate RGB colour for the back ground, based on number in your field.

eg RGB(123, 213, 132)

Ian

 

[ErnstJan]

Ian,

Thanks for you repley
What you tell I know.
But my color number is not in the 3 parts.
Have looked around and fear that its not possible in a report.
Can someone confirm this.

And then there is point 2 of my question.

Greetings,
Ernst Jan

 

[IanWaterman]

As I said you need to develop a formula which replicates

color.FromArgb([number])

and create an RGB definition.

Could be as simple as

If {FieldName} = 65536 then RGB(123, 231,132) else
If {FieldName} = .....

Ian

 

[CR85user]

If you didn't need specific colours then you could use your base number as a base for auto generating colours.

Could you gie me a couple of examples of likely numbered results and I will giev you an example to try.

Also are you trying to colour the results on the basis of value? What I mean is, do you want weighted colours? The higher the value the darker the colour etc.

'J

CR8.5 / CRXI - Discovering the impossible

 

[ErnstJan]

Oke hope it will be clear. If not let me know.

What I have is an VB.net application.
Part of the application is a rooster to see which person is on duty. That will be one person per day.

I have build is so that people can choice a color representing that person. For the color choice I us the color dialog. This means that all colors are possible.
These settings are kept in a SQL table.
Problem now is that the color is saved in a number and not split in RGB.
Ofcource in VB everything works fine due to cellformating and the ability to lookup the right color.

Now I want to build a report so the overview can be printed or converted to PDF to publish on a intranet

Example of color numbers as saved in the table:
-65536
-16776961
-32768
-1
-16744193

Hope that there is a solution to this otherwise I need to change the construction in the tables to hold RGB values.
And then I hope that Crystal Reports has the ability to look up the value.

Greetings,
Ernst Jan

 

[CR85user]

What format are these numbers in, as they are in the database currently?

When I saw 65536 I was initially reminded of XYZ space - 16 Bit rgb.

Which can be handled in Crystal quite easily as long as you don't mind conversion to 8 bit colours through division to bring in line with the 255 max value.

//{@Value}
replace({table.colvalue},'-','')

//{@Colour}
rgb({@value}/257,{@value}/257,{@value}/257)

But the higher values mean that this would not be correct.

Also the very low potential values mean that we would probably have to do some rounding to suit.

Let me know any more info you can and I am sure we can find a way around it.

'J

CR8.5 / CRXI - Discovering the impossible

 

[ErnstJan]

The current format in the database is float.
Had found a peace of code for VB.net to convert this into RGB

        Dim C As Integer = IIf(txtWaardeIn.Text = String.Empty, -1, txtWaardeIn.Text)

        Dim R As Integer = C And 255              ' Get lowest 8 bits  - Red
        Dim G As Integer = Int(C / 256) And 255   ' Get middle 8 bits  - Green
        Dim B As Integer = Int(C / 65536) And 255 ' Get highest 8 bits - Blue

        txtWaardeUit.Text = R & ";" & G & ";" & B
        txtWaardeUit.BackColor = Color.FromArgb(R, G, B)

But have not yet have time to put this code into a formula in Crystal Reports. So don't know yet if this could work.
This problem has to wait a week.

 

[CR85user]

No worries, When you come to add the formula just edit the background colour formula section with code similar to:

whileprintingrecords;
Numbervar Col := if abs({table.colourval}) in 0 to 16777215 then abs({table.colourval}) else 0
Numbervar R := Calc for R value;
//maybe something like R := Col / 65536;
Numbervar G := Calc for G value;
//maybe something like G := (Col - (R * 65536)) / 255;
Numbervar B := Calc for B value;
//maybe something like B := ((Col - (R * 65536)) - (G * 255));

Color(R,G,B)


I've not had a chance to play with this on screen so the values may need some tweaking.

'J

CR8.5 / CRXI - Discovering the impossible

 

[ErnstJan]

Thanks for looking in to this.
Really appricate it.

Can you maybe help me also with the second part of this problem.
My query has only the ID number of the person on duty.
Don't want to make a query with a lot of links.
Can you let the report look up a record in a table?
Ofcourse there will be a preformance issue.

Can you please advice me on this.

Thx in advance.
Ernst Jan

 

[ErnstJan]

Oke, Solved my problem by doing what i did not want to do.
Build a view with links to members table.
Also I changed the way I save the color. (Was in Argb).
Now I save the RGB as a string like 255128000.
First 3 = R, Middle 3 = G and last 3 = B.
Build the formula like this

Numbervar R := ToNumber(Left(CStr({getConsignatieRooster.Dag1}),3));
Numbervar G := ToNumber(Mid(CStr({getConsignatieRooster.Dag1}),4,3));
Numbervar B := ToNumber(Right(CStr({getConsignatieRooster.Dag1}),3));

Color(R,G,B)

And it works good.

Thanks for the help tho.

Greetings,
Ernst Jan

P.S. Have a Star for your efforts.