In a previous blog post, the concept of derived tables was used to show you how to create an SQL Server Reporting Services (SSRS) report. Derived tables have been around for years and provide good functionality. However, they are missing some functionality that can be helpful when writing reports. This article will show you another tool in the Transact SQL arsenal that you can use instead of derived tables.

Consider this scenario. You have a report with three numeric columns and a list of values on the left as shown here:

You can easily use derived tables to create the above report. However, what if you want to exclude territories with no MTD values? Derived tables do not give you the option to exclude rows based on the values within them.

Riding to the rescue is a concept called Common Table Expressions (CTE). Creating queries and the reports based on them using CTEs gives you a lot of additional functionality over derived tables. For example, you can use the columnar data in your WHERE conditions.

Consider this data in a table called TerritorySales.

To create a report with territories on the left and values for MTD, QTD and YTD on the top, we’re going to start with a query to pull in the territory names.

To use a CTE in a query, you begin with the word “With.” The statement below creates a query that only pulls the Territory column from a Territory table.

With A As
(Select Territory From TerritoryTable),

What we did above was create a sort of temporary table called “A” that includes a single column named “Territory.” Notice that we began the statement with the word “With.” You only use that keyword once.

The next expression is going to create our MTD column.

B as
(Select Territory, sum(Amount) as MTD from TerritorySales  where Month(SaleDate) = 11 group by Territory),

You may have started to pick up on what’s happening. What we have done is create two tables, “A” and “B,” that will be joined together in another Select statement below.

Here are the QTD and YTD expressions:

C as
(Select Territory, sum(Amount) as QTD from TerritorySales  where Month(SaleDate) between 10 and 12 group by Territory),

D as
(Select Territory, sum(Amount) as YTD from TerritorySales  where Month(SaleDate) between 1 and 12 group by Territory)

To recap, we have essentially created four “tables.” We need to create a Select statement that pulls the data from all four “tables.”

Here’s that Select statement, plus the four preceding statements.

With
A as
(Select Territory from TerritoryTable),

B as
(Select Territory, sum(Amount) as MTD from TerritorySales  where Month(SaleDate) = 11 group by Territory),

C as
(Select Territory, sum(Amount) as QTD from TerritorySales  where Month(SaleDate) between 10 and 12 group by Territory),

D as
(Select Territory, sum(Amount) as YTD from TerritorySales  where Month(SaleDate) between 1 and 12 group by Territory)
Select a.territory, b.MTD, c.QTD, d.YTD From A
Left Outer Join B on A.Territory = B.Territory
Left Outer Join C on A.Territory = C.Territory
Left Outer Join D on A.Territory = D.Territory
Where B.MTD <>

This CTE has:

1. Created four table-like objects
2. Joined the table-like objects together
3. Used a WHERE clause to join the tables

Here is an example of a report using SSRS based on the CTE.

There are many more uses of CTEs in SQL Server statements. Go to http://msdn.microsoft.com/en-us/library/ms190766.aspx for information on these powerful objects.

Please contact our Microsoft Dynamics GP Support Center if you would like more help creating reports.