Conditional Formatting based on parameter value

Question

I have a report in Report Builder 3.0 which has 2 parameters, @LocalAuthority and @FiscalYear, neither of these are multi value.

The main basis of the report is a matrix which shows certain figures for Wards within a Local Authority

Ward              Fig A    Fig B    Fig C
Springfield        50        60      40
Grange Hill        20        60      90

I want to try and conditional format my figures based on the previous fiscal year. For example if the above table was showing Fiscal Year 2018/19 and the one below is showing Fiscal Year 2019/2020 I want the text to change colour depending whether there has been an increase or not.

Ward              Fig A    Fig B    Fig C
Springfield        60        60      40
Grange Hill        20        60      80

So Springfield Fig A has increased so I would like it to show red and Grange Hill Fig C has decreased so I would like it to show green.

Have tried this as an experiment

=iif(Fields!Fig A.value > Previous(Fields!Fiscal.value),"Yellow","Purple")

but it would seem you are unable to use 'Previous' in a matrix

Answer 1

I would suggest returning this year and last year data on the same row so that the report in Report Builder has the information it needs to display nicely. In this example, "LY" means "Last Year" and "TY" means "This Year". Report Builder can then choose "Yellow" or "Purple".

Ward         LY_Fig_A  LY_Fig_B  LY_Fig_C  TY_Fig_A  TY_Fig_B  TY_Fig_C
Springfield      50        60        40        60        60        40
Grange Hill      20        60        90        20        60        80