I have a mutli drill down report containing groups. One group is assessments and the data that displays is assessment date, KPI. KPI can be either a R, G, Y. What I'm trying to do is evaluate the last record of assessment date (last one submitted) and if the KPI is a R then change the fill/background color of a box in the page header to red, do the same if Y, yellow, G, green.
Do I create a formula and drop in it the section and do I use onlastrecord or var. An example would be great.
Thank you in advance.
I tried writing formula using onlastrecord but didn't get very far
Create a formula that concatenates the date and the KPI into a delimited string. For example: ToText({assessment date}, 'yyyyMMdd') + "||" + {KPI}
In the conditional formatting expression, get the maximum value of the concatenated string, split it to an array (using the Split() function and the '||' delimiter), and grab the 2nd element in the array. That gives you the KPI in the last assessment date.