Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




Report Writing

Reporting Services, Crystal Reports, Access and other report-writing tools. Also, this covers T-SQL techniques that come in especially handy in report-writing.
Database Column Names != Report Headings

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.   For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.  I often see programmers struggling with writing dynamic SQL to produce output like this: CustomerID   2008 Total    2007 Total   Variance ----------   ----------    ----------   -------- ABC          $100          $50          $50 DEF          $200          $250         -$50 That is, the names of the columns vary based on the data; that is not a good way...

posted @ Wednesday, August 06, 2008 11:43 AM | Feedback (3) | Filed Under [ Techniques Efficiency Report Writing ]

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important.  In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.  We all know that there's lots of bad databases designs out there, so this can be a handy technique to know.  Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time,...

posted @ Wednesday, April 23, 2008 10:33 AM | Feedback (17) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques SQL Server 2005 Report Writing SQL Server 2008 ]

The Mailbag: Referencing Assemblies in Reporting Services; some SQL help

As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag!  Just some quickies today. Christopher writes: Greetings Jeff, First and foremost, great job with all of the blogs. I have a questions that I cannot seem to get a straight answer for. I am working with SQL Server Reporting Services (SSRS) and have the need to create VB functions to customize the reports generated. For example, a setter/getter to display information that would not be readily available from the query. SSRS allows this type of custom Visual Basic code to reside in the report itself, but since most of my code is across...

posted @ Thursday, October 18, 2007 12:19 PM | Feedback (0) | Filed Under [ Miscellaneous Report Writing GROUP BY Reporting Services ]

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

Filter by month (plus other time periods)

Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. What is the best way to declare parameters that will be used to indicate which month you are looking for, and how can we efficiently and easily make use of those parameters to get back the data we need? read more...

posted @ Friday, September 14, 2007 12:21 PM | Feedback (5) | Filed Under [ Techniques Efficiency Report Writing DateTime Data ]

Thinking Set-Based .... or not?

So, I hear you're a "set-based SQL master" ! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. However, don't completely forget the most important skill that you learned in the procedural world!


posted @ Monday, April 30, 2007 1:03 PM | Feedback (21) | Filed Under [ T-SQL Techniques Report Writing Joins/Relations ]

Better Alternatives to a FULL OUTER JOIN

As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.


posted @ Thursday, April 19, 2007 11:56 AM | Feedback (69) | Filed Under [ T-SQL Techniques Efficiency Report Writing Joins/Relations GROUP BY ]

How to be an Effective Report Writer

A non-technical guide to writing reports, with a focus on managing requirements, results, and expectations.


posted @ Friday, April 13, 2007 11:03 AM | Feedback (11) | Filed Under [ Techniques Report Writing ]

Conditional Joins in SQL Server

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your join expression. There's a much better way to approach the problem.


posted @ Tuesday, April 03, 2007 12:15 PM | Feedback (39) | Filed Under [ T-SQL Report Writing Joins/Relations ]

Rendering Excel and Word files with SQL Server

I've been playing around with a handy tool for creating Word and Excel files called OfficeWriter that's pretty impressive.


posted @ Tuesday, July 18, 2006 3:05 PM | Feedback (2) | Filed Under [ Miscellaneous .NET (C# / VB) Report Writing ]

.NET CrossTabs versus SQL Server CrossTabs

As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.


posted @ Thursday, May 12, 2005 10:01 AM | Feedback (7) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

Powered by:
Powered By Subtext Powered By ASP.NET