October 4, 2013 at 12:33 pm
We want to be able to use t-sql to create a simple bar graph image file, to be inserted into an email. I know how to use sp_send_dbmail, but don't really know where to start for the bar graph.
So I guess there are maybe 3 different parts to this? - creating a graph, save the graph as image file, encode the image to insert inline into an email. Or let me know if you think I can skip from step 1 right to step 3.
Any links to good articles on how to do this? I'm wondering now if it'd be simpler to find out how to use Excel or Access VBA to create and encode the graph to an email...
October 4, 2013 at 12:44 pm
This sounds like maybe creating an SSRS report would accomplish all this quite simply.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2013 at 1:34 pm
Haven't used SSRS yet- I can't find it anywhere in our start menu- does that mean we don't have it installed? Where should it be found?
October 4, 2013 at 1:49 pm
ztoddw (10/4/2013)
Haven't used SSRS yet- I can't find it anywhere in our start menu- does that mean we don't have it installed? Where should it be found?
That doesn't mean you don't have it installed. In fact, it is installed on the machine where SQL is installed. Are you working on your local machine or against a server? There are a number of ways you can determine if SSRS is installed on your instance.
Here is one way.
select * from sys.databases where name = 'ReportServer'
If that return a row you almost certainly have SSRS installed (unless somebody else created a database with that name). You may or may not have SSRS.
Assuming you have it installed you will need to work through creating your first report. Here is a decent beginner's look at a "hello world" type of report.
http://technet.microsoft.com/en-us/library/ms167305.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2013 at 1:58 pm
Started reading the SSRS stairway articles- guess I would go through BIDS or have to download report builder- but not sure if I want to go that route or not... We already have an email programmed in T-SQL and we want to be able to just add a bar chart at the bottom of it- don't want to have to re-do what is already done in the existing email code. Would I have to do that? Or can SSRS just create a bar graph image that can somehow be encoded and inserted inline into the html code we have already?
October 4, 2013 at 2:18 pm
I just realized the bar graph being asked for might not be very meaningful anyhow, so I'm not sure if I need this after all, lol... but I'll keep ya posted- thanks a bunch!
October 4, 2013 at 6:10 pm
Ok, I still need to add a bar graph. So what's the simplest way to do this? Even if I had to re-do what I did in T-SQL in the SSRS, I might not even be able to... There is a table with derived totals on top and with specific formatting: highlighting back-ground colors of certain cells in certain colors based on their value being in the top or bottom third, or being greater than a specific hard-coded value, and highlighting a different column based on comparing it to yet another column.
So I'd rather not try to re-do all that and have to re-test it all again.
So is there some way to just create a simple bar graph and save it in an image file?
October 6, 2013 at 2:17 pm
A bar graph is a graphical way to display data.
A RDBMS (like SQL Server) is designed to efficiently store, manipulate and return data.
For graphical presentation you could use Reporting Services, Excel or an to of other tools.
"Highlighting Cells in certain colors" for sure is not done using plains T-SQL. It sounds more like HTML formatting added to SQL code.
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.
Other than that you could use an Excel sheet as a linked server and send the data to that Excel file. Perform the formatting (including the Graph) at the Excel file based on the data in the original sheet and send the Excel file via mail.
There may be a few other options but none of those will be "as clean" as the SSRS approach already mentioned. At least from my point of view...
October 7, 2013 at 12:37 pm
ztoddw (10/4/2013)
...So is there some way to just create a simple bar graph...?
If you really want "simple", try the following sample query which produces a horizontal bar graph using repeating text to "draw" the bars. Would be easy to include the output in email I would think. Can't graph negative values, however.
(The output)
Item Cnt BARGRAPH
---------- ----------- --------------------------------------------------
Beer 500 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Pizza 300 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Wings 200 xxxxxxxxxxxxxxxxxxxx
Snacks 100 xxxxxxxxxx
Salad 50 xxxxx
The basic query:
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
SELECT
Item,
Cnt,
BARGRAPH = REPLICATE('x',Cnt/10)
FROM @TEMP
ORDER BY
cnt DESC
Below is a slightly enhanced query that will scale the bars according to a maximum allowed value to be plotted.
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
DECLARE@XBAR AS VARCHAR(50)
,@ScaleFactor AS DECIMAL(10,3)
,@MaxCnt INT
,@MaxAllowed INT
SET @MaxCnt = (SELECT MAX(CNT) FROM @TEMP) -- get the highest value in the data set
SET @MaxAllowed = 50 /* Set the limit on highlest value to be plotted. If an individual data point exceeds MaxAllowed, then the bar graph will be scaled to fit the MaxAllowed. */
SET @ScaleFactor = (SELECT CAST(@MaxAllowed AS DECIMAL(10,3))/MAX(CNT) FROM @TEMP) --
SET @XBAR = REPLICATE('x',@MaxAllowed)
SELECT
Item,
Cnt,
BARGRAPH = CASE
WHEN @MaxCnt <@MaxAllowed
THEN SUBSTRING(@XBAR,1,CNT)
ELSE SUBSTRING(@XBAR,1, CAST((SELECT CNT * @ScaleFactor) AS INT))
END
FROM@Temp
ORDER BY
cnt DESC
SET NOCOUNT Off
--Pete
October 7, 2013 at 2:17 pm
peterzeke (10/7/2013)
ztoddw (10/4/2013)
...So is there some way to just create a simple bar graph...?
If you really want "simple", try the following sample query which produces a horizontal bar graph using repeating text to "draw" the bars. Would be easy to include the output in email I would think. Can't graph negative values, however.
...
If you want 'not so simple':-), you can draw a graph in SSMS using the following
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;
With XAxis AS (
SELECT Geometry::STGeomFromText('LINESTRING (0 0, ' + CAST((COUNT(*) + 1) * (MAX(cnt) / COUNT(*)) AS VARCHAR(10)) + ' 0)',0) X
FROM @Temp
),
YAxis AS (
SELECT Geometry::STGeomFromText('LINESTRING (0 0, 0 ' + CAST(MAX(cnt) * 1.1 AS VARCHAR(10)) + ')',0) Y
FROM @Temp
),
Bars AS (
SELECT Geometry::STGeomFromText('POLYGON ((' +
CAST((ROW_NUMBER() OVER (ORDER BY Item) - 1) * (MAX(cnt) OVER () / COUNT(*) OVER ()) AS VARCHAR(10)) + ' 0, ' +
CAST((ROW_NUMBER() OVER (ORDER BY Item)) * (MAX(cnt) OVER () / COUNT(*) OVER ()) AS VARCHAR(10)) + ' 0, ' +
CAST((ROW_NUMBER() OVER (ORDER BY Item)) * (MAX(cnt) OVER () / COUNT(*) OVER ()) AS VARCHAR(10)) + ' ' + CAST(cnt as varchar(10)) + ', ' +
CAST((ROW_NUMBER() OVER (ORDER BY Item) - 1) * (MAX(cnt) OVER () / COUNT(*) OVER ()) AS VARCHAR(10)) + ' ' + CAST(cnt as varchar(10)) + ', ' +
CAST((ROW_NUMBER() OVER (ORDER BY Item) - 1) * (MAX(cnt) OVER () / COUNT(*) OVER ()) AS VARCHAR(10)) + ' 0))',0) B
FROM @TEMP
)
SELECT X FROM XAxis
UNION ALL
SELECT Y FROM YAxis
UNION ALL
SELECT B From Bars
and have a look in the spatial results tab
October 7, 2013 at 3:25 pm
mickyT -- nice touch extending the food theme -- "tofu -200"... ha!:laugh:
October 7, 2013 at 3:56 pm
I really do like mickyT's approach!!!
It's definitely not "out-of-the-box".
The question is: How can the "graph format" be used together with sp_send_dbmail. Any thoughts?
October 7, 2013 at 4:31 pm
LutzM (10/7/2013)
I really do like mickyT's approach!!!It's definitely not "out-of-the-box".
The question is: How can the "graph format" be used together with sp_send_dbmail. Any thoughts?
Ah, one of the tricky ones:-). I suppose rather than putting it into geometries you could create a SVG and put that into the email. I might have a look into it.
October 7, 2013 at 4:56 pm
@mickyT: I just referred to the very first question of the OP:
We want to be able to use t-sql to create a simple bar graph image file, to be inserted into an email.
Actually, I need to "blame" you for being "responsible" for the reduced amount of time I'll have for my family for the next couple of days!!
I've never had the need to deal with geometry data. At least I thought so until I've seen your post. And now I have to look deeper into it - there's obviously much more than "just" changing the color of a U.S. state depending on some sales figures using SSRS.
Very interesting stuff you've posted! THANK YOU!
October 7, 2013 at 7:31 pm
Have fun ... and sorry:-D
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply