Post reply

Conditional formatting (Bold) using another column's value or no value

  • June 16, 2014 at 1:46 pm

    #396919

    I have a multiple column table report using SSRS 2012. Need to change the Format_Title data field to Bold font when another field is blank/empty. The empty field could be either GL_Account, Company_Number or Debit_Credit columns.

    (i.e. if GL_Account is blank then for that same data row bold font the Format_Title data field).

    See attached image.

    Procedure:

    Right click on the Format_Title text box to bring up the Text Properties dialog box.

    Select Font option

    click on the Bold Style and check the checkbox

    Click on the Bold expression button (fx)

    Input a expression using one of the IIF statements below:

    =IIF(IsNothing(Fields!Debit_Credit.Value), "Default", "Bold")

    =IIF(Fields!Debit_Credit.Value <> " ", "Default", "Bold")

    =IIF(Fields!Debit_Credit.Value = " " , "Bold, "Default")

    =IIF(Fields!Debit_Credit.Value) = " ", (Fields!Format_Title = "Bold", (Fields!Format_Title = "Default")

    =IIF((Fields!Debit_Credit.Value) = " ", (Fields!Format_Title = "Bold", (Fields!Format_Title = "Default"))

    I have tried various combinations of the above expressions (only one expression at one time) and cannot get the Format_Title to bold if another specified field is blank.

    Additional question: I am not sure of the correct method to handle Nulls in this example

    There must be a way to conditionally format a field using another field's value.

    I would appreciate any assistance.

    Thanks.

  • June 17, 2014 at 1:30 am

    #1722152

    The following expression works:

    =iif(Fields!ColumnB.Value = "" Or IsNothing(Fields!ColumnB.Value),"Bold","Normal")

    I used the following data set:

    SELECT 'Test' AS ColumnA, 'Not Empty' As ColumnB

    UNION ALL

    SELECT 'Test2',NULL;

    In the second statement, I also used an empty string.

    Regarding the formulas you used:

    * the structure of IIF is the following: iif(boolean expression, true part, false part). So your first formula wrongly assigns default while it should be bold.

    * in all the other formulas, you compare against a space. This should be an empty string.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 17, 2014 at 2:48 pm

    #1722525

    I tried your expression but it did not work. I was able to discover a Format_Code field and added that column to the report along with the expression of: =IIF(Fields!Format_Code.Value = 1, "Bold", "Default") for the Bold style. This worked correctly.

    Now my problem is that I need to set the Left Padding to conditionally indent the Account Name rows if that Account Name is associated with the Format_Code = 0. I have tried several expressions and none seem to work.

    Example: IIF(Fields!Format_Code.Value = 0, + "5pt", "0pt")

    What I want is if Account Name field has a Format_Code = 0 then left pad the Account Name by x pt.

    Do you have any ideas on the the correct expression to use for this?

    Thanks.

  • June 17, 2014 at 3:06 pm

    #1722529

    The expression seems OK, except for the + sign. What is it doing there?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply