Home / Tech Tip / Crystal Tech Tip / Crystal Reports XI R2 – Highlight Values in Crosstabs

Crystal Reports XI R2 – 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?

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

In this example, because you want to compare the summarized value in the crosstab to several different database fields, and the crosstab needs to appear in the group header, your only option is to create 2 crosstabs.

Let’s first explore why the other highlighting expert and format background color options will not work for this scenario.

Option 1 – Why can’t we use the highlight expert?

First, we cannot use the highlight expert in this scenario, because we want to compare values in 2 or more database fields. The highlight expert can only compare 1 database field to a fixed value you type in. See below.

Option 2 – Format Field / Background Option

We also cannot use the Format Field option in the crosstab, because we are placing this crosstab in a group header section. Therefore, in our comparison of database fields to the summarized value in the crosstab, we will only get the first value from the details for each group. This will yield the wrong colors.

Notice that all cells are highlighted in red when we apply the above formula.

Since this formatting formula appears in the crosstab, that has been placed in the group header, when Crystal processes this formula, it is only taking the first value for the database fields {Choise_3.HigherHigh}, {Choice3.Low} and {Choice_3.LowerLow} and comparing it to the current value in the crosstab field. We need it to compare the current value in the crosstab to the matching month’s database field.

Option 3 – Create 2 crosstabs – 1 with the values to summarize and one with the highlighting colors and overlay them.

Step 1: Create the formula, that results in the colors you would like to appear in the crosstab, to highlight certain values.

In this example, I named my formula Color:

All this formula is doing is comparing two values in my data set, and if one is higher than the other, it displays a word for the color.

Step 2: Create your cross tab.

In this example, I will be taking the average of the {Choise_3.L1 Data Point Value} field by Series for each month in my crosstab.

Step 3: Now, I will make another crosstab that uses my above @Color formula. I will use the same fields for my Rows and Columns, as I did in the first crosstab, but the summarized field will be the maximum of the @Color formula.

Step 4: Now the trick will be to get rid of all the borders on the second Max of @Color crosstab.

In this example, I just made all grid lines white.

Step 5: Since we need to make the 2 crosstabs appear as 1 crosstab, you will need to line them up on top of one another. This is best done when you work in the Preview mode.

Once you have lined up the two crosstabs on top of one another it looks as though you have 1 crosstab with the numbers highlighted according to certain business rules! Congratulations you are done!!!

 

About ckruger

Check Also

Let's Speak BO Webinar Now You See Me, Now You Don’t! August 8 2017

Webinar: Now You See Me, Now You Don’t!

Join us and Creative Technology & Training Solutions’ Michael Ward, as he demonstrates and discusses …

Leave a Reply

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