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

How CubeSP Works

First we have three session settings “SET NOCOUNT ON”, “SET ANSI_WARNINGS OFF”, & “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”. The first two settings are for aesthetics. “SET NOCOUNT ON” tells the stored procedure not to print how many rows were returned. SET ANSI_WARNINGS OFF tells the stored procedure not to print a warning that nulls were excluded from group by operations. “READ UNCOMMITTED” is important. It tells the stored procedure to perform dirty reads, preventing shared read locks. As we typically update the cube only once a day –- it doesn’t make sense to have shared locks. Shared locks would only make a web user wait, until the previous web user finishes performing his query.

Let’s go through the parameters:

@region. Possible values are: All, North, East, South, & West.

@empid. Possible values are: All, 1, 2, 12, 21… Note the @empid is varchar while the empid column is an INT.

@startdate, @enddate. Possible values are any datetime value.

@method is my attempt to add Object Oriented programming to Transact-SQL. For example, suppose we want not only to sum sales, but also to group sales. We can create another stored procedure. Or we can simply add another “method”. Notice each method code block has a “return” command. This tells the stored procedure to exit at that point.

LIKE Operator. The sales manager wants to be able to select “West” region or select ‘All’ regions. We can accomplish this by using dynamic web scripting, dynamic SQL, or by the LIKE operator. The LIKE operator is straightforward to use and is standard SQL. For example, we can code the WHERE statement as “where region like @region”. If @region=”west”, this is interpreted as “where region like ‘west’”. If @region=”%”, this is interpreted as “where region like ‘%’” which returns all rows. To use the LIKE operator, we need to reset @region to “%” when it is “ALL”. We do that by “select @region=case when @region='all' then '%' else @region end”. Finally, note that the @empid variable is varchar, but the empid column is an integer. The statement “where empid like @empid” does an implicit conversion of empid into varchar then compares it to @empid. I have had cubes w/ over 10 million rows and rely on this implicit conversion w/ little adverse affect. If you’re worried about it, you can always convert all INT to varchar as you populate the cube.

Here are some results:

exec CubeSp @region ='west' -- returns 64

exec CubeSp @region ='all' -- returns 138

exec CubeSp @empid =’21’ -- returns 4

exec CubeSp @empid ='all' -- returns 138

The sales manager can now search the cube by region and empid. Now he wants to group the results. That is he wants a breakdown by region and/or empid and filter the data at the same time. We alter our CubeSp and add a new method code block “groupSales”. In our web page, we implement the group functionality by input checkboxes.

<Our input checkboxes>

<input type="Checkbox" value="Region" name="group">Region

<input type="Checkbox" value="Empid" name="group">EmpID

</Our input checkboxes>

For example, he would check Region and EmpID, if he wants to group by both. Because the input checkboxes have the same name, there is only one “Group” variable and its value is “Region,EmpID”. If he only checked region, the value would be “Region”. Note that in html, if no box was checked, there would be no Group variable –- it is undefined. In that case, use web scripting to default the value to “All”. We then pass the value to the @Group parameter of our altered stored procedure.

<Our Reporting Stored Procedure>

ALTER PROCEDURE CubeSP

(

@method varchar(50)='groupSales' --:sumSales, groupSales

,@region varchar(50)='all'

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

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

,@empid varchar(50)='all'

,@group varchar(5000)=null

)

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select @region=case when @region='all' then '%' else @region end

select @empid=case when @empid='all' then '%' else @empid end



if @method='sumSales' begin

select sumSales=sum(N)

from cube

where [date] between @startdate and @enddate

and region like @region

and empid like @empid

return

end



if @method='groupSales' begin

select region,empid,sumSales=sum(N)

from

(

select

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

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

,n

from cube

where [date] between @startdate and @enddate

and region like @region

and empid like @empid

) as a

group by region,empid

return

end

</Our Reporting Stored Procedure>