dynamic formatting in a crosstab

[TJOLIVER]

Hi

We are developing a crosstab report in Crystal XI. We are not summarizing as such, just laying the data out in a tabular format. So, I have the result field as a Max value, as it only shows one for each cell in the crosstab.

My data looks something like this:

CREATE TABLE TABLE_A (
PRODUCT_ID NUMBER NOT NULL,
TEST_DESC VARCHAR2 (25),
RESULT NUMBER (4) NOT NULL,
NO_DEC_PLACES NUMBER (2) NOT NULL,
OUT_OF_SPEC VARCHAR2 (4) NOT NULL);


Insert into table_a values (1 , "A" , 210, 0, "YES");
Insert into table_a values (1 , "B" , 11.1, 1, "NO");
Insert into table_a values (1 , "C" , 10 , 0 , "NO");
Insert into table_a values (2 , "A" , 150, 0 , "NO");
Insert into table_a values (2 , "B" , 11.0, 1 , "NO");
Insert into table_a values (2 , "C" , 6 , 0 , "YES");

I want my crosstab to look like this, with the test_desc across the top and the Product IDs down the side :-

| A | B | C
--|-----|------|---
1 | 210 | 11.1 | 10
2 | 150 | 11.0 | 6

I want help with 2 things:

1) I would like to use the no_dec_places value to dynamically format the number of decimal places
2) I would like to use the out_of_spec value to dynamically set the background to a different colour (say CrRed) if value is "YES".

We've looked at GridRowColumnValue but can't get it to work.

If you manage to get it to work, posting an example rpt file would be fantastic. Good Luck!

 

[lbass]

The values you need to reference to do the formatting must be present in the crosstab, so you must add them as separate summaries. Your first summary should be a max of the Out of Spec field, followed by a max of the decimal field, and then your results field--because you have to pass the values of one summary to another using variables, as follows:

In preview mode, right click on the out of spec summary->format field->suppress->x+2 and enter:

whileprintingrecords;
stringvar os := currentfieldvalue;
true

Select the decimal field-right click->format field->suppress->x+2-> and enter:

whileprintingrecords;
numbervar dec := currentfieldvalue;
true

Then select all three summaries->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
stringvar os;
if os = "YES" then
crRed else
crNocolor

Select the Result summary->right click->format field->number tab->customize->decimals->x+2 and enter:

whileprintingrecords;
numbervar dec;

Also place this same formula in rounding->x+2.

Finally, you should minimize the height of the dec and os summaries in design mode, by grabbing the summary border and dragging it. You should be able to get the height to be very small. To ensure that color fills the cell, it is best to uncheck "show cell margins" in the customize style tab.

-LB