Compare currentfield values in Crosstab or alternative solutions?

[starsky39]

Hi,
Does anyone have any ideas on the below?

I have a Crosstab with a Date range as the column headings. The date is filtered to show Fridays only within a date range (@backuptime). The rows are the names of servers being backed up {emm_image.clientname} The field values are summarised data volumes of data being backed up @size. So my crosstab looks like this:

14/1/12 21/1/12 28/1/12
clienta 100 130 80
clientb 100 40 41
clientc 200 200 150
Total 400 360 280

I need to be able to highlight the summarised values in red if they go up more than 20% and green if they go down more than 20%. Anything within 20% stays black.

So in the example above for clienta 100 would be black, 130 would be red and 80 would be green. For client b 100 would be black 40 would be green and 41 would be black. For clientc 200 would be black, 200 would be black and 150 would be green.

I have tried for format field > font > color > x2 the following formula:
if ({@size}\ previous ({@size})) \ 100 < 80 then crred and so on, but this won't work as it's using the @size value.

I can do a basic forumla of
If currentfieldvalue < 100 then crred
else black

This will change the colour of all fields under 100, but I cannot find how to compare the currentfieldvalue to the previous (column) value.

I then tried to do a manual cross tab, but could not get that to work either!

Any ideas? I would be very grateful!! Thank you.

 

[crystalkiwibruce]

What version of Crystal are you using? In 2008/2011 you have the Crosstab functions that let you create embedded summaries and calculated members.

As your headings are date dependent you could also achieve what you want by using am manual crosstab: A formula for each date heading and another for the value for that column. Then all the summaries are accessible using simple conditional formatting.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[starsky39]

Hi,
Thanks for the reply. I am using Crystal Reports XI. I created the crosstab like above, but I don't know how to write the formula that calculates the percentage increase or decrease and then change the font colour.

Sorry to sound dim, but I also don't know how to create embedded summaries or calculated members like you have suggested. My use of Crystal is very limited so if you could expand that would be great.

Thanks.

 

[lbass]

The solution in thread767-1674420 would work for this, you just have one less column.

If you always run the report for a limited number of weeks, a manual crosstab might be easier, however.

-LB

 

[starsky39]

Thanks lbass. However, there can be up to 300 clients listed. Does that mean I would have to manually type in 300 entries under the 'whileprintingrecords' section?

The report will not always run for a set number of weeks, it could be any number of week from 4 up to the last year.

 

[lbass]

Yes. Probably doesn't make sense.

-LB