Skip to Content

CrossTab Questions

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.


Who hopes someone has a positive answer...

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jul 21, 2008 at 10:15 PM

    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;


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

    Numbervar y:= y + 1;

    numbervar x:=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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.


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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.