SQL Server Reporting Services and Report Development

Question

I have four datasets, Plan, Forecast, Actual and SPLY per eight different product lines. I want to add conditional formatting of background color if actual is above or below Plan and/or Forecast. The report needs to export properly to Excel. Is there a way I can use one control rather than multiple text boxes to display the data, allow for conditional formatting and export to Excel? Thanks!

Environment:

SSRS 2010

SQL Server 2012

Answer 1

Great Question Lakeshore. I see two possibilities:

1. Join the datasets together in a single dataset so the results return Plan, Forecast, and Actual Values for each Product Line. Then you can simple set the background:

   =iif(Fields!Plan.Value > Fields!Actual.Value,"Pink","LightGreen")
   

2. Use the Lookup function to reference Values from a different Dataset based on the Product Line:

   =iif(Lookup(Fields!ProductLine.Value,Fields!ProductLine.Value,
   Fields!Plan.Value,"Plan") > Fields!Actual.Value,"Pink","LightGreen")
   

Answer 2

Join your data together in your SQL statement for the report so that you get one row with product, actual, plan, forecast in the same dataset. Then you can use an expression for the background color of the textbox that you want to conditionally format.

Here is an example:

=IIF(Fields!Actual.Value >= Fields!Plan.Value, "Green", "Red")

Also see this thread for more details: SSRS Field Expression to change the background color of the Cell

Or you could use a switch statement that would say if Actual is below both plan and forecast then make it red, if above or equal to both then make green, else (Actual assumed to be above one and below the other) make yellow:

=Switch(Fields!Actual.Value < Fields!Plan.Value and Fields!Actual.Value < Fields!Forecast.Value
  , "Red"
  , Fields!Actual.Value >= Fields!Plan.Value and Fields!Actual.Value >= Fields!Forecast.Value
  , "Green"
  , 1=1, "Yellow")