Create Bar Charts

Create bar charts, even includes X and Y labels!

create proc Hbar
@cmd varchar(255) , /* TSQL
SELECT statement 1st column
Character, 2nd column numeric */
@barsize
int=100 , /* Maximum size of bar */
@data_label bit=1, /* Label each bar with
value 1=yes */
@x_label varchar(25)=null,
@y_label
varchar(25)=null,
@title varchar(255)=null, /* Title for chart */
@barchar
char(1)=’*’ /* Character to use for bar */
as

SET NOCOUNT ON


Get x & y into table #t1

CREATE TABLE #t1 (x varchar(25),y
real)
INSERT INTO #t1 exec (@cmd)

— Format bars

DECLARE
@barsize2 varchar(3) Select

@barsize2=convert(varchar(8),@barsize)
DECLARE @max real,@i
varchar(12)
SELECT @max=max(y) from #t1
SELECT @i =
str(@barsize/@max,6,6)

CREATE TABLE #t2 (x varchar(25),bar varchar(255),y
real)
INSERT INTO #t2 EXEC ("SELECT right(replicate(‘
‘,25)+x,25),
Bar=convert(char("+ @barsize2
+"),
isnull(replicate(‘"[email protected]+"’,convert(int,y*" + @i
+")),”)),y
from #t1")

— Label bars if required

IF
@data_label =1
UPDATE #t2 SET bar=bar+’ (‘+convert(varchar(14),y)+
‘)’

— Print title

DECLARE @line varchar(255) ,@c int
SELECT
@c=((@barsize+50)-datalength(@title))/2
SELECT @line = REPLICATE(‘ ‘,@c) +
@TITLE
PRINT @line
SELECT @line = REPLICATE(‘ ‘,@c) + replicate

(‘=’,datalength(@title))
PRINT @line
PRINT ‘ ‘
SELECT
@line=right(REPLICATE(‘ ‘,25)[email protected]_label,25)
PRINT @line
PRINT ‘ ‘


print chart

DECLARE hb_cursor INSENSITIVE CURSOR
FOR SELECT
x,bar,y
FROM #t2

DECLARE @x varchar(25), @bar varchar(255) ,@y
real

OPEN hb_cursor
WHILE 1=1
BEGIN
FETCH NEXT FROM hb_cursor
INTO @x ,@bar ,@y
IF @@FETCH_STATUS <> 0
BREAK
EXEC (‘ PRINT
"’ + @x + ‘ | ‘ + @bar +’"’)
END
DEALLOCATE hb_cursor


Print y axis

DECLARE @Scale varchar(255) , @y_axis
varchar(255)
SELECT @scale = ‘ +’
SELECT @y_axis
= ‘ 0’

DECLARE @ten int SELECT
@[email protected]/(@barsize/10)

DECLARE @ten2 int SELECT @ten2 =
@ten

WHILE @ten2 < @[email protected]
BEGIN
SELECT @scale = @scale +
‘—-+—-+’
SELECT @y_axis = @y_axis + right (‘

‘+convert(varchar(5),@ten2),10)
SELECT @[email protected] + @ten
END
PRINT
@scale
PRINT @y_axis
PRINT ‘ ‘
SELECT @line = REPLICATE(‘ ‘,@c) +
@y_label
PRINT @line
PRINT ‘ ‘
go

/* TEST DATA */

exec
Hbar ‘select convert(varchar(5),spid),cpu from
master..sysprocesses where
cpu<>0′,
@barsize=100,
@data_label=0,
@title=’Processes by
CPU’,
@barchar=’#’,
@x_label=’SPID’,
@y_label=’CPU’

set nocount
on
declare @cmd varchar(255)
select @cmd="
select o.name,
size=sum(used * 2)
from sysindexes i, sysobjects o ,syscolumns c
where
i.id = o.id
and i.indid in (0, 1, 255)
and c.id = o.id
and o.name not
like ‘sys%’

GROUP BY o.name,o.id "

exec Hbar
@cmd,@barsize=100,@data_label=1,@title=’Tables by Size’

Latest Articles