I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007. Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select. I have come to understand that a dynamic anything is a moving target! Please advise.

Hi Roberto, Our group produces monthly reports where the number of columns in the report can grow dynamically, from 1 to 12 months. The easiest solution was to use a SQL query with a fixed YEAR_MONTH column with the product and sales data, and then let reporting services pivot the table. For example, the SQL Query returns : YEAR_MONTH PRODUCT SALES_QTY 2011_JAN Product1 250 2011_JAN Product2 150 2011_FEB Product1 200 ... In Reporting Services, our table uses [YEAR_MONTH] in the header and which will expand automatically pivoting the data. PRODUCT 2011_JAN 2011_FEB Product1 250 200 Product2 150 etc. I hope this helps. Best regards, Christopher Sorensen

As indicate above, in such a case you need to use YYYYMM (it sorts better than YYYYMMM). Example: SELECT YYYYMM=left(CONVERT(varchar, OrderDate,112),6), TotalSales=SUM(totaldue) FROM AdventureWorks2008.Sales.SalesOrderHeader GROUP BY left(CONVERT(varchar, OrderDate,112),6) ORDER BY YYYYMM /* YYYYMM TotalSales 200107 1172359.4289 200108 2605514.9809 200109 2073058.5385 200110 1688963.2744 200111 3690018.6652 .....*/ Dynamic SQL article: http://www.sqlusa.com/bestpractices/datetimeconversion/ Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam