Create Bar Charts

October 8, 2000

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('"+@barchar+"',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)+@x_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 @ten=@max/(@barsize/10)

DECLARE @ten2 int SELECT @ten2 = @ten

WHILE @ten2 < @max+@ten
BEGIN
SELECT @scale = @scale + '----+----+'
SELECT @y_axis = @y_axis + right ('
'+convert(varchar(5),@ten2),10)
SELECT @ten2=@ten2 + @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'