I'm currently setting the background color of a chart through an expression (on the 'expressions' tab) like this, and it works:
=if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) >=.95, rgb(0,255,0),
if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag) < .95 AND
Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) >=.85), Yellow(),
if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) < 85, rgb(252,0,0),
White())))
My problem:
I have a *bunch* of charts where I'm repeatedly using this same expression, or some close variation.
What I'd like to accomplish:
I'd like to encapsulate this code in a function/macro for reusability and maintainability. I'd like to create a function that accepts the chart (or object id, whatever) as a parameter, and then does something like this (my best attempt):
function ZSetBackgroundColor(parmObjectName)
{
try {
// How to do this? I want this to be parmObjectName instead, but
// was trying it out hard-coded for proof-of-concept purposes here:
var sumOfField = ActiveDocument.GetField('M6').getSelectedValues(); //This doesn't work
var x = sum(sumOfField); //?
var countOfField = Count(ActiveDocument.GetField('M6').MyFieldToCount); // How to do this?
var valueToCalc = sumOfField / countOfField;
var retColor = White();
switch (valueToCalc) {
case valueToCalc >= 95:
retColor = Green();
case (valueToCalc >= 85) && (valueToCalc <=94):
retColor = Red();
case valueToCalc <= 84:
retColor = Yellow();
default:
retColor = White();
}
return retColor;
} catch (err) {
alert(err);
}
}
And from the chart's expression, I want to do this:
=ZSetBackgroundColor(theChartObjectNameOrId_or_Whatever)
Other Notes:
Trying to do something like...
var x = Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag);
...within the macro doesn't work. It always gives me a syntax error that ":" is missing. But I'm thinking this isn't the right way to do it, anyway.
Any info would be greatly appreciated.
Thanks!
