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

The magic in our new groupSales code block is the use of a derived table, CASE and PATINDEX. For example, when we group by region, SQL will return a row for every region. But if there was only one region, SQL will only return one row. This is what the derived table does. If the user doesn’t want to group by region, the derived table changes all regions to ‘’. The outer group by statement returns one row. The derived table performs its magic by using the case statement. If the @group parameter has the word “region”, then don’t alter region, else change it to ‘’. The case statement uses PATINDEX to search for the word “region” in the @group parameter. If it finds it, it returns the starting character position else it returns zero.

Here are some results:

exec CubeSp @group=’all’

REGION EMPID sumSales

0 138

exec CubeSp @group=’region’

REGION EMPID sumSales

East 0 4

North 0 24

South 0 46

West 0 64

exec CubeSp @group=’region,empid’

REGION EMPID sumSales

North 1 18

North 2 6

South 12 44

South 13 2

East 21 4

West 31 24

West 32 36

West 33 4

exec CubeSp @group=’region,empid’,@region=’north’

REGION EMPID sumSales

North 1 18

North 2 6

This concludes our article about SEARCH & GROUP. In the future, I hope to write articles about implementing SORT, TOTAL ROWS, and PIVOT/TRANSPOSE functionality via SQL Server.

About the Author

Louis Duc Nguyen lives in Dallas, TX and has a BS in Engineering and an MBA from University of Texas at Austin and Dallas. He is currently working for a major airline. He specializes in creating data-entry and reporting applications. Please address comments and hot job tips to Louis Nguyen at louisducnguyen@hotmail.com.

Copyright © 2002-2003 Louis Duc Nguyen All Rights Reserved

]]>

Leave a comment

Your email address will not be published.