Using CTEs with SQL Reporting Services & Dynamics GP
We had a great time doing a webinar recently for the GPUG Special Interest Group (SIG) for SQL Reporting Services. We talked about using Common Table Expressions (CTE) to simplify queries for reports. CTEs were new in SQL Server 2005 and can replace using derived tables. Essentially, they create temporary result sets using nested SQL statements. CTEs also are useful in contrast to derived tables because they can self-reference and be referenced multiple times.
Here is a sample CTE used to compare year-over-year sales information for customers:
AS (select CUSTNMBR, PERIODID, YEAR1, SMRYSALS from RM00104 where HISTTYPE=1 and YEAR1=@YEARFIRST),
AS (select CUSTNMBR AS CUSTNMBR2, PERIODID as PERIODID2, YEAR1 as YEAR2, SMRYSALS as SMRYSALS2 from RM00104 where HISTTYPE=1 and YEAR1=@YEARSECOND)
–Overall SELECT statement
Select CTE_Customer.*, CTE_CustomerCompare.* from CTE_Customer left outer join CTE_CustomerCompare on CTE_Customer.CUSTNMBR=CTE_CustomerCompare.CUSTNMBR2 and CTE_Customer.PERIODID=CTE_CustomerCompare.PERIODID2
Note the use of WITH to define the first CTE (called CTE_Customer); a comma precedes the second CTE (called CTE_CustomerCompare). The SELECT statement for each CTE is nested after AS. Last but not least, we have the SELECT statement that actually pulls the information from the CTEs.
In Report Builder (for SQL Reporting Services), we can then create a dataset using our CTE statement as follows:
Note the use of two parameters (@YEARFIRST and @YEARSECOND) to make the report dynamic, so we can select two different years for comparison based on our needs. After creating the dataset, we can design the report using the fields selected from the CTEs.
Then when we run the report, we can specify the two years (based on the parameters in the statement) and review our report:
Email email@example.com for more CTE examples and sample RDLs of reports created with CTEs.