none
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

    Saturday, February 02, 2013 4:45 AM

Answers

All replies

  • 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

    Sunday, February 03, 2013 11:04 AM
  • 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

    Tuesday, February 05, 2013 9:39 AM
    Moderator