Crosstabs in Crystal Reports 9.0

[HeathHol]

Hi,

I am trying to find out how to highlight positions 1 - 3 [i.e. first place, second place and third place based on scores] within a crosstab. My crosstab records are in rows so I need to highlight all associated fields as opposed to just their score field - In the e.g below, the whole line for each participant.

e.g Position Name code Score
1 Jo JS2 400
2 Mike JS3 200
3 Lin JS4 100

Thank you - hope you can help.
HeathHol

 

[HeathHol]

Ok, maybe I should rephrase this for better understanding -

I am looking to highlight a whole row in a crosstab based on the criteria of one field within the row.

i.e. How do I use one value to highlight the entire row?

Can anyone help me? Pleeeez?

 

[lyanch]

I don't believe this is possible in a true crystal crosstab. You could easily do this in a manual crosstab.

Lisa

 

[lbass]

This is possible. First, in the crosstab expert, go to customize style, and highlight your row value "Position" and then note the entry in the box "Alias for Formulas". Let's say that it displays "table.position" (or you can change it to something shorter, if you like). This will become the value used in the formula below. In preview or design mode (i.e., not in the crosstab expert), select the summary field->format field->font->color->x+2 and enter:

if gridrowcolumnvalue("table.position") in [1,2,3] then crRed else crBlack

If you also want to highlight the row label for 1 - 3, then highlight the row label->format field->font->color->x+2 and enter:

if currentfieldvalue in ["1","2","3"] then crRed else crBlack

Note that in the second formula, even if the datatype of {table.position} is a number, it is a string when used as a label in the crosstab--so that the values in this formula must be entered as strings "1", etc., even though they are entered as numbers in the summary field formula.

-LB

 

[HeathHol]

Hi,

First of all, thank you for your help...

I have tried your suggestions and they work to highlight the position field [this can also be done via the highlighting expert] but as soon as I apply this to the next field i.e. Dealer Name there is no way to link the two fields. i.e. if position 1 is Frank's Dealership the position column label is highlighted but the name column is not because there is no way to state that the name col is linked to the position column.

I am beginning to wonder if this is unfortunately not possible. There seems to be no way of highlighting all of the fields in the first three rows.

HeathHol

 

[lbass]

Just noticed that your crosstab doesn't quite look like the usual one. Please explain what your row, column, and summary fields are.

-LB

 

[HeathHol]

Hi LB,

My crosstab is as follows -

Position DealerName DealerCode TotalScore

e.g 1 Frank's Dealership FR24 94
2 Bob's Dealership BO45 56
3 Jo's Dealership JO23 40
4 Warwick Dealership WA78 22

AVERAGE SCORE 53

I hope you can see what I mean....
The top 3 rows need to be highlighted i.e. the row is highlighted according to position.

 

[lbass]

I don't believe this is a crosstab report. It looks like you might have a group on dealer name and a topN on the total score, or that the total score is a field, not a summary, and it is sorted in descending order. If you want to highlight the top three records, then go to the section expert (format->section) and highlight the section these fields are in. If they are in a group section, highlight the group section->choose the color tab->background color->x+2 and enter:

if groupnumber in 1 to 3 then crYellow else crWhite

If they are in the detail section, highlight the details section->choose the color tab->background->x+2 and enter:

if recordnumber in 1 to 3 then crYellow else crWhite

-LB

 

[HeathHol]

By the by.....trust me....this is a crosstab - I should know seeing as though I designed the report.

Anyway - just to let u know I don't think it is possible to do this within a crosstab.

Take it easy
HeathHol.

 

[lbass]

This is possible--I have tested this. Please identify the row, column, and summary fields that you entered when you inserted the crosstab--it is not obvious--and we can go from there.

-LB

 

[HeathHol]

OK, Thanks LB...

Rows - PeriodSortOrder [groups according to quarter]
LeaguePosition [this gives position]
DealershipName
Code

Summarised Fields - Avg Score

There are no columns.

Do you need anything else??

Heath

 

[lbass]

Multiple row fields don't print like your display (in 9.0) in 8.0 (my version), but I can see that there is a problem with coloring labels. One solution might be to concatenate the three (or four) row fields and use this formula as your row field. You can then hardcode the labels to print in color by using a formula like:

if currentfieldvalue in ["1 Frank's Dealership FR24,"2 Bob's Dealership BO45","3 Jo's Dealership JO23"] then crYellow else crWhite

You would also have to then use these strings in formatting the grid cells, e.g., gridrowcolumnvalue("@concat") in ["1 Frank's Dealership FR24,"2 Bob's Dealership BO45","3 Jo's Dealership JO23"]

Another approach you might consider is to use a subreport instead of the crosstab (assuming you need the crosstab for a summary in a footer or something) where you can conditionally color a group section. Since you have no column field, you could easily create the same summaries in a regular report format (unless I'm missing important information here!).

-LB