Conditional formatting with Reporting Services

  • Comments posted to this topic are about the item Conditional formatting with Reporting Services

  • Glad to see the Reporting Services articles. This is a good one. Never would've thought of creating the formatting info back in the sproc...

    Also nice use of the word "turgid." I had to look it up... πŸ™‚

  • An extra note, instead of typing in feild!col1.value you can use me.value and then apply the formatting to a lot of columns at once.

    For example if you have several currency columns that need to be red if negative just select them all and in the color expression type iif(me.value<0,”red”,”black”)

  • Thanks Mark - this sounds like a real timesaver.

    Adam

  • I was thrown off by the ";" with the first 'CASE' for the Commission Flag (I had not used CASE before). I have worked with Crystal Reports for years, even though the Report Builder is not as 'friendly' in a lot of ways as Crystal. It appears it is just as flexable so far.

  • Oops! Sorry about the typo - the ";" in the first snippet before CASE should, of course, be a comma!

  • If you despise the report designer as much as I do you could even take it a step further by returning the colour codes/names by the stored proc. This way you avoid those nasty IIF statements, and the logic is kept in one place:

    CASE

    WHEN SP.Bonus = @Topseller THEN '#0000FF' --Blue

    WHEN SP.Bonus = @Bottomseller THEN '#FF0000' --Red

    ELSE '#000000' --Black

    END AS TopBottomSellerColour

    ...or:

    CASE

    WHEN SP.Bonus = @Topseller THEN 'Blue'

    WHEN SP.Bonus = @Bottomseller THEN 'Red'

    ELSE 'Black'

    END AS TopBottomSellerColour

    The 'Colour' property of the textbox could then simply be set to:

    =Fields!TopBottomSellerColour.Value

    Chris

  • Another Option would be to use the "Custom Code" area in the report properties to create a routine that would return a colour based on dollar value.

    This way you do not have to burden the SP with this display-ish logic.

    regards

    Matteo

  • Nice article. I do a lot of BI and KPI type reports and prefer to use a SWITCH statement on a flag instead of IIF statement:

    =switch(Fields!mydata.Value = 1,"blue",

    Fields!mydata.Value = 2,"green",

    Fields!mydata.Value = 3,"orange",

    Fields!mydata.Value = 4,"red")

    Just an option I thought I'd share.

    Frederick (Fred) J. Stemp, Jr.
    Database Administrator / Database Developer
    Dealer Funding, LLC

    '...if they take my stapler then I'll set the building on fire...'

  • Can I have a conditional format of each "Person Name".For instant each person have got a set target, Then I want to format the "Sales YTD" to quickly see if the guys are achieving their target or not?

    Please assist guys

  • any ideas on how to format a cell when the cell in the previous row has a different value.

  • What are you looking to do specifically?

    If it's from a cell and you want just that value to change format on another cell in the Expression of the cell you want changed you can reference the other cell by using ReportItems!<cell name>.Value

    But to really help, if you could provide an example of what you are trying to do, I maybe able to get you where you want to go πŸ™‚

    Frederick (Fred) J. Stemp, Jr.
    Database Administrator / Database Developer
    Dealer Funding, LLC

    '...if they take my stapler then I'll set the building on fire...'

  • I have a Width column. I need to change the format of the cells in the Width column.

    I would like the font to be Dark Blue if the Width value in the cell is different than the Width value in the cell in the previous row.

  • Does the column's property allow it to auto grow? If not how are you setting the width property?

    Frederick (Fred) J. Stemp, Jr.
    Database Administrator / Database Developer
    Dealer Funding, LLC

    '...if they take my stapler then I'll set the building on fire...'

  • sorry, its confusing. The column value is for Width for a product. Like 12ft or 10ft or 8 ft. I want to change the font color propery of this.

Viewing 15 posts - 1 through 15 (of 16 total)

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