The problem started when I needed to apply conditional formatting to a table with smooth color changes in MS SQL Server Reporting Services (SSRS). It is impossible with standard SSRS functionality. But you can use the table data to smoothly change the color with the Lightness parameter in the HSL color model.
The question is, how to convert HSL to usable in SSRS HEX or RGB color codes using SQL.
No answers were found at Stackoverflow or anywhere else, only for other programming languages
I've based the following solution on this article. As mentioned, I use 2 functions here, and I also return a dataset in both (3 columns for RGB, 1 for the hex):
CREATE OR ALTER FUNCTION dbo.HSLtoRGB (@H numeric(3,0),@S numeric(4,3), @L numeric(4,3))
RETURNS table
AS RETURN
SELECT CONVERT(tinyint,ROUND((RGB1.R1+m.m)*255,0)) AS R,
CONVERT(tinyint,ROUND((RGB1.G1+m.m)*255,0)) AS G,
CONVERT(tinyint,ROUND((RGB1.B1+m.m)*255,0)) AS B
FROM (VALUES(@H, @S, @L))HSL(Hue,Saturation,Lightness)
CROSS APPLY(VALUES((1-ABS((2*HSL.Lightness - 1))) * HSL.Saturation)) C(Chroma)
CROSS APPLY(VALUES(HSL.Hue/60,C.Chroma * (1 - ABS((HSL.Hue/60) % 2 - 1))))H([H`],X)
CROSS APPLY(SELECT TOP (1) * --It's unlikely there would be 2 rows, but just incase limit to 1
FROM (VALUES(C.Chroma,H.X,0,0,1),
(H.X,C.Chroma,0,1,2),
(0,C.Chroma,H.X,2,3),
(0,H.X,C.Chroma,3,4),
(H.X,0,C.Chroma,4,5),
(C.Chroma,0,H.X,5,6))V(R1,G1,B1,S,E)
WHERE V.S <= H.[H`] AND H.[H`] <= V.E
ORDER BY V.E DESC) RGB1
CROSS APPLY (VALUES(HSL.Lightness - (C.Chroma / 2)))m(m);
GO
CREATE OR ALTER FUNCTION dbo.HSLtoRGB_HEX (@H numeric(3,0),@S numeric(4,3), @L numeric(4,3))
RETURNS table
AS RETURN
SELECT CONVERT(binary(3),CONCAT(CONVERT(varchar(2),CONVERT(binary(1),CONVERT(tinyint,ROUND((RGB1.R1+m.m)*255,0))),2),
CONVERT(varchar(2),CONVERT(binary(1),CONVERT(tinyint,ROUND((RGB1.G1+m.m)*255,0))),2),
CONVERT(varchar(2),CONVERT(binary(1),CONVERT(tinyint,ROUND((RGB1.B1+m.m)*255,0))),2)),2) AS RGB
FROM (VALUES(@H, @S, @L))HSL(Hue,Saturation,Lightness)
CROSS APPLY(VALUES((1-ABS((2*HSL.Lightness - 1))) * HSL.Saturation)) C(Chroma)
CROSS APPLY(VALUES(HSL.Hue/60,C.Chroma * (1 - ABS((HSL.Hue/60) % 2 - 1))))H([H`],X)
CROSS APPLY(SELECT TOP(1) * --It's unlikely there would be 2 rows, but just incase limit to 1
FROM (VALUES(C.Chroma,H.X,0,0,1),
(H.X,C.Chroma,0,1,2),
(0,C.Chroma,H.X,2,3),
(0,H.X,C.Chroma,3,4),
(H.X,0,C.Chroma,4,5),
(C.Chroma,0,H.X,5,6))V(R1,G1,B1,S,E)
WHERE V.S <= H.[H`] AND H.[H`] <= V.E
ORDER BY V.E DESC) RGB1
CROSS APPLY (VALUES(HSL.Lightness - (C.Chroma / 2)))m(m);
GO
SELECT *
FROM (VALUES(210,.79,.3),
(24,.83,.74),
(360,1,1),
(0,0,0))V(H,S,L)
CROSS APPLY dbo.HSLtoRGB(V.H, V.S, V.L) RGB
CROSS APPLY dbo.HSLtoRGB_Hex(V.H, V.S, V.L) RGBhex;