locked
How to get table header dynamically from sql table header RRS feed

  • Question

  • I have a sql query

    create table temp
    (
        date1 datetime,
        category varchar(3),
        amount money
    )

    insert into temp values ('1/1/2015', 'ABC', 1000.00)
    insert into temp values ('2/1/2015', 'DEF', 500.00)
    insert into temp values ('10/1/2015', 'GHI', 800.00)
    insert into temp values ('2/10/2015', 'DEF', 700.00)
    insert into temp values ('3/1/2015', 'ABC', 1100.00)
    insert into temp values ('3/1/2015', 'XXX', 1100.00)
    insert into temp values ('1/1/2015', 'XXX', 1000.00)
    insert into temp values ('10/1/2014', 'GHI', 1800.00)

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
    WITH dummyCTE as
    (Select 1 as no union all select 1 union all select 1 union all select 1 )
    ,dumyCTE1 as (select a.no, row_number()over(order by a.no) rno from dummyCTE as a, dummyCTE as b , dummyCTE as c , dummyCTE as d)
    ,Minmaxdate as(select min(date1)mindate,max(date1)maxdate from temp)
    select @cols = STUFF((SELECT distinct ',' + QUOTENAME( FORMAT(dateadd(month,rno-1,mindate),'yy-MM')) as mon From dumyCTE1, Minmaxdate where 
    dateadd(month,rno-1,mindate) <= maxdate order by mon desc
    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    print @cols

    --FORMAT(c.date1,'MM-yy')
    --SET @cols = STUFF((SELECT  ',' + QUOTENAME(convert(varchar(7),c.date1,121)) as mon
    --            FROM #temp c where c.date1  > DATEADD(year,-5,GETDATE())  order by year(c.date1) desc,month(c.date1) desc
    --            FOR XML PATH(''), TYPE
    --            ).value('.', 'NVARCHAR(MAX)') 
    --        ,1,1,'')
    --print @cols


    set @query = 'SELECT category , ' + @cols + ' from 
                (
                    select FORMAT(date1,''yy-MM'') as mon
                        , amount
                        , category
                    from temp 
               ) x
                pivot 
                (
                     count(amount)
                    for mon in (' + @cols + ')
                ) p '


    execute(@query)

    drop table temp

    Data is coming in below manner:

    In above format:

    15-10 means Oct 15, 15-09 means Sept 15

    I am getting issues while formatting this in SSRS.

    I need SSRS table headers to be dynamic. I need that it should be according to the sql table header

    The table header should change according to the sql query

    Need Help!!


    Abhinav

    Wednesday, October 7, 2015 4:54 AM

Answers

  • Hi Abhinav,

    Use this as your header expression. I have tested with a parameter .just replace accordingly to your requirement.

    =Left(MonthName(Right(Parameters!ReportParameter1.Value, len(Parameters!ReportParameter1.Value) - 3)),3) & "-" & Mid(Parameters!ReportParameter1.Value,1,InStr(Parameters!ReportParameter1.Value,"-") - 1)


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    • Proposed as answer by Shrek Li Thursday, October 8, 2015 8:27 AM
    • Marked as answer by Shrek Li Thursday, October 29, 2015 5:46 AM
    Wednesday, October 7, 2015 5:34 AM

All replies

  • Hi Abhinav,

    Use this as your header expression. I have tested with a parameter .just replace accordingly to your requirement.

    =Left(MonthName(Right(Parameters!ReportParameter1.Value, len(Parameters!ReportParameter1.Value) - 3)),3) & "-" & Mid(Parameters!ReportParameter1.Value,1,InStr(Parameters!ReportParameter1.Value,"-") - 1)


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    • Proposed as answer by Shrek Li Thursday, October 8, 2015 8:27 AM
    • Marked as answer by Shrek Li Thursday, October 29, 2015 5:46 AM
    Wednesday, October 7, 2015 5:34 AM
  • for getting headers dynamically all you need to do is to add a column group on the date field and do a pivot based on it. The SSRS tablix has the ability to add row and column groups and adding date field in column group will ensure it generates the columns automatically based on the distinct values in date column. For the data portion you need to apply some aggregation. In case its not a purely aggregatable data (like text data for example) you can apply Min or Max functions to fake aggregation.

    see

    http://ssrstutorials.blogspot.ae/2012/10/lesson-15-ssrs-matrix-reports.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, October 7, 2015 6:11 AM