crosstab help

[Former Member]

Hi All,

I have a cross tab report having columns as brand and rows as location.

For every  Brand I have summary fields like sales,cost,variance.

It is like this

                                 Brand1                                        Brand2                                 Brand3

Location 1         sales        Cost       Var                 sales      Cost      Var            sales     Cost       Var   

Now I need change the background color of   sales        Cost       Var    headers  alternately.

Please help

[abhilash_kumar · Accepted solution]

Hi Divya,

Please have a look at this thread:

If you're only trying to change the background color of the summary fields, do you want sales, cost and var for Brand 1 to be one color and then for Brand 2 another color?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I need to change only background color of headers like below.

yellow then green.  again yellow then green

[abhilash_kumar · Accepted solution]

Click the first summary field under 'Brand 1' > Press ctrl and highlight the next two summary fields so that all three summary fields are selected at once > Right-click on one of the highlighted fields > Format objects > Border tab > Click on the formula button beside 'Background' and use this formula:

If Remainder (CurrentColumnIndex, 2) <> 0

then crYellow

else crGreen

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I need highlight only column headers like actsales,bpsales.....

PFA

[raghavendra_hullur · Accepted solution]

Hi,

In case you haven't found the solution yet, you can try this as an alternative:

Since the labels in Cross Tab cannot be referenced with GridValue function or mod function,

1. Navigate to Crosstab Expert --> Customize Style and uncheck the checkbox for "Show Labels" if it's checked to remove the existing labels and display the label only from the newly created formula as in step 2.

2. Create a formula with your label content.

3. Use that as second column(after your database field for Brand).

4. Right click on the newly added column (formula) and navigate to Format Field --> Border and write below formula content under  X+2 formula button for Background.

numbervar d; 

d := iif(d=1,0,1); 

iif(d=0,crYellow, crLime)

If you want to change the color, you can try with color() (E.g: color(255, 255, 255)) function and include the RGB values as required.

You may have to adjust the formula content as per your Crosstab columns width.

Thanks,

Raghavendra

[abhilash_kumar · Accepted solution]

Great Idea, Raghav!

Just to elaborate on what Raghav means:

1) Create a formula with this code:

WhileReadingRecords;

"Act sls    Bp sls     Var%    Act mgr       Bp mgr        Var%"

2) Add this as the second 'column' to the crosstab

3) Uncheck 'Show Labels' and then follow step 4 from Raghav's post.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash and Ragha,

As you suggested I added the formula as 3rd column(year,week,Header)  in my cross tab but the data is repeating .

Please help

[abhilash_kumar · Accepted solution]

Go to the Customize Style tab > Under 'Columns', choose the 'Week ID' column and select 'Suppress Subtotal'. Do the same for the 'Year' column.

-Abhilash