Background Color Pivot Expression Exclude Header

[denwo2010]

Hi,

I have a pivot and have the following for my Background Color

If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0)))

It works but it is Highlighting the Header which I do want how do I exclude this from happening?

Thanks

[#2]

Hi,

try to use the dimensionality( ) function in your if-clause like if (dimensionality ( )= 0 and .....)

Regards, Roland

[denwo2010]

Hi

What would the Syntax be for this now?

Thanks

[#4]

Hi.

The function dimensionality() returns the level for every line in your pivot or straight table. An easy way to check the return-value is an expression only with that function.

So add to every if-condition this function: If([SLA]<[SLA Target] and dimensionality()<> 0, RGB())... If(IsNull([SLA]) and dimensionality()<> 0, ....... should work as you want it.

RR

[denwo2010]

Hi, Does not seem to have worked header is still being Highlighted

[boorgura]

We have discussed this earlier.

And the only work around is to have the subtotal on the top and the including the condition:

if(rowno() <>0, <color_expression>)

[boorgura]

And please do let me know if there is a better and straight way to do it. 🙂

[denwo2010]

What I need to change the below to?

If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0)))

Thanks

[#9]

Hi again,

sorry, the cardinality() works NOT for pivot tables. Straight tables are ok. Sorry. So with the workaround from Rocky it should be:

If([SLA]<[SLA Target] and rowno() <>0, RGB(255,0,0),
 If(IsNull([SLA]) and rowno() <>0, RGB(214,231,247), RGB(0,255,0)))

RR

[boorgura]

After you add subtotals (that is check the partial sum option for the first dimension.

Change the expression as:

if(rowno()<>0,
If([SLA]<[SLA Target], RGB(255,0,0),
If(IsNull([SLA]), RGB(214,231,247), RGB(0,255,0))))

This should do...