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;
After all I came to rewriting the VBA function from here to SQL
It has the following parameters:
Result is a color code in chosen format
create function dbo.f_convertHSL (
@HueDegree numeric(3,0),
@Saturation numeric(6,3),
@Lightness numeric(6,3),
@Format varchar(3) )
returns varchar(100)
as
begin
declare @HuePercent numeric(6,3),
@Red numeric(6,3),
@Green numeric(6,3),
@Blue numeric(6,3),
@Temp1 numeric(6,3),
@Temp2 numeric(6,3),
@TempR numeric(6,3),
@TempG numeric(6,3),
@TempB numeric(6,3),
@Result varchar(100);
if @Saturation = 0
begin
select @Red = @Lightness * 255,
@Green = @Lightness * 255,
@Blue = @Lightness * 255;
if @Format = 'RGB'
select @Result = cast(cast(@Red as int) as varchar) + ', '
+ cast(cast(@Green as int) as varchar) + ', '
+ cast(cast(@Blue as int) as varchar);
else if @Format = 'HEX'
select @Result = '#' + convert(varchar(2), convert(varbinary(1), cast(@Red as int)), 2)
+ convert(varchar(2), convert(varbinary(1), cast(@Green as int)), 2)
+ convert(varchar(2), convert(varbinary(1), cast(@Blue as int)), 2);
else select @Result = 'Format should be RGB or HEX';
return @Result;
end;
if @Lightness < 0.5
select @Temp1 = @Lightness * (1 + @Saturation);
else
select @Temp1 = @Lightness + @Saturation - @Lightness * @Saturation;
select @Temp2 = 2 * @Lightness - @Temp1
, @HuePercent = @HueDegree / 360.0;
select @TempR = @HuePercent + 0.333
, @TempG = @HuePercent
, @TempB = @HuePercent - 0.333;
if @TempR < 0 select @TempR = @TempR + 1;
if @TempR > 1 select @TempR = @TempR - 1;
if @TempG < 0 select @TempG = @TempG + 1;
if @TempG > 1 select @TempG = @TempG - 1;
if @TempB < 0 select @TempB = @TempB + 1;
if @TempB > 1 select @TempB = @TempB - 1;
if @TempR * 6 < 1 select @Red = @Temp2 + (@Temp1 - @Temp2) * 6 * @TempR
else if @TempR * 2 < 1 select @Red = @Temp1
else if @TempR * 3 < 2 select @Red = @Temp2 + (@Temp1 - @Temp2) * (0.666 - @TempR) * 6
else select @Red = @Temp2;
if @TempG * 6 < 1 select @Green = @Temp2 + (@Temp1 - @Temp2) * 6 * @TempG
else if @TempG * 2 < 1 select @Green = @Temp1
else if @TempG * 3 < 2 select @Green = @Temp2 + (@Temp1 - @Temp2) * (0.666 - @TempG) * 6
else select @Green = @Temp2;
if @TempB * 6 < 1 select @Blue = @Temp2 + (@Temp1 - @Temp2) * 6 * @TempB
else if @TempB * 2 < 1 select @Blue = @Temp1
else if @TempB * 3 < 2 select @Blue = @Temp2 + (@Temp1 - @Temp2) * (0.666 - @TempB) * 6
else select @Blue = @Temp2;
select @Red = round(@Red * 255, 0),
@Green = round(@Green * 255, 0),
@Blue = round(@Blue * 255, 0);
if @Format = 'RGB'
select @Result = cast(cast(@Red as int) as varchar) + ', '
+ cast(cast(@Green as int) as varchar) + ', '
+ cast(cast(@Blue as int) as varchar);
else if @Format = 'HEX'
select @Result = '#' + convert(varchar(2), convert(varbinary(1), cast(@Red as int)), 2)
+ convert(varchar(2), convert(varbinary(1), cast(@Green as int)), 2)
+ convert(varchar(2), convert(varbinary(1), cast(@Blue as int)), 2);
else select @Result = 'Format should be RGB or HEX';
return @Result;
end;
Usage examples:
select dbo.f_convertHSL(24, 0.83, 0.74, 'RGB')
result: 244, 178, 134
select dbo.f_convertHSL(24, 0.83, 0.74, 'HEX')
result: #F4B286
Result can be confirmed for example here