Create a 5 star rating

  • 22 October 2020
  • 1 reply
  • 145 views

Userlevel 4
Badge +4

Goal

Show a 5 star rating in form and list.

This function creates a star rating up to 5 stars based on the used parameter

Example of stars in form
Example of stars in list

Solution

Create a subroutine that gets a numeric(2,1) as input and retuns a varbinary(max). This would look somelike this:

/* Drop function get_stars_rating first. */

if exists (select 1 from sysobjects
where name = 'get_stars_rating' and (type = 'FN' or type = 'TF' or type = 'IF'))
drop function get_stars_rating
go

create function get_stars_rating
(
@amount_of_stars numeric(2,1)
)
returns varbinary(max)
as
begin


--control_proc_id: function_get_stars_rating
--template_id: function_get_stars_rating
--prog_object_item_id: function_get_stars_rating
--template_description: Returns an icon with max 5 stars based on the amount of stars inputted

return cast('<?xml version="1.0" encoding="UTF-8"?>
<svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 120 24" width="120px" height="24px">
<defs>
<linearGradient id="star_1">
<stop offset="0%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 0, 0.0, 100 * (@amount_of_stars - 0)) as varchar(10)) + '%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 0, 0.0, 100 * (@amount_of_stars - 0)) as varchar(10)) + '%" stop-color="white"/>
<stop offset="100%" stop-color="white" />
</linearGradient>
<linearGradient id="star_2" x1="0" x2="100%" y1="0" y2="0">
<stop offset="0%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 1, 0.0, 100 * (@amount_of_stars - 1)) as varchar(10)) + '%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 1, 0.0, 100 * (@amount_of_stars - 1)) as varchar(10)) + '%" stop-color="white"/>
<stop offset="100%" stop-color="white" />
</linearGradient>
<linearGradient id="star_3" x1="0" x2="100%" y1="0" y2="0">
<stop offset="0%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 2, 0.0, 100 * (@amount_of_stars - 2)) as varchar(10)) + '%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 2, 0.0, 100 * (@amount_of_stars - 2)) as varchar(10)) + '%" stop-color="white"/>
<stop offset="100%" stop-color="white" />
</linearGradient>
<linearGradient id="star_4" x1="0" x2="100%" y1="0" y2="0">
<stop offset="0%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 3, 0.0, 100 * (@amount_of_stars - 3)) as varchar(10)) + '%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 3, 0.0, 100 * (@amount_of_stars - 3)) as varchar(10)) + '%" stop-color="white"/>
<stop offset="100%" stop-color="white" />
</linearGradient>
<linearGradient id="star_5" x1="0" x2="100%" y1="0" y2="0">
<stop offset="0%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 4, 0.0, 100 * (@amount_of_stars - 4)) as varchar(10)) + '%" stop-color="#FFD54F"/>
<stop offset="' + cast(iif(@amount_of_stars < 4, 0.0, 100 * (@amount_of_stars - 4)) as varchar(10)) + '%" stop-color="white"/>
<stop offset="100%" stop-color="white" />
</linearGradient>
</defs>


<path id="B" d="M21.983,8.916h-6.784c-0.204,0-0.386-0.13-0.453-0.323l-2.292-6.621c-0.149-0.43-0.758-0.43-0.907,0L9.254,8.594 C9.187,8.787,9.006,8.917,8.802,8.917L2.016,8.934c-0.458,0.001-0.654,0.581-0.291,0.86l5.392,4.145 c0.157,0.121,0.224,0.326,0.168,0.516l-1.941,6.576c-0.128,0.433,0.359,0.787,0.732,0.531l5.654-3.877 c0.164-0.112,0.379-0.112,0.543,0l5.654,3.877c0.372,0.255,0.859-0.098,0.732-0.532l-1.941-6.576 c-0.056-0.19,0.01-0.395,0.167-0.516l5.394-4.165C22.638,9.496,22.441,8.916,21.983,8.916z" stroke-width="1" stroke="black"/>

<use width="120px" height="24px" xlink:href="#B" fill="url(#star_1)"/>
<use width="120px" height="24px" xlink:href="#B" x="20%" fill="url(#star_2)"/>
<use width="120px" height="24px" xlink:href="#B" x="40%" fill="url(#star_3)"/>
<use width="120px" height="24px" xlink:href="#B" x="60%" fill="url(#star_4)"/>
<use width="120px" height="24px" xlink:href="#B" x="80%" fill="url(#star_5)"/>

</svg>'
as varbinary(max))

end
go

grant execute on get_stars_rating to public
go


Then add this to your form as a calculated field query or to a view.

dbo.get_stars_rating(coalesce(t1.amount_of_stars, 0.0))

I've used a yellow color. If you want to change the backgroundcolor you can find and replace #FFD54F with your preferred color. And replace “white” for the background. 

Rating with customized colors

 


1 reply

Userlevel 4
Badge +4

Btw, I also used the rating in the name of the icon, so when you click the icon it will show ‘3_1.svg’.

Reply