sql-serverreporting-servicesssrs-2008ssrs-2012sqlreportingservice

how to format the specified cell value in two different formats in SSRS Report

I have SSRS report data where we will have two different thing in some of the cells, For example: Old Values in SourceDB

FirstName   LastName 
Robin       Son
BOB         Alice

Updating the DB values:

FirstName   LastName 
Robin       S
BOB         A

After some update, we will have the changes and new & old values in Audit Table so the report will be created like this.

FirstName LastName
Robin     was: Son now: S
BOB       Was: Allice now: A

Is it possible to have the Was value in Red color and the now value in Green Color. In worst case if it is not possible how to make the whole cell value into red.

Thanks for the suggestions or answers or even feedback's are appreciated.

Solution

  • If you can't change your Audit table per Ashiko's very sensible recommendation to hold the Was and Now values in seperate columns, you will need to parse out the relevant parts of your string value in your report.

    One way you can do this is through placeholders that have expression based values. In your SSRS table, add 4 placeholders to a table that is based on your Audit dataset, with the following expressions as their Label and Value:

    1. Was: - ="Was: "
    2. Old Value - =replace(left(Fields!LastName.Value,instr(Fields!LastName.Value," Now:")-1),"Was: ","")
    3. Now: - ="Now: "
    4. New Value - =mid(Fields!LastName.Value,instrrev(Fields!LastName.Value,":")+2,99999)

    You can then format the individual placeholder items as you would normal text, with some one colour and others another.

    If you do this correctly, you should end up with something like this:

    Design View:

    Rendered:

    Do be aware though, that if you do resort to splitting your string values in this manner, if that pattern you are searching for (eg: Now: in the old value or : in your new value) you will get undesirable results in your report.

    If this is an entirely internal report for monitoring purposes, this is probably not an issue. If this will be customer facing however, I strongly recommend that you add additional columns to your Audit table for the Old and New value to allow you absolute control.