Dynamic Sql Pivot Table in Report Services or Excel?

• adams.squared

  SSCrazy

  Points: 2675

  More actions

  May 30, 2008 at 11:19 am

  #380833

  I created a nice backlog report report showing order numbers and the month they were due. The months that they were due in are the variable column headers. They vary because of past due months, so I do not know how many months, or which months I will be looking at. The resulting pivot shows the value of that order in that month. Now the accountants can show the banks how much we expect to bill in the upcoming months. So basically for my Y axis I have the orders and the X axis I have the Months they were due and the value is the dollar amount of the order.

  I used dynamic sql to make the number of columns and the column headers dynamic and I'm quite proud of myself for getting the final result. The problem is that I can't get the output beyond my grid on my screen. If I try to put it into Report Services, it will work on the grid in the data source, but I will get an error if I try to run the layout for the report. If I try and run the same SQL in Excel using Microsoft Query, the result I'll get is "Query from SqlConnection" in cell A1. If I take out the dynamic SQL and hard code my column headers, then everything works fine and dandy, but that defeats the purpose of what I am doing.

  So my question is, can Report Services handle Dynamic SQL and if so, any suggestions? If not, is there a work around? Or maybe I am going about this wrong and maybe I should run a stored procedure. Any comments are greatly appreciated.

  Here is a simplified version of my code, which I based off of Todd Townley's post here on these forums.

  Backlog View

  DT varchar(5)

  BLMonth (varchar(7)

  Value Numeric(22,2)

  DECLARE @sql VARCHAR(8000)

  SET @sql='select [DT]'

  select @sql=@sql + ', [' + blmonth +']'

  frombacklog

  group by blmonth

  order by blmonth

  set @sql=@sql+'

  From (Select [DT], blmonth, [Value] from backlog) x

  Pivot(sum([value]) for blmonth in ('

  SELECT @sql = @sql + ', [' + blmonth + ']'

  FROM backlog

  GROUP BY blmonth

  ORDER BY blmonth

  SET @sql = @sql + ')

  ) AS pvt'

  SET @sql = REPLACE(@SQL, ' IN (, ', ' IN (')

  exec(@sql)

• adams.squared

  SSCrazy

  Points: 2675

  More actions

  June 2, 2008 at 9:14 am

  #823006

  i figured it out. i do not need to use dynamic sql when using the matrix table in Report Services.