CrossTab Questions

[Former Member]

I have a crosstab that looks something like this:

	Jan-08	Feb-08	Mar-08	Apr-08	May-08	Jun-08
Jan-08	A	B	C	D	E	F
Feb-08		G	H	I	J	K
Mar-08			L	M	N	O
Apr-08				P	Q	R
May-08					S	T
Jun-08						U

There are two things I need to be able to do with this crosstab. First, I need to be able to paint the diagonals in different colors. For example, AGLPSU needs to be red, BHMQT needs to be blue, CINR needs to be green, DJO needs to be orange, EK needs to be brown and F needs to be yellow. There is no specific rule for these colors, just that each object within a diagonal needs to be the same color and each diagonal line needs to be a different color. In otherwords, the highlighting of each object is based on location and not value.

The second thing I need is to present a version of this crosstab as a % where AGLPSU are all 100%, B is the % if G, C and H are % of L, DIM are each the % of P, etc.

I can't figure out how to do either of these, or if it is possible to do either of these. If not, the client will be forced to export to Excel which frustrates the purpose of the report.

Fuskie

Who hopes someone has a positive answer...

[former_member260594 · Accepted solution]

I'll give you part one while it is still fresh in my head and then work on part 2.

You will need to create 2 nested counter formulas in the crosstab.

For the first one right click on the summarized field > Format Field > For demo purposes put the counter in the display string (normally I would put it in the suppression formula and end it with false)

Numbervar x:= x + 1;

totext(x,0)

For create the second one in the column total field, again using display string;

Numbervar y:= y + 1;

numbervar x:=0

totext(y,0)

// this will reset the x counter for every column.

Format the background color of the summarized field with the following formula;

numbervar x;

numbervar y;

if x= y then crred

else if y= x + 1 then crblue

else if y= x + 2 then crgreen

else if y= x + 3 then color(255,128,0)

else if y= x + 4 then color(128,64,0)

else if y= x + 5 then cryellow

else crnocolor

K, can you provide some sample data for th esecond criteria to make it easier for me to wrap my head around it?

Edited by: Graham Cunningham on Jul 21, 2008 3:19 PM

[Former Member · Accepted solution]

Brilliant. I never would have thought of this. I had to put the column total formula into the Horizontal Alignment format property condition since the client wanted column totals suppressed and I extended the color selection to 13 (they run the report for 13 months), but it looks great. Now if only you could center a cross-tab horizontally on a page.

Here is a better discription of the second question. Take the following crosstab data:

	Jan-08	Feb-08	Mar-08	Apr-08	May-08	Jun-08
Jan-08	481	169	139	111	104	95
Feb-08		506	207	155	131	121
Mar-08			489	174	132	122
Apr-08				388	175	138
May-08					475	157
Jun-08						426

I want the second crosstab to look like this:

	Jan-08	Feb-08	Mar-08	Apr-08	May-08	Jun-08
Jan-08	100%	33%	28%	29%	22%	22%
Feb-08		100%	42%	40%	28%	28%
Mar-08			100%	45%	28%	29%
Apr-08				100%	37%	32%
May-08					100%	37%
Jun-08						100%

For February 08, 33% is 169/506. For March 08, 28% is 139/489 and 42% is 207/489, etc. Hope this is more clear.

Fuskie

Who is learning to think outside the crosstab...

[former_member260594 · Accepted solution]

As the 100% value is processed after the previous values I don't believe you wil be able to reference them at the time to divide by. So for February08 the 506 value won't have processed yet so you won't be able to divide 169 by it.

You could however generate the 100% values in a section above (or subreport above) and assign them to an array. Using the horizontal counter you could then divide the currentfieldvalue by array[counter] in the display string formula converting it to text, multiplying it by 100 and adding the percent sign

[Former Member · Accepted solution]

That was a great suggestion and it worked to a point. Here's what I did:

I initialized a numeric array ColumnBottom in Report Footer Section A with zero values. Then in the font color formula for each cell, I put in the following:

if y < x
    then ColumnBottom[x] := CurrentFieldValue;

This records to the array the bottom value of the column.

And in the second crosstab, I put in the following Display String:

if CurrentFieldValue <> 0
    then if ColumnBottom[y+1] <> 0
        then totext(((CurrentFieldValue / ColumnBottom[y+1])) * 100,0) + "%"

This works great... As long as both crosstabs are on the same page. For some reason, the number array is getting cleared back to zero values when the second crosstab is pushed to page 2 (I need one crosstab per page).

I have tried using WhilePrintingRecords but that didn't work (since I am through printing records by the time I get to the report footer), shared and global variable declarations but I can't seem to affect this behavior.

Fuskie

Who feels so close he can just about touch it...