Mark Items in different color if mismatch found.

  • Hello,

    Here is the SQL code and sample data from SSRS report.

    CREATE TABLE #TEMP
    (
    TID VARCHAR(100),
    FName VARCHAR(100),
    LName VARCHAR(100),
    LDate DATE
    )
    INSERT INTO #TEMP VALUES ('878A','Don','Robotic','01/01/2019')
    INSERT INTO #TEMP VALUES ('878A','Dan','Robotic','01/03/2019')
    INSERT INTO #TEMP VALUES ('878A','Dan','Robo','01/04/2019')

    --Data is used in SSRS Matix Report - Based on LDate column group -- here is sample data from SSRS report

    01/01/2019 01/03/2019 01/04/2019
    TID 878A 878A 878A
    FName Don Dan Dan
    LName Robotic Robotic Robo




    Well question is, need use color coding for mismatch/difference in values - Let me provide one example - Fname is different for 01/01/2019 and 01/03/2019 dates so these case need to highlight both of them in red color, and no need color for 01/04/2019 as value is same as previous value.  I have tried to use previous and current function in SSRS but it's not worked out due to previous function is overlapping in matrix . Is there any way we can achieve same in SQL?. Unfortunately table is having more than 200 columns,. It would be great something dynamic .  If any questions please let me know, any help much appreciated.

     

  • I think it would be easier to return the value of the name last time in the data set and then check that. So, in your SELECT statement add:

    LAG(FName,1,FNAME) OVER (ORDER BY LDate) AS PrevName

    Then, for your background/text/whatever colour you can use an expression like the below:

    =IIf(Fields!Fname.value = Fields!PrevName.Value, "LightGreen","Tomato")

    No idea if those are the colours you want, but you get the idea.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thanks Thom, I have thought of LAG function to use, it will work for sure. the only worry is need use same function 200 times as 200 columns in a table. It is nice to do in data-set/report level. I will wait sometime to see if we can get any answer related data-set else will use same code. Thank you again for response.

  • koti.raavi wrote:

    Thanks Thom, I have thought of LAG function to use, it will work for sure. the only worry is need use same function 200 times as 200 columns in a table. It is nice to do in data-set/report level. I will wait sometime to see if we can get any answer related data-set else will use same code. Thank you again for response.

    But your table only has 4 columns (TID, FName, LName and LDate); are we missing information here?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • If your data, as you are implying, will have more than 4 columns then I would reshape the data thus, obviously extending it to cover all the columns

    CREATE TABLE #TEMP
    (
    [NAME] VARCHAR(100),
    LDate DATE,
    [VALUE] VARCHAR(100)
    )
    INSERT INTO #TEMP VALUES ('TID','01/01/2019','878A');
    INSERT INTO #TEMP VALUES ('FName','01/01/2019','Don');
    INSERT INTO #TEMP VALUES ('LName','01/01/2019','Robotic');

    INSERT INTO #TEMP VALUES ('TID','01/03/2019','878A');
    INSERT INTO #TEMP VALUES ('FName','01/03/2019','Dan');
    INSERT INTO #TEMP VALUES ('LName','01/03/2019','Robotic');

    INSERT INTO #TEMP VALUES ('TID','01/04/2019','878A');
    INSERT INTO #TEMP VALUES ('FName','01/04/2019','Dan');
    INSERT INTO #TEMP VALUES ('LName','01/04/2019','Robo');

    add LAG and the colour coding as Thom suggested

    and feed it to a Matrix with row grouping on [NAME] and column grouping on LDate

    • This reply was modified 2 years, 5 months ago by  David Burrows.
    • This reply was modified 2 years, 5 months ago by  David Burrows.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows wrote:

    If your data, as you are implying, will have more than 4 columns then I would reshape the data thus, obviously extending it to cover all the columns

    You'll need to convert to a consistent datatype here as well. I doubt that every column will be a varchar here if the OP does have over 200 columns.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A wrote:

    David Burrows wrote:

    If your data, as you are implying, will have more than 4 columns then I would reshape the data thus, obviously extending it to cover all the columns

    You'll need to convert to a consistent datatype here as well. I doubt that every column will be a varchar here if the OP does have over 200 columns.

    True and good spot Thom 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 7 (of 7 total)

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