SSRS change color of font based on non-numeric product

My report is attempting to track how many loads a day are being delivered to the customer by each driver.  We have two customers and want to make sure drivers are not just delivering to only one customers.  User wants only to see the total deliverys per driver in one column, but color coded.  So customer1 is "Red", customer2 is "Blue" delivered to both is "Green".  

Right now I am using a count function on the customername column.  I get the correct number of loads per driver for the day.  I am just unsure how to change the font color based on the name when both customer names are present.  IIF statements work for either or, but not both.  I tried a switch() but still could not get it to work.
LVL 5
rtayIT DirectorAsked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Try the following query:

with ShippersPerDriver as (
      select D.ENumber, COUNT(distinct M.Shipper) ShipperCNT
      from dbo.Malt M
      inner join dbo.Tbl_Driver D on M.Driver = D.ENumber
      group by D.ENumber
)
SELECT dbo.Malt.ShDate, dbo.Malt.FrtBill, dbo.Malt.Shipper, dbo.Tbl_Driver.LastName, dbo.Tbl_Driver.FirstName
      , SHP.ShipperCNT
FROM dbo.Malt
LEFT OUTER JOIN dbo.Tbl_Driver ON dbo.Malt.Driver = dbo.Tbl_Driver.ENumber
left outer join ShippersPerDriver SHP on SHP.ENumber = dbo.Tbl_Driver.ENumber

It adds an additional column, ShipperCNT, representing the number of shippers that the driver is delivering to.  In your situation it should be either one or two.

In the report you can then use that value in combination with the Shipper name to get your coloring right, something like:

=Switch(Fields!ShipperCNT.Value = 2, Green
  , Fields!ShipperCNT.Value = 1 and Fields!Shipper.Value = "Anheuser Busch", Red
  , Fields!ShipperCNT.Value = 1 and Fields!Shipper.Value = "Miller Coors", Blue)
0
 
SThayaTechnical MAnagerCommented:
you can create nested iif to achieve tyhis


can you please send your dataset sample data .... this will help us to sort your issue
0
 
ValentinoVBI ConsultantCommented:
Could you post the expression(s) that you've tried? Also the IIF statements that you've gotten to work.

In the meantime, perhaps the following explanation on how to use the Switch() helps?  See http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2279-Adding-an-Else-to-your-Switch.html
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
rtayIT DirectorAuthor Commented:
This is a little confusing I know.  Here is what I am trying to accomplish:

=Switch(Fields!Shipper.Value = "Anheuser Busch", "Red", Fields!Shipper.Value = "Miller Coors", "Blue", Fields!Shipper.Value = "Anheuser Busch" AND "Miller Coors", "Green", Fields!Shipper.Value =Nothing, "Black")

This above example does not work at all, just returns all font black.

This:

IIF(Fields!Shipper.Value = "Anheuser Busch" , "Red", "Blue") works to the extent that it colors font red or blue, but does not account for both.

Here is the problem.  The report only does a Count on shipper by driver by day.  So the Count for driver A may be three for 1/1/2012.  If he only delivers to one shipper, the color code is correct.  but if he delivers one 2 loads to shipper1 and One load to shipper2, the code selects the first shipper in the count and gives it that color.  Because each load record is in a seperate row, I don't know how to get "Anheuser Busch" AND "Miller Coors"  = Green Font.  Also, if the driver is off duty, shipper will be blank because count is 0 loads (no records).
0
 
ValentinoVBI ConsultantCommented:
So we're talking about beer then, cool, let's get started! ;)  (sorry, being from Belgium I couldn't resist the temptation)

Fields!Shipper.Value = "Anheuser Busch" AND "Miller Coors"

That will never work.  The reason for that is because you need to look at it at record level, and obviously Shipper can only have one value at that level.

I think the easiest way forward is to tackle this at query level.  Could you post your current query along with some sample data?
0
 
rtayIT DirectorAuthor Commented:
Yes, beer.  But I don't get free samples, unfortunatly.  

Here is the query.

SELECT     dbo.Malt.ShDate, dbo.Malt.FrtBill, dbo.Malt.Shipper, dbo.Tbl_Driver.LastName, dbo.Tbl_Driver.FirstName
FROM         dbo.Malt LEFT OUTER JOIN
                      dbo.Tbl_Driver ON dbo.Malt.Driver = dbo.Tbl_Driver.ENumber

Attached are tables and sample data.

thanks for your help.
Malt.xlsx
driver.xlsx
Malt.sql
Driver.sql
0
 
rtayIT DirectorAuthor Commented:
Thank you very much for this.  It is a great help.  

It is working fine, except that the distinct count on shipper is looking at the entire database rather than the date range parameter that filters the rest of the report.  For example if we are reporting between 02/12/2012 and 02/14/2012 the drive may have only shipped to one shipper during that date range.  the distinct count is still bringing up 2 because he delivered to the other shipper one time three months ago.  

Any ideas how to insure that the distinct shipper count is filtered by the rest of the reports paramaters?
0
 
rtayIT DirectorAuthor Commented:
Also, each day needs to count shipper.  2/12 can be red, 2/13 can be green.  right now it is the same color for all days for the driver.
0
 
rtayIT DirectorAuthor Commented:
I got it.  Thank you so much for your help.  Beers on me.
0
 
rtayIT DirectorAuthor Commented:
Follow up question.  Bellow is my query that works.  It only displays drivers that had loads in the time period.  Can you see a way to display every driver in the driver table even if they did not have loads in the time parameter?

WITH ShippersPerDriver AS (SELECT     D.ENumber, COUNT(DISTINCT M.Shipper) AS ShipperCNT, M.ShDate
                                                               FROM         Malt AS M INNER JOIN
                                                                                     Tbl_Driver AS D ON M.Driver = D.ENumber
                                                               GROUP BY D.ENumber, M.ShDate)
    SELECT     Malt.ShDate, Malt.FrtBill, Malt.Shipper, Tbl_Driver.LastName, Tbl_Driver.FirstName, SHP.ShipperCNT, SHP.ENumber, SHP.ShDate AS Expr1
     FROM         Malt RIGHT OUTER JOIN
                            Tbl_Driver ON Malt.Driver = Tbl_Driver.ENumber LEFT OUTER JOIN
                            ShippersPerDriver AS SHP ON Malt.Driver = SHP.ENumber AND Malt.ShDate = SHP.ShDate
     WHERE     (Malt.ShDate BETWEEN @StartDate AND @EndDate)
     ORDER BY Tbl_Driver.LastName
0
 
ValentinoVBI ConsultantCommented:
perhaps this?

WITH ShippersPerDriver AS (
      SELECT     D.ENumber, COUNT(DISTINCT M.Shipper) AS ShipperCNT, M.ShDate
      FROM         Malt AS M INNER JOIN
      Tbl_Driver AS D ON M.Driver = D.ENumber
      GROUP BY D.ENumber, M.ShDate
)
SELECT     Malt.ShDate, Malt.FrtBill, Malt.Shipper, Tbl_Driver.LastName, Tbl_Driver.FirstName, SHP.ShipperCNT, SHP.ENumber, SHP.ShDate AS Expr1
FROM Tbl_Driver
left outer join Malt ON Malt.Driver = Tbl_Driver.ENumber
LEFT OUTER JOIN ShippersPerDriver AS SHP ON Malt.Driver = SHP.ENumber AND Malt.ShDate = SHP.ShDate
WHERE     (Malt.ShDate BETWEEN @StartDate AND @EndDate)
ORDER BY Tbl_Driver.LastName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.