Formating single fields in across tab

[Kretzer]

Hi,

I try to format the detail fields of a cross tab via formula.
I try to say that - if the value of each field is below avalue it should turn red - if it is above a certain value the field shall turn green.

To test is I used the following furmula:
if {ZMR.val1 - Ist-Wert pro Monat} > 15 then
formula = crFuchsia
elseif {ZMR.val1 - Ist-Wert pro Monat} < 10 then
formula = crSilver
else
formula = crGreen
end if

But everything what happens is that ALL the fields turn the same color! I dont have a clue where the mistake is..

Thank you for your help.

Martin

 

[kenhamady]

right click in the cell (in preview)
select format field
Go to the Font tab and click the formula button next to color.

Use:

if CurrentFieldValue > 15
then crFuchsia
else
if CurrentFieldValue < 10...
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Kretzer]

Thank you very much
this was exactly what I was looking for.

But what can I do if the color of the field is not depending on it’s own value but on the value of a field a month ago?

In my report, if you`re refreshing the data (F5) you will be asked for which month an for which year the report should be done.

With these two parameter fields I select all data which matches the month and year selected.

I guess it’s possible to pick somehow the value from the month before out of the database...
But how?

Because I’m doing a cross tab - all my fields which should be colored are sum fields- in the database there are just the normal singel data - so there is no field I can compare it with.

I hope you understand my problem and maybe you know an answer..

Thank you.

Martin

 

[kenhamady]

Sorry, I don't understand.
Can you describe a specific example of which total you want to color and what record you used to determine the color? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Kretzer]

Well, let me try to explain it more precisely.
The Basis of the whole report are 300000 Data rows. For each row there is a field “description” a unique number of the row, a field “month”, a field “year” and a field containing the actual values for this item (row) and another field containing the plan value for this row.

What I do is a cross tab with all these data.
So that the description is on the left handside (the rows) and the columns are the busines regions. So the cross tab should tell the one who is looking at it the actual and the plan values for each different row (description) and depending on the different business region.

That means, in the Cross-Tab assistant I drag the actual and the plan field in the detail part of the cross tab and that’s why you will see a sum of each item in the cross tab.

That was just a small background information to understand the whole issue better.
In these 300000 data rows there are all values for each month for the whole year. But the report I shall create is a monthly report. So what I had to so is select all the data concerning to a special month. I solved this with 2 parameter fields. These parameter fields (month and year) are chosen by the user after updating the report (e.g. pressing F5). So the user can say I want a report for May, 2002 or for November 2001 ....
To select the correspondent data I included a selection on these two parameter fields which go like this - select all Data where the month is like the value of the parameter field and the year is like the year of the parameter field. After this selection you only have the date of this particular month left in the report.

So that works allright, i think.

The problem which I have still to solve is a problem in the Cross Table.
The detail fields in the cross table (actual and plan values) shall be colored depending on the value of the month before! That is the point. I need to compare the actual value displayed in the cross tab with the actual value a month before. That seems to me quite difficult as I only have the value of this one month in the report left.
The second thing is that the compared field from the cross tab is a sum field and this sum field does not exist in the database. So I cannot simply compare this cross-tab value with a value from the database! I would have to calculate a sum first!

What came to my mind to solve this problem is to group my report on the years and months. So that I have a group with all the years and a group with all the monts concernig to a year. After I have done that it should be possible to put the cross table in the group header of the month group, shouldn’t it???

That would mean (at least I think so) that for every month a year a cross tab woulb be created - so I would not need the parameter fields anymore, would I???

If this all would work, I had for every value a volue of the month before (except for the very first month).
But even if this would work - I don’t have a clue how to compare the two values...

I hope i could make myself clear and that you see my point....

Maybe you have an idea??

Thank you in advance
Martin

 

[kenhamady]

I have a rough idea of a remotely possible way, that would be fairly complex and may not be possible.

First you would have to have both months in memory. You could hide one month by grouping on month and putting the cross-tab in the Group footer. That way you could suppress the prior months cross-tab.

Doing the comparison would require an array of variables that list the prior month valeues.

What version of CR?
What are the row/column and summarized fields of your cross-tab?
Have you worked with Crystal variables? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Kretzer]

Allright:

- I work with crystal Version 8.5

- the cross tab looks like this

http://www.ginko.de/user/martin2/crosstab/MeinHTML.html

- the only variables I used are the Parameter fields to select the month and the year for the report.


I just recognized that I probably had an error in my thinking.
You can find the detail values which are shown in the report (actual and plan value) in the report itself.
Because in the view where all the data is coming from - there are already such groups like they are in my second column row (BRB, NRW, BAY I and so on) So the values which are shown there are also in the “normal” report - outside the cross-tab. The Cross tab is saying it is a sum field but because there is just one value this value equals the sum...
So when I wrote .... “ The second thing is that the compared field from the cross tab is a sum field and this sum field does not exist in the database. So I cannot simply compare this cross-tab value with a value from the database! I would have to calculate a sum first!...”

I did not recognize that the values ARE in the database.

I hope this did not confuse you to much...

Martin

 

[kenhamady]

This would be very complex. I am afraid that getting this to work would go beyond the level of support I provide in TT. You might want to consider getting an expert to look at the report. There are several experts here (you can tell by looking at our signatures) who are also available for rent. Please make contact outside of TT, since recruiting is not allowed here.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Kretzer]

Hi, one more hopefully last question.
I spoke with the person who created the view for this report and now I do have the field - actual value of last year also available.
The color of each field in the cross tab shall depend on following rules.

If actual value < value from the year before -> red
if actual value < plan value but better than the year before -> yellow
if actual value > or similar plan value -> green
My formula looks like this:

if currentFieldValue < {actual value per month} then crred
    else
        if (currentFieldValue < {plan value per month} and currentfieldvalue > {actual value from the year before}) then crYellow
        else 
            if  currentfieldvalue >= {plan value per month} then crGreen

The result in the cross table is that most of the fields turn green. To make is short - the fields are not colored the way they should be.

But.
If i use the same formular on the values outside of the cross-table then wverithing works fine. Most values turn green - some stay black - some red and some yellow.

I guess the reason is in the way crystal handles the values in a cross-tab.

Do you have an idea for a solution?

Thanks in advance for spending so much time with my problems...

Martin

 

[kenhamady]

Sorry, but it still won't work.

If you use any field from the report, then every cell in the cross-tab will be using {actual value per month} from the same record, which ever record is active at the time the Cross-tab is generated. (ie, if the xtab is in the RH, then all cells will use the first record of the report. )

No way for the cell to query a specific record. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com