Show color code in straight table by using drill down in the Created Group

[vlakeshr]

Hi All,

I have shown  Drill down in straight table and giving color code at background. I have used below code to show the background color. It is working for VS and SVS but when I am goes to next drill down step to Objective in my Dimension  below formula is not giving out put as per requirement and color code is not working on that level.

My Dimension has drill down group as VS--> SVS--> Objective --> Key Result

Drill down where applying color code on background as [VS Reporting Current Status]--> [SVS Reporting Current Status]--->[Objective Reporting Current Status] 

Dimension Background code is for above Group

=if([VS Reporting Current Status]='On Track', RGB(78,156,45),if( [VS Reporting Current Status]='At Risk', RGB(255,179,25),if( [VS Reporting Current Status]='Will Not Achieve',RGB(168,0,11),

if( [SVS Reporting Current Status]='On Track',RGB(78,156,45),if( [SVS Reporting Current Status]='At Risk',RGB(255,179,25),if( [SVS Reporting Current Status]='Will Not Achieve',RGB(168,0,11),

if( [Objective Reporting Current Status]='On Track',RGB(78,156,45),if( [Objective Reporting Current Status]='At Risk',RGB(255,179,25),if( [Objective Reporting Current Status]='Will Not Achieve',RGB(168,0,11),

If( Status='On Track',RGB(78,156,45),if( Status='At Risk',RGB(255,179,25),if( Status='Will Not Achieve',RGB(168,0,11),if( Status='Achieved',RGB(27,125,156),if( Status='Not Started',RGB(255,255,255),

RGB(255,255,255)))))))))))))))

Anybody can help me to solve this issue or fix it. appreciate for quick response.

Note: I can not share the  data due to client security purpose. 

[vlakeshr]

can you please help me to solve this query.

[vlakeshr]

can you please help me to solve this query.

[marcus_sommer]

You are querying fields without any aggregation in your color-expression. Depending on the dimensions within your group there may multiple values available for the dimension-value. If this happens the result will be NULL and therefore invalid for a color.

This means you need a more specific querying and/or using an aggregation maybe concat() to determine a color - and I think the use of getcurrentfield() would be quite helpful to differ the expression between the various dimensions in your group.

- Marcus