t-sql to create simple bar graph image file to be inserted into email

Question

t-sql to create simple bar graph image file to be inserted into email

Viewing 4 posts - 16 through 18 (of 18 total)

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

ztoddw Ten Centuries Points: 1189 More actions · Answer 1

LutzM (10/6/2013)
If you know how to use html code to design a bar graph (e.g. [/url] or [/url]), you could expand the html code already in use.

This looks simple- I'll do this. Thanks!

mickyT SSChampion Points: 10360 More actions · Answer 2

Hi

I can't test this in an email yet (so it may not work) and the rendering of the graph would be dependent on the email client looking at the message.

The following will build an svg (scalable vector graphic) excerpt that could be inserted into you email's HTML body. It looks rather ugly, but can be changed to suit you needs. Hope it helps.

SET NOCOUNT ON

DECLARE @TEMP TABLE (ITEM VARCHAR(10),CNT INT)

INSERT @Temp (Item, Cnt)

SELECT'Beer',500

UNION ALL

SELECT'Pizza',300

UNION ALL

SELECT'Wings',200

UNION ALL

SELECT'Snacks',100

UNION ALL

SELECT'Salad',50

UNION ALL

SELECT 'Tofu',-200;

DECLARE @width INT = 700;

DECLARE @height INT = 550;

DECLARE @emailbody VARCHAR(MAX);

With Scales AS ( -- work out some parameters

SELECT (@height / 1.1) / (MAX(cnt) - ((MIN(cnt) - ABS(MIN(cnt))) / 2.0)) YScale,

((MIN(cnt) - ABS(MIN(cnt))) / 2.0) YOffset,

@width / (COUNT(*) + 1.0) XWidth

FROM @TEMP

),

XAxis AS ( -- draws the x axis

SELECT '<line x1="0" y1="' + CAST(@height + (YOffset * YScale) AS VARCHAR(30)) + '" x2="' + CAST(@width AS VARCHAR(30)) + '" y2="' + CAST(@height + (YOffset * YScale) AS VARCHAR(30)) + '" style="stroke:rgb(0,0,0);stroke-width:2"/>' X

FROM Scales

),

YAxis AS ( --draws the y axis

SELECT '<line x1="1" y1="0" x2="1" y2="' + CAST(@height AS VARCHAR(30)) + '" style="stroke:rgb(0,0,0);stroke-width:2"/>' Y

FROM Scales

), --additional code could be added to add ticks etc

Bars AS ( --Draw the bars

SELECT '<rect width="' + CAST(XWidth as varchar(30)) +

'" height="' + CAST(abs(cnt) * YScale as varchar(30)) +

'" x="' + CAST(((ROW_NUMBER() OVER (ORDER BY Item) - 1) * XWidth) + 1 as varchar(30)) +

'" y="' + CAST(@height - ((((cnt + abs(cnt)) / 2) - yOffset) * YScale) AS VARCHAR(30)) +

'" style="fill:yellow;stroke:red;stroke-width:2"/>' +

'<text x="' + CAST((((ROW_NUMBER() OVER (ORDER BY Item) - 1) * XWidth) + 1) + (xWidth / 2.0) as varchar(30)) +

'" y="' + CAST(@height + (yOffset * YScale) + 10 as varchar(30)) +

'" fill="black" transform="rotate(60 ' + CAST((((ROW_NUMBER() OVER (ORDER BY Item) - 1) * XWidth) + 1) + (xWidth / 2.0) as varchar(30)) + ',' + CAST(@height + (yOffset * YScale) as varchar(30)) + ')" style="font-family:Arial;font-size:16">' + item + '</text>'

B

FROM @TEMP t

CROSS APPLY (SELECT * FROM Scales) s

)

-- put it all together

SELECT @emailbody = '<svg xmlns="http://www.w3.org/2000/svg" version="1.1">' + CAST(A AS VARCHAR(MAX)) + '</svg>' FROM (

SELECT CAST(T as XML)

FROM (

SELECT B T From Bars

UNION ALL

SELECT X T FROM XAxis

UNION ALL

SELECT Y T FROM YAxis

) SVG

FOR XML PATH('')

) A (A);

-- Emailing stuff here

print @emailbody;

You can try the resulting svg here

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Crud. This looks very interesting. I've been playing with SVG generation in T-SQL and this looks a whole lot easier than what I've been doing.

I say "crud" because I found this good thead the night before Thanksgiving day and I just know it's going to occupy some time this weekend. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mickyT SSChampion Points: 10360 More actions · Answer 4

Have fun with it Jeff ... but don't let it consume your Thanks Giving weekend. Re-reading this thread, I might have to have another look at this.