Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dynamic Shading of cell based on a value in another cell (row number)

Answered Dynamic Shading of cell based on a value in another cell (row number)

  • Wednesday, November 21, 2012 8:04 PM
     
     
    Sign In to Vote
    0

    I would like to shade(gray) the values in # Store BOM based on when the rownumber is less then the rownumber of the current month.

     So the result would be Month Feb-Oct would be gray while Nov-Jan would remain white.

     

All Replies

  • Wednesday, November 21, 2012 8:49 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi There

    Thanks for your posting. Can you please let us know what type of report you are using? Are you using Matrix report? I mean How you are displaying the information the line above # Store BOM .

    What does the rownumber line displaying?

    Please update

    Many thanks

    Syed

     
  • Wednesday, November 21, 2012 8:58 PM
     
     
    Sign In to Vote
    0

    yes is a matrix report.

    rownumber = the result of using the RowNumber() on the group. So it will always be 1 to 12.

     
  • Wednesday, November 21, 2012 9:16 PM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    Hi There

    Thanks for your posting. Let say you have column group with month so you can write expression for the textbox # Store BOM fill colour like this

    =iif(Fields!month.Value>1,"SeaGreen","Gray")

    If you have any question please let me know.

    A screenshot for your help

    Many thanks

    Syed Qazafi Anjum

    • Proposed As Answer by Sanjeewan Kumar Thursday, November 22, 2012 3:09 PM
    •  
     
  • Wednesday, November 21, 2012 9:26 PM
     
     
    Sign In to Vote
    0

    Hello,

    This does not solve my issue as your the "1" in your "iif(Fields!month.Value>1" is constant. I need the shading to occur dynamically based on the current month No. Becasue these are fiscal months and cant use date math as Jan which is the last fiscal month < the current calendar month of Nov. This is way im trying to use a rownumber in some way.



    • Edited by fountainhead7 Wednesday, November 21, 2012 9:28 PM
    •  
     
  • Thursday, November 22, 2012 8:43 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Hello,

    What's the data type of the “Month” field? If it is Date type, then please refer to the following expression:

    =IIF(DateDiff(“m”,First(Fields!Month.Value), Fields!Month.Value)>=0 and DateDiff(“m”,Fields!Month.Value, now())<0),"Gray","White”)

    =IIF(DateDiff(“m”,First(Fields!Month.Value), Fields!Month.Value)>=0 and DateDiff(“m”,Fields!Month.Value, now())<0),"Gray","White")


    Regards,
    Fanny Liu

    Fanny Liu

    TechNet Community Support

     
  • Thursday, November 22, 2012 3:04 PM
     
     Answered
    Sign In to Vote
    0

    Hello Fanny,

    No its not its just text.

    Thats ok I codes the logic in the dataset.

    Thanks to everyone for responding.

    • Marked As Answer by fountainhead7 Thursday, November 22, 2012 3:05 PM
    •