Solved

SSRS change color of font based on non-numeric product

Posted on 2012-03-14
11
563 Views
Last Modified: 2012-03-17
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.
0
Comment
Question by:rtay
  • 6
  • 4
11 Comments
 
LVL 11

Expert Comment

by:SThaya
ID: 37723603
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37723606
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
 
LVL 5

Author Comment

by:rtay
ID: 37726743
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37726819
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
 
LVL 5

Author Comment

by:rtay
ID: 37726889
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37728370
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
 
LVL 5

Author Comment

by:rtay
ID: 37730527
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
 
LVL 5

Author Comment

by:rtay
ID: 37730979
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
 
LVL 5

Author Closing Comment

by:rtay
ID: 37731074
I got it.  Thank you so much for your help.  Beers on me.
0
 
LVL 5

Author Comment

by:rtay
ID: 37731347
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37732348
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now