Search code examples
reporting-servicesssrs-2012

How to change color with mutiple iif

I have text box expression below, the result look like this:
Newtoy - Oldtoy - Notoy
I can get all text to become blue color, but I want the "dash sign" (-) become black color.
My expression is incorrect . Can you help to see what went wrong. Thank you. 

="font color = 'blue' size = '1'" 
& iif(First(Fields!ID.Value, "DataSet2") = " " ,"None", First(Fields!ID.Value, "DataSet2"))+ "/font",

= iif(First(Fields!ID.Value, "DataSet2") = "-" ,"black", "blue",

And this is the query populating the dataset:

SELECT STUFF((SELECT CAST(b.branch as varchar) +' '+ ' | ' +' ' 
FROM printers p 
full join branch b on p.branchid = b.branchid 
where p.printername is null 
order by b.branch FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),2,1,'');
Solution

  • Alright, so now that I see why the data is formatted as it is, I've found a solution that should work for you. Basically, to make this work, you'll need to modify the query from the dataset to the following:

    SELECT CAST(ISNULL(b.branch, 'None') as varchar)
FROM printers p 
FULL JOIN branch b ON p.branchid = b.branchid WHERE p.printername IS NULL 
ORDER BY b.branch

    This will provide the b.branch values in a way that they can be joined in SSRS with font formatting in between each value. It also accounts for NULL values with the ISNULL function which will check each ID and select NULL values as None when it returns the values.

    The expression should begin with the font color set to blue. Next, you'll need to use two SSRS functions, LookupSet will return all of the values of Fields!ID.Value in an array which we can put into one string with Join. Join requires values to join and a delimiter, which in this case is your chance to format the text color correctly. Delimiting by "</font><font color = 'black' size = 1>-</font><font color = 'blue' size = 1>" will close the first <font> tag which should make the first ID blue, inserts a black dash, and opens a new <font> tag for the next ID, and so on. Finally, you add a closing </font> tag to finish coloring the final ID in blue.

    ="<font color = 'blue' size = 1>" 
& Join(LookupSet(1, 1, Fields!ID.Value, "DataSet2"), "</font><font color = 'black' size = 1>-</font><font color = 'blue' size = 1>") & "</font>"

    I've tested this and it seems to work as it should. However, you'll need to be sure that you use this expression in a Placeholder with Markup type set to HTML to Interpret HTML tags as styles for this to work.

    Here's an example of how it looks when I use the expression above with a lighter color to demonstrate:

    example