Hi Peter,
Supposing you've set up two parameters named paramRed
and paramGreen that will accept Integer value between
1 and an upper limit of 99 and the textbox name for the
percentage calculation is txtRevenuePct, then this (hopefully)
should do the trick:
In txtRevenuePct's Background Color property, type this expression:
Iif ( [Your formula below] <= Parameters!paramRed.Value / 100, "Red",
Iif ( [Your formula below] >= Parameters!paramRed.Value / 100, "Green",
"Amber" ) )
Slight modification to your formula:
=Iif ( Sum ( Fields!resp_tot_plan_amount.Value ) <> 0 ,
( Sum ( Fields!resp_tot_act_amount.Value ) -
Sum ( Fields!resp_tot_plan_amount.Value ) ) /
Sum ( Fields!resp_tot_plan_amount.Value ) , 0 )
GeoSynch
[quoted text, click to view] "Peter Nolan" <peter@peternolan.com> wrote in message
news:1126969268.668899.12560@g47g2000cwa.googlegroups.com...
> Hi All,
> I think there must be a better way....and someone here must know it...
> ;-)
>
> I am building reports to do revenue variance from plan analysis.
>
> I have the forumula below working to produce the amount and protect
> from the divide by zero error. However, I also want to set the colour
> of the background of the cell for both the amount and the percentage
> variance....
>
> Initially, I'd like to set it for -5% or worse= red, -5% - +5% = amber
> and +5% and above green....
>
> And thinking about it...it would actually be really good to have the
> percentage as a parameter on thre reports that defaults to 5% to change
> the colours....
>
> But it looks like I must repeat this forumla in each case...or must I?
>
> Has anyone else done this kind of colour coding for revenuye variance?
>
> Thanks
>
> Peter
>
www.peternolan.com >
> PS. This is the forumla I mentioned...
>
> =IIf(sum ( Fields!resp_tot_plan_amount.Value )= 0 , 0 , (sum(
> Fields!resp_tot_act_amount.Value ) - sum (
> Fields!resp_tot_plan_amount.Value ))/ IIf(sum (
> Fields!resp_tot_plan_amount.Value )= 0 , 1 , (sum (
> Fields!resp_tot_plan_amount.Value ))) )
>
