Can I use @table variable in SQL Server Report Builder?

Question

Using SQL Server 2008 Reporting services:

I'm trying to write a report that displays some correlated data so I thought to use a @table variable like so

DECLARE @Results TABLE (Number int
                       ,Name nvarchar(250)
                       ,Total1 money
                       ,Total2 money
                       )

insert into @Results(Number, Name, Total1)
select number, name, sum(total)
from table1
group by number, name

update @Results
set total2 = total
from
(select number, sum(total) from table2) s
where s.number = number

select from @results

However, Report Builder keeps asking to enter a value for the variable @Results. It this at all possible?

EDIT: As suggested by KM I've used a stored procedure to solve my immediate problem, but the original question still stands: can I use @table variables in Report Builder?

Answer 1

No.

ReportBuilder will

1. 2nd guess you
2. treats @Results as a parameter

Answer 2

Put all of that in a stored procedure and have report builder call that procedure. If you have many rows to process you might be better off (performance wise) with a #temp table where you create a clustered primary key on Number (or would it be Number+Name, not sure of your example code).

EDIT
you could try to do everything in one SELECT and send that to report builder, this should be the fastest (no temp tables):

select
    dt.number, dt.name, dt.total1, s.total2
    from (select
              number, name, sum(total) AS total1
              from table1
              group by number, name
         ) dt
        LEFT OUTER JOIN (select
                             number, sum(total) AS total2
                             from table2
                             GROUP BY number --<<OP code didn't have this, but is it needed??
                        ) s ON dt.number=s.number