SQL Server Techniques for Creating a Web Reporting Page — Part 3

By : Louis Duc Nguyen Jul 20, 2003

Printer friendly

This article uses the table we created in Part 1, called CUBE. Data is usually normalized into columns. For example, suppose we have a table of appointments. The table consists of 2 columns, DATE and APPOINTMENT. A common request is to display the data in a monthly calendar format. That is “pivot” the columns into a matrix. The matrix’s rows are the week number (52 weeks in a year) and the columns are the days in a week (Sun Mon…Sat). The monthly calendar is a simplified case of the pivot problem, as we know how many columns there are (7 days in a week) and we know their order (Sun Mon…Sat). Below we will go thru an example where we do not know either.

REQUIREMENTS

Previously, we displayed sales data in 3 columns: region, employee ID and sum of sales. Now our sales manager wants region in the Y vertical axis and employee ID in the X horizontal axis. That is one column for region and multiple columns for employee ID. Moreover, he doesn't want the X and Y axes to always be region and employee ID. In the web page, we provide 3 input select boxes. The first select box determines the X axis: region or employee ID. We pass this value into the @X parameter. The second select box determines the Y axis: region or employee ID. We pass this value into the @Y parameter. The third select box determines the sort order: ascending sales, descending sales, or default. We pass this value into the @orderby parameter. The code is listed below.

<Our Reporting Stored Procedure>

CREATE PROCEDURE CubePivotSP

(

@method varchar(50)='main'

,@startdate datetime='1900/1/1'

,@enddate datetime='2100/1/1'

,@group varchar(5000)=null

,@orderby varchar(5000)='region,empid'

,@X varchar(50)='empid'

,@Y varchar(50)='region'

)

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @temp varchar(8000)

if @method='drone' begin

  selectx=case @x when 'region' then region when 'empid' then empid end

   ,y=case @y when 'region' then region when 'empid' then empid end

   ,sumSales=sum(N)

  from (

  select

  region=case when patindex('%region%',@group)>0 then region else '' end

  ,empid=case when patindex('%empid%',@group)>0 then cast(empid as varchar)
else '' end

  ,n

  from cube

  where [date] between @startdate and @enddate

  ) as a

  group by region,empid

  order by case @orderby when 'desc'  then sum(N) end desc,case @orderby
when 'asc' then  
  sum(N) end,region,empid

return

end

if @method='main' begin

create table #X (X varchar(50),Y varchar(7000), sumSales int, I int identity(1,1))

insert into #X

exec CubePivotSP @method ='drone',@x=@x,@y=@y,@startdate=@startdate, @enddate=@enddate,@orderby=
@orderby,@group=@X   

create table #Y (X varchar(50),Y varchar(50), sumSales int, I int identity(1,1))

insert into #Y

exec CubePivotSP @method ='drone',@x=@x,@y=@y,@startdate=@startdate, @enddate=@enddate,@orderby=
@orderby,@group=@Y   

create table #R (X varchar(50),Y varchar(50), sumSales int, I int identity(1,1))

set @temp=@X + ',' + @Y

insert into #R

exec CubePivotSP @method ='drone',@x=@x,@y=@y, @startdate=@startdate, @enddate=@enddate,@orderby=@orderby,
@group=@temp 

set @temp=null

update #x set @temp = y = isnull(@temp,'') + ',_'+ x+'=max(case x.i when 
'+cast(i as varchar)+'
then r.sumSales end)'

select top 1 @temp= y from #x order by i desc

exec ('select '+@y+'=r.y'+@temp+' from    #R as r   join #X as x on r.x=x.x
join #Y as y on r.y=y.y group by r.y order by max(y.i)')

return

end

</Our Reporting Stored Procedure>