Comparing values in crosstabs

[Former Member]

<p>Hi,</p> <p>Example..</p> <p> Let&#39;s say my columns were Months, my rows were Departments and my data cells were expenditures. Now I want to colorize those expenditures based on average expenditures for the previous years... and my dataset already includes this data. So I&#39;d color the font red if that mont&#39;s expenditure is higher than its previous average, etc.</p> <p>Now, I understand the difficulities in correlating crosstab data. However, Crystal Reports allows me to put both my &#39;expenditure&#39; and &#39;previous average expenditure&#39; fields into a single crosstab cell (they are displayed stacked on top of each other)... so the correlation has already been made. When I put a conditional formula on one of those data fields, is there a way to access that other field in the same cell so that I can compare the values for formatting? </p> <p>The only options I can find for accessing other data within the crosstab are GridRowColumnValue and CurrentFieldValue.</p> <p>If my method is not possible, are there any other suggests for how to do this?</p> <p>By the way, this is just a simplified example... a &#39;manual&#39; crosstab is not an option.</p> <p>&nbsp;</p> <p>Thanks <br /></p>

[Former Member · Accepted solution]

GridRowColumnValue(row or column name) may come in handy here.  You may be able to use this function along with CurrentFieldValue and compare the values.Â

Hope that helps.Â

- Kathryn Webster (Report Design Consultant)

[Former Member · Accepted solution]

<p>Hi, thanks for the response... I&#39;ve thought about what you said for a while now... sorry, but I&#39;m just not seeing it. Could I get you to elaborate a little more?</p><p>As far as I can tell, GridRowColumnValue simply returns a scalar value of the current row or column value. I don&#39;t see any way I can navigate to another field to get its CurrentFieldValue. Am I missing something<br /> </p>

[Former Member · Accepted solution]

I can't figure out how to get this to work horizontally but I did find a solution if you were comparing to the value ABOVE it (vertically).Â

I think the reason why this is the case is that the crosstab is constructed (at print-time) columns and then rows.

Anyhow, the formatting formula that I tried is on the font color:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~_{</p><p align="left">currencyVar number;<br />StringVar HigherOrLower;</p><p>if number > currentfieldvalue then HigherOrLower := "red" else HigherOrLower := "green";<br />number := currentfieldvalue;</p><p>select higherorlower<br />case "red" : red<br />case "green" : green</p><p align="center">}~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Does anyone else have any ideas on how to get this comparison and formatting to be done horizontally???

- Kathryn Webster (Report Design Consultant)

[Former Member · Accepted solution]

Thanks again for the additional info. Sorry for being dense, but I just don&#39;t see where you&#39;re getting two different field values in that script. I&#39;ll just try it out and see what happens. Maybe then the light will come on ;-)<br />I&#39;ll report back here and let you know what I found out.

[Former Member · Accepted solution]

Hi,

I did work on a similar problem and got the solution by working around. Please find the solution below. I did save the values of first column in an array and used the values for comparing with the values of a second column. It is similar to X(I) >= currentMemberValue.

However, this worked when I wanted to highlight the second column (by that  time first column values are available). But having problem when I wanted to highligh the first column values (since the second column values are not available by the time the first column values are formatted). Any idea please ?

rgrds

Ramprasad

' approach :

' Find the Item names with changing_gears column value > 0 and store them in an array

'Â when checking Mountain_bikes column, refer to the grid and check if the same Item name

'Â is available in the grid.

'Â if it is available, it means, the Item is having a value of more than 1000 for changing_gears column.

'Â If the conditoin is satisfied, change font of the column.

global ItemsList(20) AS string 'to store descriptions of items having value greater than 1000

' In production environment the array dimension can be changed dynamically

' using "redim with preserve"

global IÂ AS NUMBER 'to store the latest index number

global j as number

dim k as number

GLOBAL INITIALIZED AS NUMBER

if i = 0 then ' initialise i only once.

i = 1

end if

if j = 0 then

j = 1

end if

IF INITIALIZED = 0 THEN

for k = 1 to 20

ItemsList(k) = "N"

INITIALIZED = 1

next

END IF

if GridRowColumnValue ("Measures, Level 0") = "Sum of changing_gears"Â then

if (CurrentFieldValue > 1000) then

ItemsList(I) = "Y"

end if

i = i + 1

end if

formula = defaultattribute

if GridRowColumnValue ("Measures, Level 0") = "Sum of Mountain_bike" then

if ( ItemsList(j) = "Y") and (CurrentFieldValue < 150 )then

formula = crRed

end if

j = j + 1Â

end if

[Former Member · Accepted solution]

Kathrun, you are very right: cross-tabs are created by columns. This causes big issue on multi-page cross-tabs with several rows, because there is no way to track hom may records were printed on the page. I guess, would be a great function to have, please pass this comment to Crystal Reports design team.

It is possible to compare 2 measure: create two global variables Measure1 and Measure2 on Suppress conditional level and use ratio ToText(Measure1/Measure2) in DisplayString place.

For integrated solutions, please see

http://www.relasoft.net/KB10001.html

Regards,

Alexander