Home / Tech Tip / Crystal Tech Tip / Crystal Reports 2008 – Highlight Values in Crosstabs

Crystal Reports 2008 – Highlight Values in Crosstabs

Question: I would like to highlight values in my crosstab, with a different background color, by comparing the summarized value in the crosstab to other fields in my report, but the highlight expert and the format field/background options don’t seem to work correctly?

Solution in Crystal Reports XIR2:

Answer: You can create a formula that results in the word colors, you would like to highlight the summarized fields in the crosstab with, and then use that formula as a field in another crosstab, that will display on top of the original crosstab.

Solution in Crystal Reports 2008:

Answer: Instead of making 2 crosstabs, you only need to make one. And highlight the values in the crosstab using the new Crystal 2008 functions: GridValueAt, GetRowPathIndexOf, and CurrentRowIndex.

Our issue before was that we could not use the format field option in the crosstab, because we were placing this crosstab in a group header section. Therefore, in our comparison of database fields to the summarized value in the crosstab, we were only getting the first value from the details for each group. This will yield the wrong colors.

Below is the formula of the values we want to compare, but this formula only works in the detail section of our report.

Instead, we can put all the database fields (above) we want to compare to the summarized field (the data point value field above), in the crosstab, and use the new functions GridValueAt and GetRowPathIndexOf to compare the different values in the crosstab to that summarized field.

The new GridValueAt Crystal 2008 function returns the value of the summary at the specified location and the GetRowPathIndexOf function returns the absolute index of the path or -1 if the path cannot be found. So, using these 2 functions together, we can get the exact value of any item in the crosstab and compare it to the current value.

Steps:

  1. Create 1 crosstab using the value you want to summarize, along with the values you want to compare to. In this case, I am using a formula that results in a database field. Each of the other database fields we just do an average (or minimum or maximum would also work) to get the actual value that appears in the detail section.

Preview View:

Design View:

  1. Next, all we have to do is right-click on our main summarized field, which is the @Value field in this case and select the Format Field option and then Border tab and then background formula button.

  1. Now, our formula is going to look very similar to the one we use in the detail section of our report, except using the new crosstab functions in replacement of the database fields.
  2. First, we need to make sure we are accessing the values in the right row and applying the formula to the correct row. In order to do that, we can use the new function called: CurrentRowIndex in our formula. See below.

If CurrentRowIndex = 1 Then

So, we check first that we are in the right row, and if we are, then we will perform our comparisons between the value we have clicked on and the other values in the crosstab.

  1. Now, we can access the current value of the field we are on using the CurrentFieldValue function and then compare it to each of the rows in the crosstab using the GridValueAt and GetRowPathIndexOf to yield a different background color on the field. I will explain the formula below:

If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,1 ) Then
crRed
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,2 ) Then
crYellow
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,3 ) Then
crNoColor
Else
crGreen

The first IF statement states if the current value of the field we are on is greater than the value located in the row called: “Series 1”, column #1, and summary index of #1. So, if we look back at my crosstab, this is referring to the summary field right below the field I am on, which is the average of the HigherHigh database field.

  1. We can complete the formula by adding another overriding IF statement that looks at the next row in the crosstab and does the same type of comparisons, but results in different colors.
  2. Therefore, our final formula looks like this:

If CurrentRowIndex = 1 Then

If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,1 ) Then
crRed
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,2 ) Then
crYellow
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 1”),1 ,3 ) Then
crNoColor
Else
crGreen

Else If CurrentRowIndex = 2 Then

If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 2”),1 ,1 ) Then
crGreen
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 2”),1 ,2 ) Then
crNoColor
Else If CurrentFieldValue > GridValueAt (GetRowPathIndexOf (“Series 2”),1 ,3 ) Then
crYellow
Else
crRed
Else
crNoColor

  1. The result of this formula color codes my crosstab correctly.

  1. Now, all I need to do is make the other fields hidden. I can do this by right-clicking on the field and selecting Format Field and clicking on the Common tab and selecting Suppress. If you still don’t like the space in between the rows you can make the grid lines white and draw your own boxes.

About ckruger

Check Also

Let’s Speak BO Webinar: Design and Development Techniques for Mobilizing SAP Dashboards October 17, 2017

Webinar: Design and Development Techniques for Mobilizing SAP Dashboards

Join us in a fantastic and informative session with expert developer and Dashboards Consultant, Roxanne …

2 comments

  1. I’m really frustrated! In Crystal Reports v8 I want to be able to conditionally format…

    1) A crosstab summary value (crosstab A): if currentfieldvalue > the row’s total value (i.e. in the Total Column)

    2) A crosstab summary value (crosstab B): if currentfieldvalue > the crosstab’s grand total (i.e. in the Total Total cell)

    Help!

  2. Hi Anita Delre,

    Thanks for the question. A consultant will follow up with you shortly.

    -Analaura

Leave a Reply

Your email address will not be published. Required fields are marked *