Solved

SSRS change color of font based on non-numeric product

Posted on 2012-03-14
11
564 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

14 Experts available now in Live!

Get 1:1 Help Now