Dynamic column header based on given two date parameters

Question

user pass date parameters:

StartDate - 2012-01-03  //(Dynamic user can run for any dates)

EndDate - 2013-04-02   //(Dynamic user can run for any dates)

Output

ID	NAME	PrjStartDate	PrjEndDate	MonthsDura	WeightedRev	2012-    01(Jan)	2012-02(Feb)	2012-03(Mar)	…...... .........	2013-04(Apr)
1	aaaa	2012-03-12	2013-01-14	10	2000	Null	Null	1450	….....1450...........	Null
--	--	--	--	--	--	--	--	---	----	---

             

I want to show the output as above where in the date columns changes as date changes so does the weightedrev

My Query

DECLARE
		@StartDate datetime,
		@EndDate datetime,
		@StartStr nvarchar(12),
		@EndStr nvarchar(12),
		@query nvarchar(max),
		@years nvarchar(2000)
		
set		@StartDate = '2012-01-03'   //user can select any date
set		@EndDate = '2013-04-12'     //user can select any date

set		@StartStr = '''' + left(convert(nvarchar(20), @StartDate, 21), 10) + ''''
set		@EndStr = '''' + left(convert(nvarchar(20), @EndDate, 21), 10) + ''''


set  @years = STUFF(( SELECT DISTINCT
						'],[' + convert(nvarchar(7), Calc_Date, 126) + ' (' + datename(month, Calc_Date) + ')'
                        FROM   abc.explodemonths(@StartDate, @EndDate)  //function
                        ORDER BY '],[' + convert(nvarchar(7), Calc_Date, 126) + ' (' + datename(month, Calc_Date) + ')'
                        FOR XML PATH('')
						), 1, 2, '') + ']'
						
					
set	@query = 

'SELECT *
FROM (
		select	
			convert(nvarchar(7), Calc_Date, 126) + '' ('' + datename(month, Calc_Date) + '')'' as MonPer,
				--calc_date,
				o.id,
				o.name,
				o.probability,
				o.CloseDate,
				o.Start_Date__c as StartDate,
				o.End_Date__c as EndDate,
				o.Amount,
				o.ExpectedRevenue as WeightedRevenue,
				(datediff(m, o.Start_Date__c, End_Date__c) + 1) as MonDuration,
case when (datediff(m, o.Start_Date__c, End_Date__c) + 1)  <= 0 then null else o.ExpectedRevenue / (datediff(m, o.Start_Date__c, End_Date__c) + 1) end as RevMon ///for everymonth

		from	stagingopportunity o
		join	abc.explodemonths('+@StartStr+', '+@EndStr+') cal on cal.calc_date between abc.SynMonthBegin(o.Start_Date__c)  and abc.SynMonthBegin(o.End_Date__c)    /*this is a Table value function*/
		where	o.isdeleted = 0
		and		o.isclosed = 0
	
) AS src
-----cant use pivot in SSRS reports so  pivot logic wont work --------------
/*PIVOT (
    MAX(RevMon) FOR MonPer IN ('+@years+')
) AS pvt */
order by probability desc, name asc'
--select	@query 
execute	(@query)

I cannot use pivot to get the results...not sure how i can get the output with some other query....totally stuck

Any help is very much appreciated

Thank You

Answer 1

does your query (without pivoting) give you the details needed for every month? if yes then use a column group on either Start_Date or EndDate

---

Teddy Bejjani - BI Specialist @ Netways

Answer 2

Hi Synuser,

It’s hard for me to understand your query completely for the reason that I am not an expert of T-SQL query. However as per my understanding, we needn’t use the pivot function to convert row to column and then display it. We can add a column group on date column, then it will display in columns. For more information about column group, please see:
http://popbi.wordpress.com/2012/03/02/ssrs-how-to-add-a-column-group-to-an-existing-table/

I have tested it with some sample data, the screenshots below are for your reference.

Hope this helps.

Regards,
Charlie Liao

---

Charlie Liao
TechNet Community Support