asked on

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.

SThaya

you can create nested iif to achieve tyhis

can you please send your dataset sample data .... this will help us to sort your issue

ValentinoV

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 https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2279-Adding-an-Else-to-your-Switch.html

rtay

ASKER

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).

ValentinoV

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?

rtay

ASKER

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

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rtay

ASKER

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?

rtay

ASKER

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.

rtay

ASKER

I got it. Thank you so much for your help. Beers on me.

rtay

ASKER

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

ValentinoV

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