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

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>

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |