Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Conditional formating based on two diferent fields values

Answered Conditional formating based on two diferent fields values

  • Thursday, March 21, 2013 12:52 AM
     
     
    Sign In to Vote
    0

    Hello,

    I have created a report on Report  Builder 3.0 that has a structure similar to the following example

    Event ID,    Event Date,     Participant ID,   Event Class ....

    1728          01/24/2012       1701                A23

                      01/25/2012       1042                A23

                      01/26/2012       1903                R15

    2015          03/18/2012       2380                Z10

                      03/19/2012       2489                X44

                      03/20/2012       3012                Z10

    In this query, the transactions are grouped by Event ID. An event could last many days and have many participants with unique Participant IDs. However, the one constant is that all transactions for the same event must be coded with the same Event Class. In our case, the Event Class code given to the first instance of an Event Date should dictate what the Event Class code should be coded as for the remaining transactions under the same Event ID.

    In the above example (Event ID 1728) the last Event Class is different from the first (or earliest) entry under that same Event ID. The same is true for the second transaction on Event ID 2015.

    I would like to know how to write an expression in the field color value that will create a conditional formatting to turn the Event Class red if any transaction posted after the first one for that event has a different Event Class value. In this example, both R15 and X44 are anomalies that should appear in red for us to correct them in the database.

    Thank you so much for your assistance.

    Regards,

    Manuel

     

All Replies

  • Wednesday, March 20, 2013 2:52 AM
     
     
    Sign In to Vote
    0

    Hello,

    I have created a report on Report  Builder 3.0 that has a structure similar to the following example

    Event ID,    Event Date,     Participant ID,   Event Class ....

    1728          01/24/2012       1701                A23

                      01/25/2012       1042                A23

                      01/26/2012       1903                R15

    2015          03/18/2012       2380                Z10

                      03/19/2012       2489                X44

                      03/20/2012       3012                Z10

    In this query, the transactions are grouped by Event ID. An event could last many days and have many participants with unique Participant IDs. However, the one constant is that all transactions for the same event must be coded with the same Event Class. In our case, the Event Class code given to the first instance of an Event Date should dictate what the Event Class code should be coded as for the remaining transactions under the same Event ID.

    In the above example (Event ID 1728) the last Event Class is different from the first (or earliest) entry under that same Event ID. The same is true for the second transaction on Event ID 2015.

    I would like to know how to write an expression in the field color value that will create a conditional formatting to turn the Event Class red if any transaction posted after the first one for that event has a different Event Class value. In this example, both R15 and X44 are anomalies that should appear in red for us to correct them in the database.

    Thank you so much for your assistance.

    Regards,

                    

     
  • Thursday, March 21, 2013 3:17 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hi Data,

    Click the EventClass text-box in the report, and then use the expression to set its Color property in the Properties window.
    =iif(fields!EventClass.Value=first(fields!EventClass.Value,"EventID"),"Black","Red")

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,

    Charlie Liao
    TechNet Community Support

     
  • Thursday, March 21, 2013 4:08 AM
     
     
    Sign In to Vote
    0


    Try

    = IIF( Previous(Fields!EventClass.Value) <> Fields!EventClass.Value, "RED", "BLACK")

    And if you want above condition only to be applied after first row of your group then try

    = IIF( (Previous(Fields!EventClass.Value) <> Fields!EventClass.Value) AND RowNumber("EventID") > 1 , "RED", "BLACK")

     
  • Thursday, March 21, 2013 4:31 AM
     
     
    Sign In to Vote
    0

    Hi,

    Charlie has answered your question in your previous forum.. 

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e29ecf53-23cd-4bb1-b267-4df7b619fd89

    I think that is the best solution.

    Thanks,

    Rana

     
  • Thursday, March 21, 2013 5:28 AM
     
     
    Sign In to Vote
    0
    Charlie thank you so much for providing me with such diligent and timely response. I truly appreciate your effort. It is perfect. Regards, Manuel
     
  • Thursday, March 21, 2013 3:24 PM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    Would you please help me understand the following error message that I received. If it helps, I am using a Table to display the query result in which only the Amount field, which is currency is set to show a total. The remaining fields are regular fields with no subtotal values.

    Thank you so much once again!

    "The Color expression for the text box ‘EventClass' has a scope parameter that is not valid for an aggregate function.  The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."

    Regards,

    Manuel

     
  • Friday, March 22, 2013 1:48 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Data,

    This error message means that the scope of the aggreagte function in your expression is not correct. This issue maybe caused by that there are two tablix in your report. And each tablix has a row group, so the the scope of the aggreagte function of the expression in the tablix should be the corresponding one in that tablix.

    If the issue psersists, please post the expression and structure of the tablix using a screenshot, so that we can make further analysis.

    Regards,

    Charlie Liao
    TechNet Community Support

     
  • Saturday, March 23, 2013 12:41 AM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    I created a new tablix and it worked great. Thanks!

    Manuel