May 1, 2019 at 10:19 am
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.
May 1, 2019 at 10:43 am
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
May 1, 2019 at 11:39 am
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.
May 1, 2019 at 12:01 pm
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
May 1, 2019 at 12:42 pm
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
Far away is close at hand in the images of elsewhere.
Anon.
May 1, 2019 at 1:14 pm
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
May 1, 2019 at 2:07 pm
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
varcharhere 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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply