Colour Formatting Based on Conditions

Question

I wan to format a report to look like so:

I have replicated the data like so:

 IF EXISTS (SELECT * FROM sys .objects
WHERE object_id = OBJECT_ID(N'dbo.CompareVal' )
AND type in (N'U'))
    DROP TABLE dbo.CompareVal;
CREATE TABLE dbo.CompareVal
(CompareValID  INT          Not Null 
, ValName      NVARCHAR(75) Null
, Vehicle      INT          Null
, Driver       INT          Null
);
GO

INSERT INTO CompareVal(CompareValID, ValName, Vehicle, Driver)
VALUES(1,'ABC Company Value',10, 15)

--SELECT * FROM #CompareVal

 IF EXISTS (SELECT * FROM sys .objects
WHERE object_id = OBJECT_ID(N'dbo.GroupStatistic' )
AND type in (N'U'))
    DROP TABLE dbo.GroupStatistic;
CREATE TABLE GroupStatistic
(StatisticID      INT          IDENTITY(1,1)
, CompanyName     NVARCHAR(75) Null
, [Description]   NVARCHAR(75) Null
, VehicleAvalible INT          Null
, DriverAvailable INT          Null
);

INSERT INTO GroupStatistic(CompanyName, [Description], VehicleAvalible, DriverAvailable)
VALUES ('APC and Sons', 'Something..', 5, 7)
     , ('ABC Camp limited', 'We deliver..', 15, 15)
  , ('Silas and Sons', 'Anything goes.', 10, 7)
  , ('Richard and Co', 'Helping you all the way.', Null, Null)
  , ('Kayla and Jane', '', 0, 3)
  , ('James and Jane', '', 1, 0)

--SELECT * FROM #GroupStatistic

SELECT CompanyName
     , [Description]
  , ISNULL(CAST(VehicleAvalible AS NVARCHAR(30)),'N/A') AS VehicleAvalible
  , ISNULL(CAST(DriverAvailable AS NVARCHAR(30)),'No Driver available') AS DriverAvailable
  , 0 AS TotalVehicles 
  , 0 AS TotalDrivers
FROM GroupStatistic

  UNION 

SELECT '' AS CompanyName
     , 'Group Value Standard' AS [Description]
  , CAST(Vehicle AS NVARCHAR(30)) AS VehicleAvalible
  , CAST(Driver AS NVARCHAR(30)) AS DriverAvailable
  , Vehicle
  , Driver
FROM CompareVal

And my expression for "VehicleAvailable" column is :

=Switch(Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Black"
   , Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Black"
   , Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Black"
   , Fields!Description.Value <> "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Gold"
   , Fields!Description.Value <> "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Red"
   , Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Blue"
   )

This doesn't work as I am comparing integer against text value. How do I format to get result like the image shown above?

Answer 1

I would add a column to the SELECT e.g. VehicleAvailableInt that would return a integer value - then use that in the Expression.

You could also rationalise your Switch code - it seems to me the first 3 cases could be replaced with just:

Fields!Description.Value = "Group Value Standard" , "Black"

Also your last Switch case will probably never be reached?