Color code cells in a column on a pivottable - can it be done ?

[v-jacko]

I am after something that seems a reasonable thing to want to do in a
pivot table:

Color code all cells in a column to a specific color - e.g

Good Bad
A 2 1
B 10 7

So everything in Good column should be green and everything in Bad
should be red

Good and bad (& A and B) are from a dimension in the OLAP cube at the
backend

Searched this group and the help but the only color coding I can find
depends on the value itself

 

[Alvin Bruney]

That should be easy to do and there are a number of ways to do it. For
instance, you can use a numberformat to color the cells appropriately based
on the value inside the cells. You combine this with an iterative approach
that examines each field in your specified axis. For instance, this is code
posted in here that i've modified to iterate the axis with an appropriate
numberformat

foreach (Excel.PivotFieldSet fldSet in
this.suPivotTable.ActiveView.FieldSets)
{
foreach(Excel.PivotField f in fldSet.Fields)
{
this.suPivotTable.ActiveView.FieldSets[fldSet].Fields[f].NumberFormat
= "[Green]#,##0;[Red](#,##0)";
}
}


--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006

 

[v-jacko]

Alvin,
thanks for the response, but either I am missing something and/or
missed a point and/or that doesn't cut it - I am never sure if I am
using the right terminilogy, but I should explain that 'Good' and 'Bad'
are Unique vlaues within a dimension - call it "Rating", so the pivot
really looks like the below - I only have a single dimension called
rating on the column axis

Rating
Good Bad
Count Count
A 5 3
B 1 10

If I correctly interpret you resonse and try it via just changing the
number format for the detail (count) field, all the numbers turn green
- again, I am not looking to color code on the value - I am looking to
color code on the unique value of a dimension - 5 & 1 should be green
because they are in the good column and 3 and 10 should be red because
they are in the bad column (and ideally I am looking to change to
background color, not the text color - I have looked and looked at this
but am concluding it can't be done in a pivot table